Don't understand why can't use select query

[lawdy]

I fell like I am missing something. The most common type of query (that I need) is the select query. When I try a select query (in code), I get the error "A RunSQL action requires an argument consisting of an SQL statement". So I end up building a 'make table' query. I just want to make sure I am not missing anything. Any help is appreciated.

[orange]

Show us what you tried.

[ssanfu]

I get the error "A RunSQL action requires an argument consisting of an SQL statement"

"RunSQL" can only be used for action queries. Append, Update and Delete are action queries. A SELECT query is NOT an action query.

In code, for select queries, you use something like:

Option Compare Database
Option Explicit

Sub SomeProcedure()

Dim d As DAO.Database
Dim r As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Field1, Field2 FROM MyTable WHERE Field3 = " & Me.cboControl1 & " ORDER BY Field1;"

Set r = CurrentDb.OpenRecordset (strSQL)
' do stuff

r.Close
Set r = Nothing

End Sub

or

Option Compare Database
Option Explicit

Sub SomeProcedure()

Dim d As DAO.Database
Dim r As DAO.Recordset
Dim strSQL As String

Set d = CurrentDb

strSQL = "SELECT Field1, Field2 FROM MyTable WHERE Field3 = " & Me.cboControl1 & " ORDER BY Field1;"

Set r = d.OpenRecordset(strSQL)

' do stuff

r.Close
Set r = Nothing
Set d = Nothing

End Sub

[lawdy]

Ok ssanfu, that worked. However, how do I reference the data in the query?

[ssanfu]

Depends on what you are trying to do.
A recordset opened in code cannot be bound to a form; the recordsetcreated in code can only be accessed in code.

For instance, I have a dB where I enter work hours. Rather than have a combo box to select each worker (approx 3000) and enter the hours, I first create dummy records with the hours = -1111. Then I open the hours entry form, selecting only those hours where the hours - 1111.

When I click the "Enter Hours" button, the code uses a recordset to select all of the employees. It then loops through the recordset adding a record for each active employee. Then the form opens with only the employees with hours = -1111 (the form is filtered). I enter the actual hours (maybe some of the employees didn't work this month). When I close the form, all records where hours = -1111 are deleted.

Two recordsets are opened in code, one for the active workers (call it recordset "W") and one to enter the hour records (call it "E").
If the worker in the current record of recordset "W" is determined to be an active employee, then a record is inserted into recordset "E".

That is an overview of one way to use a recordset in code.

If you can explain what you trying to do, we should be able to help you with the code.

[orange]

Further to ssanfu's comments, and using his sample code. I'm not sure exactly what you mean by

how do I reference the data in the query

If by query you mean

 "SELECT Field1, Field2 FROM MyTable WHERE Field3 = " & Me.cboControl1 & " ORDER BY Field1;"

You should not look at that as a query in Access terms. That is the sql of the recordset r.

If you want to refer to fields in that recordset you can use:

r!Field1
r!Field2

or
r.fields("Field1")
r.fields("Field2")

[lawdy]

Thanks Orange, that is exactly what I was looking for and I thank both of you.