DLookup on form text field

[Gregvg]

I have a form called "Input Information" that writes to a table called "Data". I have a field on that form called "Unit Number". I have another text field on the form called "Unit Rate".

I have another table called "Vehicle List". 2 of the fields on this table are "Vehicle Id" and "Cost Per Mile"

I am trying to do a lookup against the Unit Number which is the same as Vehicle ID to get the Unit Rate which is the same as Cost Per Mile. I put the following code in the change event on the form field called Unit Number but it isn't working. Am I barking up the wrong tree?

Me.Unit_Rate.Value = DLookup("[Total Cost Per Mile]", "[Vehicle List]", "[Vehicle Id]=" & Me!Unit_Number)

 

[pr2-eugin]

I think it shoule be a matter of some simple missing quotes.. try the following it might work..

Me.Unit_Rate.Value = DLookup("[Total Cost Per Mile]", "[Vehicle List]", "[Vehicle Id]=" & Me!Unit_Number & " ")

try the ones that have & and two "..

 

[Gregvg]

That solved one problem but now I am getting a type mismatch error. Is there a better way to pass that data to the table? Once i have the lookup value needs to become static. If the value changes on the Lookup table it can't change on the form.

 

[pr2-eugin]

when you say static.. do you mean that the values must not be editable by users who handle the form?? in that case you have to make sure that the property of the field is made Locked=Yes. the value can be generated/placed through VBA but cannot be changed by the user interacting with the form.

in regards to the error make sure the Unit_Rate field's type is similar to that of [Total Cost Per Mile]..

If you still have issues.. post back..

 

[Gregvg]

The Cost Per Mile updates on a yearly basis but once that value is established on a specific date I don't want it to update again on the data table so i have history. that is what i am trying to do with the form. Lookup the value on that day and pass it to the data table. i hope this makes sense to you. There is a lot of other fields on this form all doing what they are suposed to do. but this one has me stumped.