Auto Filling Fields in Current Record from Previous Record

[J. Trucking]

Hello,

I have looked through posts related to Auto-Filling a field from a
previous record but am still a little unsure of how to do things.
What I would like to do is auto fill a field in the current record
with one from a different field in a previous record. What I have is
a form that records "engine hours" and "tonnes moved" for a piece of
equipment. The operator will record the staring hours and tonnes (at
the start of a day) and the hours and tonnes at the end of the day.
So in the database, I would like the "Starting Hours" and "Starting
Tonnes" on the current/new record to fill with the "Ending Hours" and
"Ending Tonnes" from the previous record. Is this accomplished with
Microsoft's "AutoFill" function on their website or do I need to
change it a bit.

Thanks in advance,

John

 

[Guest]

I would use dlookup...

In the On Current event of the form use something like...

if me.newrecord then
me.starting_hours = dlookup("Ending Hours", "TableName", "DateField = #" &
format(dmax("DateField", "TableName"), "mm/dd/yyyy") & "#")
end if

Essentially, this looks up the Ending Hours of the record with the maximum
date.
If there are several pieces of equipment then you will need to restrict the
above further to use that equipment's id.
I've also assumed one entry per date.

Hope this points you in the right direction

Andy Hull

 

[J. Trucking]

I would use dlookup...

In the On Current event of the form use something like...

if me.newrecord then
me.starting_hours = dlookup("Ending Hours", "TableName", "DateField = #" &
format(dmax("DateField", "TableName"), "mm/dd/yyyy") & "#")
end if

Essentially, this looks up the Ending Hours of the record with the maximum
date.
If there are several pieces of equipment then you will need to restrict the
above further to use that equipment's id.
I've also assumed one entry per date.

Hope this points you in the right direction

Andy Hull








- Show quoted text -

Thanks Andy. Worked great!