Open Recordset with Dlookup Variable for Field Name

[Sorrells]

Greetings,

Here I am again, treading where I've never trod before. I have looked through a lot of threads but cannot find what I am looking for which is this:

I have several tables with common field names. Within a form, and totally outside the realm of form controls, I need to access one, then many tables. Within these tables there is a specific field I am after that varies depending on the value displayed in a listbox. I use this value in a DLOOKUP to obtain a string that is a field name.

I then need to open the table, using this Dlookup stringname as the field name in an OpenRecordset statement.

In pseudo-sort_of-code, my objective would look something like this:

' text value from listbox name lstRoom
Dim CR_Abb As Variant
Dim fldName As Field
Dim dbs As DAO.DATABASE
Set dbs = CurrentDb
CR_Abb = DLookup("[OG_Abb]", "[Table_1]", "[Room] = lstRoom&quot
Set fldName = CR_Abb
Set rstDFT_1 = dbs.OpenRecordset("SELECT [ Room_Groups].SubGroup " & _
"FROM [Room_Groups] " & _
"WHERE [Room_Groups].fldname = Yes&quot

When the above runs, the error "Object required" is associated with the Set fldName statement.

I know my approach is wrong. But there must be some way that the variable (CR_Abb) can be used in the OpenRecordset statement to refer to a real and existing field in the table. In this case, I need the list of names in the table field Subgroup so that I can repeat almost the same sequence in each of the tables that this list would allow me to open.

This is not easy to describe and I would be happy to provide additional information that may help in resolving this problem. My thanks in advance.
Regards, Sorrells

 

[PWise]

try this
Dim CR_Abb As Variant
Dim dbs As DAO.DATABASE
Set dbs = CurrentDb
CR_Abb = DLookup("[OG_Abb]", "[Table_1]", "[Room] = lstRoom&quot
Set fldName = CR_Abb
Set rstDFT_1 = dbs.OpenRecordset("SELECT [ Room_Groups].SubGroup " & _
"FROM [Room_Groups] " & _
"WHERE [Room_Groups]." & CR_Abb & "= Yes&quot

 

[Sorrells]

I found the solution!

After continued looking in forums and newsgroups, I found some interesting twists to the SQL that finally led me to the solution.

The variable fldName was not necessary. Instead the variable CR_Abb can be used directly in the SQL as follows:

Set rstDFT_1 = dbs.OpenRecordset("SELECT [ Room_Groups]." & CR_Abb & _
",[ Room_Groups].Object_subGroup " & _
"FROM [ Room_Groups]. " & _
"WHERE [ Room_Groups]." & CR_Abb & "= Yes&quot

I hope this is of use to others! I can go on my happy way now!
Regards, Sorrells

 

[Sorrells]

Thank you pwise,

Your input has provided much needed positive reinforcement.

I have a happy issue from this problem and can move forward into the next one! Regards, Sorrells