Cannot Get DLookup Function to work in Access 2013

Question

I am trying to get the Dlookup function to work in Access 2013, but i just cannot get it to work, heres what i have so far :-

I have a query called qry_VehicleOverview in this query there are Two fields called VehicleNumber and DateLastExam

I have a form, there are a number of fields, two of them called Vehicle1 and DateLastExamV1, in DateLastExam1 ! am trying to reference the relevant exam based on Vehicle1 from qry_VehicleOverview field, so when a Vehicle Number is added to Vehicle1 it displays the correct Exam in DateLastExam1

first of all I create a combo box in the form, called Vehicle1 and referenced it to VehicleNumber from qry_VehicleOverview

then i created a text box in the form, and called it LastExamVehicle1, in the control source of this field I added the DLookup function :-

=DLookup("[DateLastExam]","qry_VehicleOverview","[VehicleNumber]=""" & [Vehicle1].[Text] & """")

Then chose After update in Event tab and Selected code Builder in here I added :-

Private Sub LastExamVehicle1_AfterUpdate() Me.LastExamVehicle1.Requery End Sub

but when run the form, first of all i get an error of #Type in the field, when i change the value in Vehicle1 the eror then changes to #Error

Answer 1

I create a combo box in the form, called Vehicle1 and referenced it to VehicleNumber from qry_VehicleOverview

If that is so, there is no need for DLookup as you already have the value.

Set the RowSource of Vehicle1 to qry_VehicleOverview and the count of fields for the combobox to 2 and use this ControlSource for your textbox:

=[Vehicle1].[Column](1)

It will automatically update.

Answer 2

Don't use [Vehicle1].[Text], use [Vehicle1].[Value] instead. Or just [Vehicle1] .
(.Value is the default property)

.Text is only valid while the focus is in the control, and in AfterUpdate it isn't anymore.

.Text is mainly useful in the Change event, but that wouldn't make sense for your case.

Edit

You probably need the "full path" to the control in DLookup. For better readability I suggest using single quotes.

=DLookup("[DateLastExam]","qry_VehicleOverview","[VehicleNumber]='" & Forms!yourForm![Vehicle1] & "'")

Also please double-check all names in your form (and then in your question). E.g. from your description, your event procedure should read:

Private Sub Vehicle1_AfterUpdate()
     Me.DateLastExamV1.Requery
End Sub