Set query SQL using VBA

[Guest]

I have a query that is used by a number of other queries. Depending on what
is entered in a form I want to use VBA to put a different SQL statement in
the query. For example if they select option 1 from an option box, the SQL
statement would be different to if they select option 2. Is this possible?
Basically I want to change the SQL of a stored query based on an option box.

 

[Brendan Reynolds]

You can modify the SQL property of the DAO.QueryDef object ...

Private Sub cmdTest_Click()

If Me.Frame0.Value = 1 Then
CurrentDb.QueryDefs("qryTest").SQL = _
"SELECT * FROM tblTest ORDER BY TestID ASC"
Else
CurrentDb.QueryDefs("qryTest").SQL = _
"SELECT * FROM tblTest ORDER BY TestID DESC"
End If
DoCmd.OpenQuery "qryTest"

End Sub

 

[Guest]

Thanks Brendan. I will give it a try.

You can modify the SQL property of the DAO.QueryDef object ...

Private Sub cmdTest_Click()

If Me.Frame0.Value = 1 Then
CurrentDb.QueryDefs("qryTest").SQL = _
"SELECT * FROM tblTest ORDER BY TestID ASC"
Else
CurrentDb.QueryDefs("qryTest").SQL = _
"SELECT * FROM tblTest ORDER BY TestID DESC"
End If
DoCmd.OpenQuery "qryTest"

End Sub