last piece missing in combo box look up

[Guest]

Hello all....

I'm trying to auto-fill alot of fields on a form based on what is chosen off
of a combo box. The combo box is based on a query and displays record
number(unique), lastname and firstname. As long as I hardcode the record
number, it works find...I just need to figure out the syntax to grab the
record number off of the combo box.

Here is my code snippet currently:

Dim varX As Variant
varX = DLookup("[PatientFirstName]", "qryLastMovementTime2", "[RecordNumber]
= 2")
If (Not IsNull(varX)) Then Me![PatientFirstName] = varX


What I need to do is change "[RecordNumber]=2" to
"[RecordNumber]=TheRecordSelectedOffTheComboBox"

The combobox name is "Combo58" currently. Can someone please help me with
the correct way of editing my statement above?

thanks for taking the time to read.

 

[Marshall Barton]

I'm trying to auto-fill alot of fields on a form based on what is chosen off
of a combo box. The combo box is based on a query and displays record
number(unique), lastname and firstname. As long as I hardcode the record
number, it works find...I just need to figure out the syntax to grab the
record number off of the combo box.

Here is my code snippet currently:

Dim varX As Variant
varX = DLookup("[PatientFirstName]", "qryLastMovementTime2", "[RecordNumber]
= 2")
If (Not IsNull(varX)) Then Me![PatientFirstName] = varX


What I need to do is change "[RecordNumber]=2" to
"[RecordNumber]=TheRecordSelectedOffTheComboBox"

The combobox name is "Combo58" currently. Can someone please help me with
the correct way of editing my statement above?

There is no need to use DLookup for this. If the combo
box's BoundColumn property corresponds to the record number,
then the combo box's Value property will contain it.

If you want to copy the third field in the combo box's
RowSource table/query, the code in the combo box's
AfterUpdate event (and the form's Current event?) would be:
Me!PatientFirstName = Me.combobox.Column(2)

To look up a field that is not in the row source
table/query, you would use:

DLookup("PatientBirthDate", "qryLastMovementTime2", _
"RecordNumber=" & Me.combobox