To: Killian and Norie:
I have several tables, each one contains data and comments, or key words. I want to search these tables for the record(s) containg my choice of key word. As I add records, I might also add new key words. I (want to) choose the key word from a Combo Box.
Note...Given a combo box,I CAN do this!
I want to assemble an SQL statement with the table and field names needed to set up a combo box.
My form (Form1) has an option box corresponding to each table. The code under each option box contains the table and field names needed for the SQL statement.

I use a function to assemble and return the SQL statement. The function is called from the chosen option box, and is passed table and field names. It returns an SQL called MySql.
MySql is saved as a Public variable.
In Design form, the Combo box|Properties|Row Source, I type "MySQL", without the quotes.
The Combo box's Control source is left blank.
If the Row Source Type is Table/Query I get an error message:
'~cq_cForm1~sq_cCombo6' specified on this form does not exist
If the Row Source type is blank, or SQL, No error message, but an empty Combo box.
My form name is Form1
The Function is Function MakeMySql(Table,Field)
I use many MsgBoxes to help...
Any/All help will be appreciated!
........................................................................... ....
[VBA]Option Compare Database
Public MySql As String
Private Function MakeMySql(ByVal Table As String, ByVal Field As String) As String
Dim TableField As String
MsgBox ("Table = " & Table & ", Field = " & Field)
Table = "[" & Table & "]"
Field = "[" & Field & "]"
TableField = Table & "." & Field
MsgBox ("Now Table = " & Table & ", and TableField = " & TableField)
MySql = "SELECT " & TableField & " FROM " & Table & ";"
'MySql = Chr(34) & MySql & Chr(34) 'Adding the Chr(34)s didn't help!
MsgBox ("Final sql = " & MySql)
MakeMySql = MySql
End Function
Private Sub Option2_GotFocus()
MsgBox ("Option 2, Got Focus")
Dim Table As String
Dim Field As String
Table = "Table1"
Field = "Color"
MySql = MakeMySql(Table, Field)
MsgBox ("In Option2, MySql = " & MySql)
End Sub[/VBA]

The same assembled SQL statement placed as Row Source in a diferent Combo Box Does Work.

Sorry to be so wordy, but what can be expected from an old retired professor?
mud2