Problems with dlookup criteria in vba for access

Question

I am a beginner, and this is my first post, just to get that out first. Ive highlighted the problem im having in bold, but basically Im having trouble searching the table with the given criteria. Im trying to shorten my code by using this for loop. Currently im running 40 odd dlookup.

I have a database of users with desk numbers assigned to them and ive created a form with a layout that shows where their desk is using labels, but im trying to use the labels caption as search criteria in the dlookup. it works if I use the exact labels name and i started having problems if I use me.control. is there anything else I can use? I can set the labels caption using me.control but not search with it.

For deskNumber = 1 To 3

      strDesk = "desk" & deskNumber

      strDeska = "desk" & deskNumber & "a"

      If ("Desk_No") <> "Empty" Then
              If Me.Controls(strDesk).Caption = DLookup("Full_Name", "Tbl_Employee", "Desk_No='" & **Me.Controls(strDeska).Caption** & "'") = vbNullString Then
              Else
                   Me.Controls(strDesk).Caption = "Empty"
              End If
      Else
              Me.Controls(strDesk).Caption = "Empty"
      End If
Next deskNumber

Evan Evan · Accepted Answer · 2014-08-22T19:10:25

Firt off, code error:

If ("Desk_No") <> "Empty" Then

That is comparing the strings "Desk_No" and "Empty" which are constants and never equal.

Second, I dont really understand what you are trying to do but Labels cannot be controlled as easily as text boxes. I would reccomend changing the labels to text boxes and formating them the same way your labels are formatted.

I would say put in some breakpoints and see what is actually being returned from these controls and dlookup

Problems with dlookup criteria in vba for access

1 Answers