Create query using combo box parameter as the chosen field

[Noobie89]

Hi everyone,
I looked all over internet to understand how to use combo box parameter as the chosen field in the upcoming query creation.

Sub CreateQuery()



Dim db As DAO.Database
Dim qDef As DAO.QueryDef

Set db = CurrentDb
Set qDef = db.CreateQueryDef("MyQuery")
qDef.SQL = "SELECT * FROM "
qDef.Close
db.Close
Set qDef = Nothing
Set db = Nothing
Application.RefreshDatabaseWindow

End Sub

How can I use a combo box parameter after SELECT or FROM in SQL SYNTAX?

[ranman256]

You can either put the text boxes in the SQL:
qdef.sql = "select * from table where [field]='" & me.txtBox & "'"
qdef.close
Docmd.openQuery qdef.name

or

set rst= qdef.openrecordset

or use param in qdef:

Qdef.parameters(0) = me.txtBox
ser rst= qdef.openrecordset

or

just open the query, no code:
docmd.openQuery "qsQuery"

[Noobie89]

But this would only work as a criteria. I have a combo box of all the tables and another of all the fields dependent of which table is chosen in the first combo box. I want to use these two parameters to create a query. Something like this:
qDef.SQL = "SELECT x FROM y "
x = [Forms]![Form1]![Combo5].[Value]
y = [Forms]![Form1]![Combo7].[Value]

[Noobie89]

Found it!
Sub CreateQuery()

Dim db As DAO.Database
Dim qDef As DAO.QueryDef

Set db = CurrentDb
Set qDef = db.CreateQueryDef("MyQuery")
qDef.SQL = "SELECT * FROM " + Me.Combo2.Text
qDef.Close
db.Close
Set qDef = Nothing
Set db = Nothing
Application.RefreshDatabaseWindow

End Sub

[Welshgasman]

I would be using concatenation with &

"SELECT " & [Forms]![Form1]![Combo5] & " FROM " & [Forms]![Form1]![Combo7]