I want to create a form that tracks vehicle mileage. Rather than having to enter the starting mileage on each new record (which is the ending odometer reading from the previous record) I’d like to get access to bring that number forward. With the help menu I figured out how to update it if it’s in the same field ….. but I need to get it form the “mileage end” field in the previous record to the “mileage start” field of the new record.
automatic fill
- Thread starter 48Tad
- Start date
Idjit
Clamoring Preschooler
- Local time
- Today, 18:15
- Joined
- Apr 18, 2002
- Messages
- 216
This should work - insert the following code so that when you open a new record, you set the value of your beginning mileage field to:
DLookUp("Forms![FORMNAME]![END]","TABLENAME","[AUTO]=Forms![FORMNAME]![AUTO]-1")
Insert your form/table names and the name of the ending mileage field. This function works only if you have an autonumber field as your primary key in the mileage table.
DLookUp("Forms![FORMNAME]![END]","TABLENAME","[AUTO]=Forms![FORMNAME]![AUTO]-1")
Insert your form/table names and the name of the ending mileage field. This function works only if you have an autonumber field as your primary key in the mileage table.
Idjit
Clamoring Preschooler
- Local time
- Today, 18:15
- Joined
- Apr 18, 2002
- Messages
- 216
I've been playing around with the database for a while, but I'm afraid I can't get the autofill to work the way you want it to. The correct syntax is:
DLookUp("[MilageStop]","MilageTbl","[AutoNumber] = Forms![MilageFrm]![AutoNumber]-1")
But I haven't been able to figure out where to put it so that it fills in the starting mileage field immediately when a new record is opened. I'm sure someone with more experience than I will see this and get you further ahead. You can also check here for more information on Dlookup and PrevRecVal:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504
Here are a couple of other comments I have:
You have the mileage fields set to Integer type - this data type has an upper value limit of 32,767. You'll want to change this to Single or Double type in order to enter values higher than that.
On your form, set the Control Source for the field Total to =[MilageStop]-[MilageStart]. Right now you have it set to Total, which isn't a field in the table.
If the starting mileage will ALWAYS be the ending mileage of the previous record (and you won't be changing values or deleting records that were previously entered), then there is no need to have a separate field for starting mileage (or for total mileage driven) - you're duplicating data that can always be computed on the fly using a query.
Sorry I couldn't be more help.
DLookUp("[MilageStop]","MilageTbl","[AutoNumber] = Forms![MilageFrm]![AutoNumber]-1")
But I haven't been able to figure out where to put it so that it fills in the starting mileage field immediately when a new record is opened. I'm sure someone with more experience than I will see this and get you further ahead. You can also check here for more information on Dlookup and PrevRecVal:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504
Here are a couple of other comments I have:
You have the mileage fields set to Integer type - this data type has an upper value limit of 32,767. You'll want to change this to Single or Double type in order to enter values higher than that.
On your form, set the Control Source for the field Total to =[MilageStop]-[MilageStart]. Right now you have it set to Total, which isn't a field in the table.
If the starting mileage will ALWAYS be the ending mileage of the previous record (and you won't be changing values or deleting records that were previously entered), then there is no need to have a separate field for starting mileage (or for total mileage driven) - you're duplicating data that can always be computed on the fly using a query.
Sorry I couldn't be more help.
Thank you ... I'll keep trying. Re: your statement that if there is no change in the record "MilageStop" of last record always = MilageStart of new record .... thus there is no need for those fields ..... thats what I'm trying to do ... and dont care if its in a form or a query .... as long as I can get it ino a report at the end of the month.
Similar threads
Solved
Select Last Record
