MS Access DLookUp using variables

0
votes

I am building a form in MS Access and I want some information to be pulled from one of several tables and populated into a text box, based on two drop downs that the user interacts with on the same form. After researching this, I found that DLOOKUP does what I am looking to do - the only problem is that I am having issues with getting it to work properly and I keep getting "#NAME" appearing in the text box.

I did some tutorials with DLOOKUP and had successful results but applying it to my projects has not been successful. In my project the FieldName of the Dlookup is static, which is "Description" but both the table name and Criteria are dynamic being passed to it as variables.

Currently I have four tables: tblAC, tblAT, tblAU, and tblCA - all following the design:

Table (Control_Number, Description)

Each table has four records, with the 'Control_Number" as the primary key. The control number is a string - because it can have letters in it.

What I have so far is:

  Dim controlfamily1 As String
  Dim control1 As String

  controlfamily1 = "tbl" & CStr(Me.cboControlFamily.Value)
  control1 = CStr(Me.cboControls.Value)

     Me.txtDescription.ControlSource = DLookup("[Description]", controlfamily1, "[Control_Number] ='" & control1 & "'")

Where controlfamily1 is a variable of the type string - representing the TableName in the DlookUp. Control1 is a also a string variable representing the specific criteria to search on. I believe my issue all boils down to the my use of quotes, brackets and double quotes.

Thanks!

3
ms-access
I tried it with a variation on your system (same punctuation, but changing the field and table names to match my existing data), and it worked fine. This leads me to think that the problem is not with your quotes and brackets, but rather with your variables. Is it possible, maybe, that your Me.cboControlFamily.Value already has "tbl" in the value, causing it to duplicate that text (e.g., "tbltblAC")? – Joe DeRose

3 Answers

3
votes

If you put expression in ControlSource, you must use "

Me.txtDescription.ControlSource = "=DLookup(""[Description]"", ""[tbl" & controlfamily1 & "]"", ""[Control_Number] ='" & control1 & "'"")"

but try to set property in design mode to

= DLookup("[Description]", "tbl" & CStr(Me!cboControlFamily), "[Control_Number] ='" & Me![cboControls] & "'")

and do Me.txtDescription.Requery when needed

2
votes

I realise that this is a pretty old post, but it helped me so:

I had success with the following where fieldName is a string containing the name of the tables field: Note the position of the square brackets inside the parenthesis

DLookup("[" & fieldName & "]", "my_table", "id = " & my_id)

It helped me deal with a bad naming convention where there was field1, field2...8 that I had to collect data from.

0
votes

when you pass the tablename as a string in some variable, you do not need to use "".

So dlookup("[FieldA]","tableName","criterion")

or

Dim tableNamezzz as String
tableNamezzz = Cstr(me.textboxCarryingTableName.value)
dlookup("[FieldA]", tableNamezzz, "Criterion")

If you want to pass the column name as a variable and into the dlookup function, use below:

Dim columnNamez as String
Dim tableNamez as String
    tableNamez = Cstr(me.textboxCarryingTableName.value)
    ColumnNamez = Cstr(me.textboxCarryingColumnName.value) 
DLookup("ID", TableNamez, ColumnNamez & "='" & Me.txtFieldValue & "'")