Extract value from specific record to populate a new record

sHi,
Not really sure how to get my question across. I am using Office 2007. I
have a form that consists of a Drop box(which displays a license plate
numbers) and other fields with vehicle information ie. "miles out" and "miles
in". This form populates a dispatch info table. When I choose a license plate
number for a new record I would like the "miles in" field to auto fill with
the last "miles in" value corresponding to the license plate number chosen.
any help would be greatly appreciated.

"Eddie B" Eddie wrote in message
...
sHi,
Not really sure how to get my question across. I am using Office 2007. I
have a form that consists of a Drop box(which displays a license plate
numbers) and other fields with vehicle information ie. "miles out" and
"miles
in". This form populates a dispatch info table. When I choose a license
plate
number for a new record I would like the "miles in" field to auto fill
with
the last "miles in" value corresponding to the license plate number
chosen.
any help would be greatly appreciated.


I assume that by "drop box" you mean a combo box. To answer your question,
I need answers to some questions of my own:

1. What is the name of the table that contains the vehicle information,
including the license plate number, miles in, and miles out?

2. What are the names of those fields in the table?

3. Do you really want to auto fill the "miles in" field with the vehicle's
last "miles in", or is it the "miles out" field that you want to fill?

4. How do you determine what is the *last* "miles in"? Is it the highest
number?

5. I'm guessing these are odometer readings. If the last "miles in" is the
highest one, might you ever have to deal with an odometer that has rolled
over from its maximum reading to zero again?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

"Dirk Goldgar" wrote in message
...


I assume that by "drop box" you mean a combo box. To answer your
question, I need answers to some questions of my own:

1. What is the name of the table that contains the vehicle information,
including the license plate number, miles in, and miles out?

2. What are the names of those fields in the table?

3. Do you really want to auto fill the "miles in" field with the vehicle's
last "miles in", or is it the "miles out" field that you want to fill?

4. How do you determine what is the *last* "miles in"? Is it the highest
number?

5. I'm guessing these are odometer readings. If the last "miles in" is
the highest one, might you ever have to deal with an odometer that has
rolled over from its maximum reading to zero again?


Oops, forgot:

6. What is the name of the combo box?


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Thank you for the reply.
Yes, the "drop box" is actually a combo box.

1. Table name is "dispdetails.
2. Field names are "tagnumber" "milesout" "milesin"

3. You are correct in that I want the miles out filled in with the last
miles in for that tagnumber.

4. yes, it would be the highest number for that tagnumber.

5. yes, these are odometer readings. there is no chance of having to deal
with an odometer that has rolled over.

6. The combo box name is "vehiclebox"

Thanks again

"Dirk Goldgar" wrote:

"Dirk Goldgar" wrote in message
...


I assume that by "drop box" you mean a combo box. To answer your
question, I need answers to some questions of my own:

1. What is the name of the table that contains the vehicle information,
including the license plate number, miles in, and miles out?

2. What are the names of those fields in the table?

3. Do you really want to auto fill the "miles in" field with the vehicle's
last "miles in", or is it the "miles out" field that you want to fill?

4. How do you determine what is the *last* "miles in"? Is it the highest
number?

5. I'm guessing these are odometer readings. If the last "miles in" is
the highest one, might you ever have to deal with an odometer that has
rolled over from its maximum reading to zero again?


Oops, forgot:

6. What is the name of the combo box?


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

"Eddie B" wrote in message
...
Thank you for the reply.
Yes, the "drop box" is actually a combo box.

1. Table name is "dispdetails.
2. Field names are "tagnumber" "milesout" "milesin"

3. You are correct in that I want the miles out filled in with the last
miles in for that tagnumber.

4. yes, it would be the highest number for that tagnumber.

5. yes, these are odometer readings. there is no chance of having to deal
with an odometer that has rolled over.

6. The combo box name is "vehiclebox"


Based on this info, you could use the following event procedure for the
AfterUpdate event of the combo box:

'------ start of code ------
Private Sub vehiclebox_AfterUpdate()

If Not IsNull(Me.vehiclebox) Then

Me.milesout = _
DMax("milesin", "dispdetails", _
"tagnumber = '" & Me.vehiclebox & "'")

End If

End Sub
'------ end of code ------

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

I copied and pasted the code in the AfterUpdate event of the combo box. For
some reason I am getting a Run Time error 3646 Type Data Mismatch In
Criteria Expression. Any ideas what I am doing wrong.

"Dirk Goldgar" wrote:

"Eddie B" wrote in message
...
Thank you for the reply.
Yes, the "drop box" is actually a combo box.

1. Table name is "dispdetails.
2. Field names are "tagnumber" "milesout" "milesin"

3. You are correct in that I want the miles out filled in with the last
miles in for that tagnumber.

4. yes, it would be the highest number for that tagnumber.

5. yes, these are odometer readings. there is no chance of having to deal
with an odometer that has rolled over.

6. The combo box name is "vehiclebox"


Based on this info, you could use the following event procedure for the
AfterUpdate event of the combo box:

'------ start of code ------
Private Sub vehiclebox_AfterUpdate()

If Not IsNull(Me.vehiclebox) Then

Me.milesout = _
DMax("milesin", "dispdetails", _
"tagnumber = '" & Me.vehiclebox & "'")

End If

End Sub
'------ end of code ------

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

"Eddie B" wrote in message
...
I copied and pasted the code in the AfterUpdate event of the combo box. For
some reason I am getting a Run Time error 3646 Type Data Mismatch In
Criteria Expression. Any ideas what I am doing wrong.

What data type is the field "tagnumber" in table "dispdetails"? I assumed
it was text; if it's a number type then the code should be:

Me.milesout = _
DMax("milesin", "dispdetails", _
"tagnumber = " & Me.vehiclebox)


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

It worked like a charm. Thank you very much for your help

"Dirk Goldgar" wrote:

"Eddie B" wrote in message
...
I copied and pasted the code in the AfterUpdate event of the combo box. For
some reason I am getting a Run Time error 3646 Type Data Mismatch In
Criteria Expression. Any ideas what I am doing wrong.

What data type is the field "tagnumber" in table "dispdetails"? I assumed
it was text; if it's a number type then the code should be:

Me.milesout = _
DMax("milesin", "dispdetails", _
"tagnumber = " & Me.vehiclebox)


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)