Setting Parameters or Query def for Access 2003 DAO SQL query

[kronster]

Greetings! Hopefully I can find some help here in the forum. Basically I am writing a small form with three dropdowns where a user can select a table, a field, and a field attribute to run a query that will return any other occurrence of that attribute in other tables and fields within that database. For example, gathering all the flight regulations that contain an acronym (TCAS) from a different table that shows aircraft capabilities listing TCAS in a field. The problem I have is my SQL query is generating a 3061 error. From what I can tell, I need to add "parameters" to a query definition to make the query work... but I'm totally lost on how to do this.

I have provided all the code in my form below... just imagine a combobox for the tables, a combobox for the fields, and a combobox with the attributes within the fields. I haven't worked on how to report the results yet, just trying to get this query issue resolved first. Many thanks!

[VBA]Option Explicit
Option Compare Database
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim field As DAO.field
Dim sSQL As String
Dim sSQL2 As String

---------------------------------------------------
Public Sub UserForm_Initialize()
Set db = CurrentDb
For Each tbl In db.TableDefs
If Not Left(tbl.Name, 4) = "MSys" Then
cboTables.AddItem tbl.Name
End If
Next tbl
End Sub

---------------------------------------------------
Public Sub cboTables_Change()
Set rs = db.OpenRecordset(cboTables.Text)
cboFields.Clear

For Each field In rs.Fields
cboFields.AddItem field.Name
Next

End Sub

---------------------------------------------------
Public Sub cboFields_Change()
'Set db2 = CurrentDb
sSQL = "SELECT DISTINCT " & cboTables.Text & "." & cboFields.Text & " FROM " & cboTables.Text & " ORDER BY " & cboFields.Text & " ASC"
Set rs2 = db.OpenRecordset(sSQL)
cboAttribute.Clear

Do Until rs2.EOF
'Debug.Print rs2.Fields(cboFields.Text)
cboAttribute.AddItem rs2.Fields(cboFields.Text)
rs2.MoveNext
Loop

End Sub

---------------------------------------------------
Public Sub cmd_Ok_Click()
Dim test1 As String
Dim test2 As String
For Each tbl In db.TableDefs
If Not Left(tbl.Name, 4) = "MSys" Then
Set rs = db.OpenRecordset(tbl.Name)
For Each field In rs.Fields
'this debug is checking that the FOR statements loop through all the tables and fields for the query
Debug.Print tbl.Name & " - " & field.Name

'This section will run the query on the current table and field determined by the looping
sSQL2 = "SELECT " & tbl.Name & "." & field.Name & " FROM " & tbl.Name & ""

'Currently the error is occurring here***********************
Set rs3 = db.OpenRecordset(sSQL2)
'*************************************************

Do Until rs3.EOF
'set values to ALL CAPS to avoid issues with upper/lower case differences and avoid empty fields
If Not rs3.Fields(field.Name) = Null Then
test1 = rs3.Fields(field.Name)
test1 = StrConv(test1, vbUpperCase)
test2 = cboAttribute.Text
test2 = StrConv(test2, vbUpperCase)

'ensure the test includes the cases where the string is before, after or within other text
If test1 Like "*" & test2 & "*" Then
'this will eventually do something with a match, for now, it is just stating where it found them
Debug.Print "LOCATED MATCH WITHIN " & tbl.Name & " - " & field.Name
End If
End If
rs3.MoveNext
Loop
Next
End If
Next
Unload frmQuery
End Sub[/VBA]

[Bob Phillips]

What do sSQL and sSQL2 hold just before the error?

[Aflatoon]

Perhaps you need:
[vba] sSQL2 = "SELECT [" & tbl.Name & "].[" & field.Name & "] FROM [" & tbl.Name & "]"[/vba]
in case of spaces or other special characters.