Text box DLookup

[eacollie]

I have a textbox on a form (form2) where I need to set the display with vba as it may change depending on the data selected in a combobox on another form (form1).

I'm using the following code in the form (form2) OnLoad event:

    Dim contactnumber As Long
    contactnumber = [Forms]![Form1]![subfrm].[Form]![Contact]

    If Forms!frmForm1.cmbobox= "Selection1" Then
        Me.Textbox1.ControlSource = DLookup("[LastName] & "", "" & [Title] & "" "" & [FirstName]", "[table1]", "[ID] = " & contactnumber)
    End If
 
 Me.Textbox1.Requery

When debugging, the correct value shows up for the "contactnumber" variable and the control source of the text box but it is empty.

What am I doing wrong?

 

[SOS]

You can't look up multiple fields with one DLookup like that. You would need one for each field. LastName would get its own. Title would get its own, FirstName would get its own and then you would concatenate those three DLookups together.

 

[eacollie]

Thanks SOS.

Got it to work:

Me.Textbox1 = DLookup("[LastName] & "", "" & [Title] & "" "" & [FirstName]", "[table1]", "[ID] = " & contactnumber)