By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

DLookup Question

Breezwell
P: 33
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.
Oct 10 '08 #1
Share this Question
Share on Google+
7 Replies


P: 90
I guess it could work in theory.

Why don't you try it out and see what happens?
Oct 10 '08 #2

DonRayner
Expert 100+
P: 489
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 & '"'
Oct 10 '08 #3

NeoPa
Expert Mod 15k+
P: 31,495
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!
Oct 12 '08 #4

Breezwell
P: 33
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.
Oct 12 '08 #5

NeoPa
Expert Mod 15k+
P: 31,495
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 :
Expand|Select|Wrap|Line Numbers
  1. Call MsgBox("[Names]", _
  2.             [Forms]![Form1].cmbTables, _
  3.             "[LastName] = 'Jones'")
  4. DLookup("[Names]", _
  5.         [Forms]![Form1].cmbTables, _
  6.         "[LastName] = 'Jones'")
Oct 13 '08 #6

Breezwell
P: 33
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:
Expand|Select|Wrap|Line Numbers
  1. Dim var As String
  2. var = DLookup("[Names]", [Forms]![Form1].cmbNames, "[LastName] = 'Jones'")
  3. 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.
Oct 13 '08 #7

NeoPa
Expert Mod 15k+
P: 31,495
That's enterprising :)

For assistance with debugging, should you need it in the future, check out Debugging in VBA.
Oct 14 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.