Re: Populate Field Based on Value of Previous Record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Mmm ..
base the data sheet on the normal query, not the totals query.
The totals query is used to get the previous odo.

Here is code you can put into the Load event for your subform.

Private Sub Form_Load()
dim dtePrev As Date
Dim lngPrevOdo as Long
Dim strCriteria as String

dtePrev = Nz(DLookup("[NameOfTotalsDateField]", "NameOfTotalsQuery"),0)
Debug.Print dtePrev
strCriteria = "[FillupDate] = " & SQLDate(detPrev0 & ""
Debug.Print strCriteria
lngPrevOdo = DLookup("[NameOfOdoField]", "[NameOfMileageTable]",
strCriteria)
Debug.Print lngPrevOdo
End Sub


Use the value for lngPrevOdo in your calculation for mileage.
If you wish to show the previous odo on the form, you can make an unbound
textbox and put its control source
=Nz(lngPrevOdo,0)

Replace the obvious with your object and control names.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Marie" <Marie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3409058B-ED71-4386-B953-425BFCEE7EBA@xxxxxxxxxxxxxxxx


"Jeanette Cunningham" wrote:

You will need to change the query that the form is based on - it is
probably
looking for the value for previous odo and can't find it.
Get the datasheet to work properly, then we can help with the dlookup.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Marie" <Marie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3D5769DF-CA75-4D44-9D40-BEE1C3388015@xxxxxxxxxxxxxxxx


"Jeanette Cunningham" wrote:

Hi Marie,
I suggest that you make a small change to the Mileage table.
Remove the field called PreviousODO - you can always find the value
for
ProviousODO without storing it twice in that table.
It is stored twice because it is first entered as CurrentODO, then in
the
next record it is repeated again but this time called PreviousODO.

When you need to find the PreviousODO to use for your calculation, you
can
look it up using DLookup and the most recent date.

Create a query based on the mileage table, using the primary key
field,
the
CurrentODO and the FillupDate.
Change the query to a totals query and in the totals row under
FillupDate,
put Max.
This will give you the previous ODO for the most recent date.
Post back if you need help with the dlookup.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Marie" <Marie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0FCAEC02-D098-4F18-B981-FA118E598CF7@xxxxxxxxxxxxxxxx
Hello,
I am working on a project that keeps track of our fleet of buses.
One
thing
we need to keep track of is each time a bus fuels up, we need the
previous
odometer reading, the current odometer reading, and the amount of
gallons
used to full the tank. I then have a calculated field on the form
only
to
show the amount of miles driven between each fuel up. Right now, I
have
a
Vehicle table, and a Mileage table. It is one to many. I have a form
for
the
vehicles, and a subform for the mileage. On the subform, these are
the
fields: FillupDate, PreviousODO, CurrentODO, and Gallons. I would
like
to
have the Current ODO populate the next record for the same vehicle
in
the
PreviousODO field. I have looked for the answer on this forum, but
so
far
nothing has worked for me. The VehID field is text, based on the VIN
number.
I appreciate any help that you can give me.



Thank you for your reply, Jeanette. When I did as you suggested, the
datasheet did not show beneath the titles.. Also, where do I put the
dlookup? Thanks.


I deleted PreviousODO from the Mileage table and created a new query. I
then
based the subform on the new query. If I disengage the totals button, the
daasheet shows. As soon as I add totals, the datasheet disappears. The
main
query is based on the Vehicles table.


.

Relevant Pages

  • Re: Populate Field Based on Value of Previous Record
    ... You will need to change the query that the form is based on - it is probably ... I suggest that you make a small change to the Mileage table. ... next record it is repeated again but this time called PreviousODO. ... This will give you the previous ODO for the most recent date. ...
    (microsoft.public.access.forms)
  • Re: Report is making too many numbers
    ... Am I supposed to attached the new totals query to the already existing ... where the Award is stored. ... FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON ...
    (microsoft.public.access.reports)
  • Re: Running Totals On QueryDef In Code
    ... with the query as a source. ... You could also use a totals query as you mentioned, but do it all in code. ... Dim DB As DAO.Database ... Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined, ...
    (comp.databases.ms-access)
  • Re: Counting 3 different fields seperately on a report
    ... I created the 'totals query' and put in the criteria Like "X" but when I go ... "Jeff Boyce" wrote: ... Access asks you for a value (via a parameter prompt) when it can't find the ...
    (microsoft.public.access.reports)
  • Re: TWO Crosstab Query Ouput Questions
    ... locations for each species as rows with the Banding ... all recoveries in that location for each of those species. ... the SQL of the current crosstab query looks like this: ... concerning the Totals Query. ...
    (microsoft.public.access.queries)