Use a form's control value as a table name

[Nicholas Scarpinato]

Hello all. I have a search form that I've built that I want to add additional
functionality to. Right now this form is coded to search from one table, but
I want to expand it to search all tables and be able to find data based on
the fields in those tables. This requires two things: A way to select the
table to search, and a way to pull the field names for those tables. I have
the first problem solved, but now I'm somewhat stuck on the second part. How
do I programmatically pull a field list for a table based on a value of a
combobox on my form?

This is the code I have so far (non-functional, but you can see where I'm
heading with it):


Private Sub SearchTable_AfterUpdate()
Dim db As Database, fld As DAO.Field, sqlStmt As String, fSkipExecute As
Boolean, TableString As Table
DoCmd.RunSQL "DELETE * FROM tblSearchFields;"
Set db = CurrentDb()
Set TableString = Me![SearchTable]
For Each fld In TableString.Fields
sqlStmt = "INSERT INTO tblSearchFields (SearchFields) VALUES ('" &
Replace(fld.Name, "'", "''", 1, -1, vbDatabaseCompare) & "');"
If (Not (fSkipExecute)) Then
db.Execute sqlStmt, dbFailOnError
Else
fSkipExecute = False ' Reset.
End If
Next fld
Set fld = Nothing
Set db = Nothing
End Sub


Any help in trying to get the value of the control in a format that I can
use to pull the fields from that table is appreciated.

 

[Marshall Barton]

Hello all. I have a search form that I've built that I want to add additional
functionality to. Right now this form is coded to search from one table, but
I want to expand it to search all tables and be able to find data based on
the fields in those tables. This requires two things: A way to select the
table to search, and a way to pull the field names for those tables. I have
the first problem solved, but now I'm somewhat stuck on the second part. How
do I programmatically pull a field list for a table based on a value of a
combobox on my form?

This is the code I have so far (non-functional, but you can see where I'm
heading with it):


Private Sub SearchTable_AfterUpdate()
Dim db As Database, fld As DAO.Field, sqlStmt As String, fSkipExecute As
Boolean, TableString As Table
DoCmd.RunSQL "DELETE * FROM tblSearchFields;"
Set db = CurrentDb()
Set TableString = Me![SearchTable]
For Each fld In TableString.Fields
sqlStmt = "INSERT INTO tblSearchFields (SearchFields) VALUES ('" &
Replace(fld.Name, "'", "''", 1, -1, vbDatabaseCompare) & "');"
If (Not (fSkipExecute)) Then
db.Execute sqlStmt, dbFailOnError
Else
fSkipExecute = False ' Reset.
End If
Next fld
Set fld = Nothing
Set db = Nothing
End Sub


Any help in trying to get the value of the control in a format that I can
use to pull the fields from that table is appreciated.

Try something more like:

Dim TableString As String 'table name
Set db = CurrentDb()
db.Execute "DELETE * FROM tblSearchFields;"
TableString = Me![SearchTable]
For Each fld In db.TableDefs(TableString).Fields
sqlStmt = "INSERT INTO tblSearchFields (SearchFields)
VALUES ('" & Replace(fld.Name, "'", "''", 1, -1,
vbDatabaseCompare) & "');"

 

[Nicholas Scarpinato]

Thanks Marshall. That worked perfectly.

Hello all. I have a search form that I've built that I want to add additional
functionality to. Right now this form is coded to search from one table, but
I want to expand it to search all tables and be able to find data based on
the fields in those tables. This requires two things: A way to select the
table to search, and a way to pull the field names for those tables. I have
the first problem solved, but now I'm somewhat stuck on the second part. How
do I programmatically pull a field list for a table based on a value of a
combobox on my form?

This is the code I have so far (non-functional, but you can see where I'm
heading with it):


Private Sub SearchTable_AfterUpdate()
Dim db As Database, fld As DAO.Field, sqlStmt As String, fSkipExecute As
Boolean, TableString As Table
DoCmd.RunSQL "DELETE * FROM tblSearchFields;"
Set db = CurrentDb()
Set TableString = Me![SearchTable]
For Each fld In TableString.Fields
sqlStmt = "INSERT INTO tblSearchFields (SearchFields) VALUES ('" &
Replace(fld.Name, "'", "''", 1, -1, vbDatabaseCompare) & "');"
If (Not (fSkipExecute)) Then
db.Execute sqlStmt, dbFailOnError
Else
fSkipExecute = False ' Reset.
End If
Next fld
Set fld = Nothing
Set db = Nothing
End Sub


Any help in trying to get the value of the control in a format that I can
use to pull the fields from that table is appreciated.

Try something more like:

Dim TableString As String 'table name
Set db = CurrentDb()
db.Execute "DELETE * FROM tblSearchFields;"
TableString = Me![SearchTable]
For Each fld In db.TableDefs(TableString).Fields
sqlStmt = "INSERT INTO tblSearchFields (SearchFields)
VALUES ('" & Replace(fld.Name, "'", "''", 1, -1,
vbDatabaseCompare) & "');"