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")
Set fldName = CR_Abb
Set rstDFT_1 = dbs.OpenRecordset("SELECT [ Room_Groups].SubGroup " & _
"FROM [Room_Groups] " & _
"WHERE [Room_Groups].fldname = Yes")
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.
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")
Set fldName = CR_Abb
Set rstDFT_1 = dbs.OpenRecordset("SELECT [ Room_Groups].SubGroup " & _
"FROM [Room_Groups] " & _
"WHERE [Room_Groups].fldname = Yes")
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.
