Having problems with DLookup

[Ryker7]

I have a form called frmservices which has two boxes. The lookup box contains social security number. The second box contains the name. When a social security number is selected, I want the name box to be populated with the corresponding name. On the control source of the name box, I have the following Dlookup statement.

=DLookup("[Name]", "tblelders", "[ssnumber]= Form![ssnumber]&quot

where name is coming from the table tblelders. SSNUMBER is the primary key in the table and also listed in the table tblservices which is the record source for this form I am working with.

The lookup is working, but only after you close the database or go back two records. When social security number is selected, the name box remains unpopulated, however, backtrack not one but two records, and the name field will populate on the current record and remain populated from then on. Then all this starts over with a new record. What have I done wrong?

 

[MichaelintheUK]

Hello Ryker7

I hope this helps.

You appear to have hardcoded the text into the string

=DLookup("[Name]", "tblelders", "[ssnumber]= Form![ssnumber]&quot

You need to make this dynamic by placing it ouside the string.

Example if SSNumber is a numeric field

=DLookup("[Name]", "tblelders", "[ssnumber]=" & FormName![ssnumber])

Example if SSNumber is a text field

=DLookup("[Name]", "tblelders", "[ssnumber]='" & FormName![ssnumber] & "'&quot


Thanks Michael

 

[AlienRay]

It's possible the DLookup is waiting for a Refresh event. You could try putting an On Exit Refresh event on the SSN field.

If it were me, however, I would scrap the whole DLookup formula. Instead, I'd make the SSN field a combo box with two columns: SSN and Name. The Name column would have a width of zero, so as not to display. On the On Exit event, I would then populate the Name box with:

Me.Namebox = Me.SSNbox.Column(1)

This should be much faster than a DLookup, and less prone to error.