DLookup Question

[Breezwell]

This is probably a simple question for someone out there.

I understand that the DLookup function takes has the following syntax:

DLookup(expression,domain,[criteria])

From what I have read, domain can be a table or query.

I have no problem getting the function to work when I use the actual name of a table for the domain. My question is, can I utilize a combobox that lists the names of my tables and pass the value of the selected table from the combo box to the DLookup function as the domain?

For example, I have a Form named Form1 with a combobox named cmbTables that lists the following table names:

Table1
Table2
Table3

In the DLookup function, is it possible to do something like this:

DLookup("[Names]","[Forms]![Form1].cmbTables","[LastName] = 'Jones'")

I was hoping this, or some sort of similar code, would translate to this:

DLookup("[Names]","Table1","'LastName] = 'Jones'")

Thanks for any and all help.

[mandanarchi]

I guess it could work in theory.

Why don't you try it out and see what happens?

[DonRayner]

This is probably a simple question for someone out there.

I understand that the DLookup function takes has the following syntax:

DLookup(expression,domain,[criteria])

From what I have read, domain can be a table or query.

I have no problem getting the function to work when I use the actual name of a table for the domain. My question is, can I utilize a combobox that lists the names of my tables and pass the value of the selected table from the combo box to the DLookup function as the domain?

For example, I have a Form named Form1 with a combobox named cmbTables that lists the following table names:

Table1
Table2
Table3

In the DLookup function, is it possible to do something like this:

DLookup("[Names]","[Forms]![Form1].cmbTables","[LastName] = 'Jones'")

I was hoping this, or some sort of similar code, would translate to this:

DLookup("[Names]","Table1","'LastName] = 'Jones'")

Thanks for any and all help.

The way you have it written you are passing the string "[Forms]![Form1].cmbTables" to the dlookup function, I don't think it will work like that. Probally just need to remove the quotes or if that doesnt work maybe something like '"' & [Forms]![Form1].cmbTables & '"'

[NeoPa]

Don's first guess is right ;)

If the value of the [cmbTables] control is "Table1", then simply leaving the quotes off should do what you require.

Welcome to Bytes!

[Breezwell]

Thanks everyone for the suggestions.

Sorry for not getting back to this sooner. Been trying to make this work.

I tried removing the quotes, changing the way the quotes were used, etc. but nothing seemed to work. I had to change my entire approach to the initial way I was trying to construct my UI.

Yes, it did seem as though just not having the quotes would work, as I have managed to concatenate form references before in a similar manner outside the DLookup function, so I am still stumped on this one. I may explore this more when I have time, as it would provide a nice way to allow users to specify which tables they want to search from a user-control perspective.

Thanks again for the help.

[NeoPa]

I'm confident the command works as expected. I can only assume other issues are not as expected. Try adding the following line to see if it shows anything up :

Call MsgBox("[Names]", _
            [Forms]![Form1].cmbTables, _
            "[LastName] = 'Jones'")
DLookup("[Names]", _
        [Forms]![Form1].cmbTables, _
        "[LastName] = 'Jones'")

[Breezwell]

NeoPa,

Your confidence was well proven.

I went ahead and put together a small database with minimal data and tried using the table reference without quotes as in the folloiwng:

Dim var As String
var = DLookup("[Names]", [Forms]![Form1].cmbNames, "[LastName] = 'Jones'")
MsgBox(var)

There were two entries for Jones in the table that the comboox cmbNames listed. Sure enough, the MsgBox displayed the name Jones as predicted and desired. DCount also worked and returned the proper number of Jones entries.

In my original project code, the error was showing up on the DLookup function, so I am not sure what exactly was causing the code to throw the error. I have reworked all that code, so debugging is not something I plan on doing right now. Probably a good lesson for the future.....

Anyway, thanks again for following up on this as I now have another tool to add to my fledgling collection of Access skills.

[NeoPa]

That's enterprising :)

For assistance with debugging, should you need it in the future, check out Debugging in VBA.