Auto Populate Form Text Box from Table

[virtualkeeper via AccessMonster.com]

I've got a small problem I need some help with:

I have a form (Reservations) bound to a Table (Reservations) that looks up
information on a Vehicle based on a selection from a cbo dropdown box. The
information is list displayed in a Subform (SubReservations). I'm wanting to
display the last milage entry (OdometerIn) from the subform in the form
textbox (OdometerOut) in the main form. I tried using a DMax function but
have had no luck.

Does anyone know how to do this?

The end result would be that a person selects a vehicle, sees the history and
automatically has the milage populated to help complete the new record of the
reservation for that vehicle.

Thank you in advance for any and all help.

 

[kingston via AccessMonster.com]

Assuming the data is entered correctly, the function DMax() will work:

CurrentMileage = DMax("[OdometerIn]","[Reservations]","[VehicleID]=" & [Forms]
![Reservations]![VehicleID])

This assumes there is an ID field for the vehicle and it is a number. If it
isn't a number, change the criteria to add parentheses around the value.

 

[virtualkeeper via AccessMonster.com]

CurrentMileage = DMax("[OdometerIn]","[Reservations]","[VehicleID]=" & [Forms]

![Reservations]![VehicleID])

This assumes there is an ID field for the vehicle and it is a number. If it
isn't a number, change the criteria to add parentheses around the value.

kingston,

Thank you for your reply. The field data is letters and numbers. I could
change it to the ID but as of now I'm just retooling someone else's brief
start to the database. I tried this:

=DMax("[OdometerIn]","[Reservations]","[Vehicle]=" & "[Forms]![Reservations]!
[Vehicle]")

I put the code in the default value field of the OdometerOut in the main form
and it doesn't give me anything. Here is a sample of the Vehicle field data:
TRA1401ATXTRUCKT002

I had a similar DMax in it before and also, no output. Any other ideas?

 

[kingston via AccessMonster.com]

Try this:

=DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & [Forms]![Reservations]!
[Vehicle] & "'")

CurrentMileage = DMax("[OdometerIn]","[Reservations]","[VehicleID]=" & [Forms]
![Reservations]![VehicleID])

This assumes there is an ID field for the vehicle and it is a number. If it
isn't a number, change the criteria to add parentheses around the value.

kingston,

Thank you for your reply. The field data is letters and numbers. I could
change it to the ID but as of now I'm just retooling someone else's brief
start to the database. I tried this:

=DMax("[OdometerIn]","[Reservations]","[Vehicle]=" & "[Forms]![Reservations]!
[Vehicle]")

I put the code in the default value field of the OdometerOut in the main form
and it doesn't give me anything. Here is a sample of the Vehicle field data:
TRA1401ATXTRUCKT002

I had a similar DMax in it before and also, no output. Any other ideas?

 

[virtualkeeper via AccessMonster.com]

=DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & [Forms]![Reservations]!

[Vehicle] & "'")

No, that didn't work either. Thank you for working with it though.

 

[kingston via AccessMonster.com]

Where are you entering this expression? Could you elaborate on what doesn't
work? IOW, is there an error message? Enter Crlt+G, and in the bottom pane
of the VB window, type:
?DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & [Forms]![Reservations]!
[Vehicle] & "'")
Make sure that the form Reservations is open and the control Vehicle has
valid data.

=DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & [Forms]![Reservations]!
[Vehicle] & "'")

No, that didn't work either. Thank you for working with it though.

 

[virtualkeeper via AccessMonster.com]

Where are you entering this expression?

In the Default Value of the OdometerOut field.

Could you elaborate on what doesn't
work?

Sure, at first when the form is opened up, before the Vehicle is chosen from
the drop down, it displays an #Error message. When the Vehicle is chosen from
the drop down, it displays 0.00 in the Odometer Out box.

Enter Crlt+G, and in the bottom pane

of the VB window, type:
?DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & [Forms]![Reservations]!
[Vehicle] & "'")

I did but it didn't give me any clues. I've pulled the relevant tables and
forms out into a DB and posted them at :
http://www.virtualkeeper.com/problemo.mdb if you'd like to take a look at it.
It's 536K.

 

[kingston via AccessMonster.com]

Sorry, but the link doesn't work for me. I'm running AC2000; you probably
have something more up-to-date.

Did it work when you entered the expression in the VB results pane? Did it
give you the correct number?

It's not surprising that this will return #Error at first because there is no
Vehicle. However, is 0.00 correct for the vehicle selected? Actually, you
probably want to set this value using an event rather than making it the
default value for a bound control. For example, when a vehicle is selected,
the AfterUpdate event for that control would have something like:

Me.OdometerOut = DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & Me.
Vehicle & "'")

Where are you entering this expression?

In the Default Value of the OdometerOut field.

Could you elaborate on what doesn't
work?

Sure, at first when the form is opened up, before the Vehicle is chosen from
the drop down, it displays an #Error message. When the Vehicle is chosen from
the drop down, it displays 0.00 in the Odometer Out box.

Enter Crlt+G, and in the bottom pane

of the VB window, type:
?DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & [Forms]![Reservations]!
[Vehicle] & "'")

I did but it didn't give me any clues. I've pulled the relevant tables and
forms out into a DB and posted them at :
http://www.virtualkeeper.com/problemo.mdb if you'd like to take a look at it.
It's 536K.

 

[virtualkeeper via AccessMonster.com]

Sorry, but the link doesn't work for me. I'm running AC2000; you probably

have something more up-to-date.

Yep, AC2002-2003.

Did it work when you entered the expression in the VB results pane? Did it
give you the correct number?

No, I didn't see anything remotely correct.

It's not surprising that this will return #Error at first because there is no
Vehicle. However, is 0.00 correct for the vehicle selected?

I did expect the #Error. The 0.00 is not correct.

Me.OdometerOut = DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & Me.
Vehicle & "'")

That gave me a Runtime 3464 error with a data type mismatch in the
expression. I'll try to winnow that down to something workable though. It's a
nice little problem though isn't it? All the things I've read on here
relating to other problems should have worked but did not either. Hmmm...
chaos. ;-)

 

[kingston via AccessMonster.com]

What did you see when you entered the expression in the VB results pane? Try
using the DMax function without the criteria:
?DMax("[OdometerIn]","[Reservations]")
If that doesn't work, then [Reservations] is not the correct table name or
[OdometerIn] is not the correct field name. Otherwise, it appears that the
fields OdometerIn and OdometerOut may not be numeric so no maximum value can
be found.

Sorry, but the link doesn't work for me. I'm running AC2000; you probably
have something more up-to-date.

Yep, AC2002-2003.

Did it work when you entered the expression in the VB results pane? Did it
give you the correct number?

No, I didn't see anything remotely correct.

It's not surprising that this will return #Error at first because there is no
Vehicle. However, is 0.00 correct for the vehicle selected?

I did expect the #Error. The 0.00 is not correct.

Me.OdometerOut = DMax("[OdometerIn]","[Reservations]","[Vehicle]='" & Me.
Vehicle & "'")

That gave me a Runtime 3464 error with a data type mismatch in the
expression. I'll try to winnow that down to something workable though. It's a
nice little problem though isn't it? All the things I've read on here
relating to other problems should have worked but did not either. Hmmm...
chaos. ;-)