OPening Form to include last value

[ssworthi]

I have a table that tracks vehicle mileage and have created a form for data entry. I would like the form to reflect the last mileage when a user selects a vehicle #. Is that macro within the form or how would I best accomplish that?

 

[John Big Booty]

Have a look at the Dmax() function, that should do what you are after.

 

[ssworthi]

Thank you so very much. So would I in form design create a text box who's property is the vmax query? IN the vmax query would I include a prompt for the veh #? I really appreciate the help.

 

[John Big Booty]

You could use something along the lines of;

=DMax("MilleageField", "TableThatContainsThatField", "VehicleID = " & Me.VehicleID)

As the control source for your field.

This would return the maximum mileage for the VehicleID in the current record.

 

[ssworthi]

Thank you so much; I will give that a try.

 

[ssworthi]

I'm sure I have missed something because it says "#Name" but this is what put in the control source of the text box for last mileage. Thank you for your help. This helps me learn as wellTable is Vehicle Usage)

=DMax("[Vehicle Usage]![End Hrs/Miles]" , "[Vehicle Usage]![BCO #]" & Me.[Vehicle Usage]![BCO #] )

 

[vbaInet]

Like John wrote:

=DMax("Field here", "Table Or Query here", "Criteria here")

It has to be in that exact format.

 

[ssworthi]

Please bear with me; and thank you so much for your help. I am getting a very large number that is not tied to anything that I can see. To reflect the last ending mileage of a vehicle in a text box I have the following:
=DMax("[End Hrs/Miles]","[Vehicle Usage]","[BCO #]")
Field Table Vehicle #

Thanks again!

 

[vbaInet]

You will get there in the end. See what John wrote in the third part of the function, that's what ties it to the current record.

=DMax("[End Hrs/Miles]","[Vehicle Usage]","[BCO #]")

 

[ssworthi]

=DMax("[End Hrs/Miles]", "[Vehicle Usage]", "[BCO #] = ” & Me.[BCO #])
Please,please confirm; brackets around the fields and quotes are where they should be; I don't know much about these things. Are my spaces correct? Thank you! (I know I am close)

 

[vbaInet]

You're alright.

What is the data type of BCO?

 

[ssworthi]

It is a text field i.e., V006. If it needs to be numeric it can easily be changed; I'm in the start up of this db.

 

[ssworthi]

I am getting an invalid string error; I must be have something wrong in the verbiage. Also, should I change the type to numeric?

 

[vbaInet]

If it's text then it should be like this:

[FONT=Calibri][SIZE=3]=DMax("[End Hrs/Miles]", "[Vehicle Usage]", "[BCO #] = [COLOR=Red]'[/COLOR]" & [BCO #] & "[COLOR=Red]'[/COLOR]")[/SIZE][/FONT]

Also note that I took out the Me. because it's not valid in the Control Source.

 

[ssworthi]

Did you hear me? Big shout "Awesome"! Thank you, thank you!

 

[vbaInet]

Glad we could help.

 

[ssworthi]

What is the best way to handle opening the form to a new record? I have tried on the Main Form a button who's macro says to open the form but how do I go to a new record? I have tried different things and then the DMax no longer works. Thanks so much.

 

[vbaInet]

If you're using code in the Click event of a button -->

docmd.OpenForm "Form Name here", , , , [COLOR=Red]acFormAdd[/COLOR]

 

[ssworthi]

Would that be in form properties? Thanks so much for the help; this is really so great.

 

[vbaInet]

Yep, Property Sheet of the button > Events tab > On Click event