Query previous record for form control

Can not seem to find how to return data from the previous record to us with an unbound form control like I would have done using MS ACCESS

My table of trip records. tblTrips contains fields for previous accommodation, depart date, time and odometer etc. Also arrive accommodation, arrive date, time and odometer. Then depart details for calculating length of stay, kilometres travelled while at accommodation, cost etc using a query.

Surely it would be more efficient to return the previous record details rather than enter them again and store them again. Being able to use data from the previous record for calculations in a query would mean less typing and data space. Hope I have explained things sufficiently.

In access it was something like SELECT ID, AccomName, Address, depart date etc FROM tblTrips WHERE (TripID = forms!frmTrips.TripID -1) ORDER BY TripID

How can I achieve this.

Cheers,

Mike

Edit 29/12/2018 NSW AU time 08:12 PM

Blockquote

First, there was no formed named frmTripandSubForm. Second, you really need to learn more about Base, database and especially SQL. SQL (and not the graphical interface) will provide more answers to problems than anything else. Without it you'll forever be asking questions. Finally, the attached sample is nothing more than the application of all that has been mentioned in the answer already.

frmTripsModified uses the same linked form sub form method had given in the previous sample. It uses individual fields vs the frowned upon table control (told you there is no difference - they are just controls).

frmTripLog_sfmTripsModified uses the SQL approach noted in edit #2 above (and a previous answer).

Blockquote

There have been so many versions and changes to the tables, forms etc. I had to remake the odb from the split database and ended up with some different names. I hope I have finally conformed with the site reply etiquette.

Thanks, will check out the attached file. I have SO MUCH to learn, thank you for your patience.

Mike

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

You could possibly do something similar in Base but it would be as faulty as the Access statement provided. When working with database records, in most cases this 'ID' field is an auto increment field. Now is a record is deleted then number is not necessarily used again. So now there is no 'ID - 1' to be retrieved.

In you particular situation, the common factors are the 'TripLogID' and the accomodations. So if you select a record where the 'TripLogID' fields are equal AND 'PrevAccomID' of the current record is equal to a record containing that in the 'ArriveAccomID' you have the previous record.

Edit 2018-12-28:

With your last comment it seems there is still a bit of a connection problem with the situation. Hopefully this will clear up a few items. Attached is a sample I've been using to help answer your questions. It is certainly not your tables or data within but it should do. Please look at form TripToAccom. This is simply my record of your tblTrips table. The main table control just lists all records for that trip (only one trip in my file - see top of form). When a record is selected the table control in the bottom sub sub form displays the previous record.

If the top control has the first record selected the bottom control is empty because there is no previous record. Any other record does show the previous record. This is what you have been asking for, was answered above, and is done through the form linkage (typical form & sub form).

But I have been trying to state this is NOT what you want and you have been alluding to that. It appears you want additional information - calculations. What these are may determine the processing scheme. Doing this with queries may be tough but not impossible. Doing this with macros is another story. Macros require absolute detail - all internal field & form names and possibly more. Macros without the original Base file may not be easily possible and in either case requires time. Macros also requires your understanding of them in order to modify in the future.

You may also want to look at my Query section - in particular Mileage. This simply appends an odometer reading from one record onto another. Whether it is previous or next it is just SQL modifications. The point is that SQL may be part of your answer but the question is certainly not clear.

Sample ----- TowVehicles.odb

Edit 2018-12-28 #2:

Notwithstanding the aforementioned, you can take you current query and using a 'left join (see -> SQL Joins) similar to the one used in the 'Mileage' query example, append the information wanted from the previous segment. Then use the results to create your form. Using SQL directly instead of the graphical interface you have previously displayed will require you to understand SQL and its constructs. But this will enable you to gather all information you want and create totals (calculations) wanted.

Edit ...

more

It may also help here to explain what end result you may be looking for. This often changes what approach is to be taken. For example, you mention a query for calculations. Are you attempting by segment? by entire trip? by....?

( 2018-12-28 00:12:52 +0100 )edit

The calculations are for each "segment" @Ratslinger. I want to have a form with unbound text controls which on opening the form show the depart details from the previous stay. I realise there are issues using an autovalue field. I think from memory I used a combo box in an access form with something like: SELECT tblAccom.AccomID, [ArriveAccomName] & " ArriveAccomDate" & [DepartODO] AS PrevAccom, PrevAccomName,PrevAccomDepartDate etc FROM tblAccom

Then used unbound controls referencing the Combobox.Column(n) for each result

Hope this makes sense.

Mike

( 2018-12-28 09:06:11 +0100 )edit

@Mycle I don't believe we are on the same page. To simply place the previous record on the page is just a sub form using the method described in my answer. However, the problem lies in my comment and yours about calculations. Just having the record on the form is not going to allow any calculations. In many of my personal endeavors I create somewhat detailed results based upon various data for forms or reports. Many times I use macros to produce the results. I am not certain you are in a position for this.

So this is why I have asked what your "expected end result" is. What that is, as well as its' intended use, may well determine just how to approach the matter. It approaches a cost/profitability function which may entail much more than a simple record display.

( 2018-12-28 19:10:46 +0100 )edit

Thanks @Ratslinger, thank you for your patience, I hope to make things more clear.

Present situation: Data content for the subform is qryTrips. Some of the query fields are calculated such as TotalNights (DATEDIFF( 'dd', [ArriveAccomDate], [DepartDate] ) and AccomTotal\$ ("ArriveAccomRate" * DATEDIFF( 'dd', "ArriveAccomDate", "DepartDate" ). these are data fields for controls on the subform.

I simply would like to have fields on the subform referencing the previous record PrevAccomName, PrevDepartODO etc. And hopefully use "some" of these in a calculation for the distance travelled etc.

I have no preference for how this is achieved, either using a query, query and a subform, Macro, or simply a Combobox on the subform

Mike

( 2018-12-28 22:55:08 +0100 )edit

@Mycle A bit confused here. Just noticed you re-posted the same comment again. Have you looked at the edit section in my answer? (Second edit also added).

( 2018-12-29 00:28:23 +0100 )edit

Sorry my mistake, I have been going to the end of comments, downloaded the db and looking at it now Thanks again

Hi @Ratslinger I prefer not to use datasheet view for asthetic reasons, could not find a connection between the queries and subforms in your TripSectors form. I would like to upload my DB for you to have a look but can't see how. Do I upload it somewhere and provide the link??

Mike

( 2018-12-29 02:02:17 +0100 )edit

@Mycle The chosen controls are of not consequence. It could be separate of table - that makes no difference whatsoever. Will look at sample now.

There is no query for sub form. It is all in linking the two.

It would also help to know which form you are talking about since there are many.

( 2018-12-29 03:52:22 +0100 )edit

I think I see what you mean, linking via the master and subform ID controls, The frmTripandSubForm is where I would like to see calculated fields. frmTrips is for entering data and where seeing the departure data from the previous record would negate the need to re enter it as PrevAccom data.

( 2018-12-29 05:15:18 +0100 )edit

See edited answer. Note you may need to click on (more)` for all of it.

( 2018-12-29 06:57:37 +0100 )edit

@ Ratslinger, added the DB file here for you to have a look:

C:\fakepath\VanTripperVer6.10 (2).odb

Mike

more

( 2018-12-29 03:49:25 +0100 )edit

Opened new answer and found the attach file, so thought that was how I was supposed to do it, if I had edited my question it would have been available, I get it.

( 2018-12-29 11:05:45 +0100 )edit