467,087 Members | 1,234 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,087 developers. It's quick & easy.

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.
Oct 10 '08 #1
  • viewed: 1455
Share:
7 Replies
I guess it could work in theory.

Why don't you try it out and see what happens?
Oct 10 '08 #2
DonRayner
Expert 256MB
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 16PB
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
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 16PB
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
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 16PB
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.

Similar topics

2 posts views Thread by sherah | last post: by
2 posts views Thread by ctyrrell@stny.rr.com | last post: by
4 posts views Thread by Karen Bielska | last post: by
6 posts views Thread by Ron | last post: by
3 posts views Thread by OlyOil | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.