MS Access correctly utilizing a string variable within DLookup

0
votes

I am attempting to replace the domain parameter of DLookup with a variable, the intent being a single place to make a change if one is required. This is how I am declaring the variable:

Dim MnMnuSettingTbl As String
MnMnuSettingTbl = "'tblMainMenu'"

This is the original segment where the variable is to be used:

Me.MainMenuChoiceOne.Caption = DLookup("BtnText", "tblMainMenu", "ID = 1")

I wish to replace the domain criteria "tblMainMenu" with the variable, but when I attempt to do so it either does not compile, or I get an error message stating the table can not be found. I have reviewed several articles on this matter, and I am gathering I am not passing the variable correctly, via the improper use of single or double quotes. I'm rather embarrassed, so at this point I am looking for the correct way to either format the variable or the correct way to use it within the DLookup context.

1
variablesms-accessms-access-2013

1 Answers

1
votes

The variable must contain the same constant string as you currently have in the DLookup.

MnMnuSettingTbl = "tblMainMenu"
Me.MainMenuChoiceOne.Caption = DLookup("BtnText", MnMnuSettingTbl , "ID = 1")

Single quotes would be needed for string parameters in the WHERE clause, e.g.

strTextID = "'QD42'"
x = DLookup("foo", "bar", "TextID = " & strTextID)