Select query in VBA

[Paul Warren]

I have a form which is used to collect criteria from a user. I wish to use this criteria in a Select query to display results. Currently I build an SQL statement in VBA from the input on the form and use the RunSql command with a Select Into query. I have looked in the online help and it doesn't tell you how to create a Select query it only mentions Action Queries.

Any help would be much appreciated.

 

[MHM]

Hi Paul,

RunSQL only works with action queries (which is reasonable).
But you can create a query calling values from any (open) form, setting WHERE to

Forms!MyFormName!MyControlName

So when you don't close the form before, you can use that query.

Else you need to open a recordset like this:

Dim dbCurr as DAO.Database
Dim rs as DAO.Recordset
Dim SQL as String

SQL="SELECT Field1, Field2, Field3... " & _
"FROM Table1 [INNER JOIN...] " & _
"WHERE " & criteria & ";"
set dbCurr=currentDB
set rs=dbCurr.OpenRecordset(SQL)

Is this enough help? Otherwise try to contact me again.

Mic

 

[Paul Warren]

Thanks Mic it seems to work however can you tell me how I can display the recordset now I have it and if it is possible to display it on a form?

Thanks

Paul