recordset on a query

  • Thread starter Thread starter dickminter
  • Start date Start date
D

dickminter

My query criteria references a field value on an open form
([forms]![frmName]![fieldname]). When I use the SQL
statement for this query to create a DAO recordset, I get
an error. If I remove the field reference from the SQL
statment and substitute a value, it works find.

I can solve the problem by using docmd to run the query as
a make table, and set the recordset on the the table
created, but why won't it work the other way? How else to
filter the recordset using the field value from the open
form?

DM

P.S. The purpose of the query is to create a recordset
containing all records from the open form's linked subform
with fields values from both.
 
[email protected] said:
My query criteria references a field value on an open form
([forms]![frmName]![fieldname]). When I use the SQL
statement for this query to create a DAO recordset, I get
an error. If I remove the field reference from the SQL
statment and substitute a value, it works find.

I can solve the problem by using docmd to run the query as
a make table, and set the recordset on the the table
created, but why won't it work the other way? How else to
filter the recordset using the field value from the open
form?

DM

P.S. The purpose of the query is to create a recordset
containing all records from the open form's linked subform
with fields values from both.
Click to expand...

The problem is that, while Access knows what that form reference means,
DAO does not. The form reference is a query parameter that must be
filled in before the query can be evaluated. When *Access* processes
the query, as in DoCmd.OpenQuery or DoCmd.RunSQL -- or when you open the
query from the database container window -- it fills in the paramer
value from the form. But when you open the query via DAO, it's your
responsibility to fill in the parameter value. For a stored query, you
can do this by way of the DAO QueryDef object, like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("MyStoredQuery")

For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset

' ... work with rs ...
rs.Close

Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

If you're building your query as a SQL statement in a string literal or
variable, though -- rather than using a stored query -- you don't need
all the above. You can just embed the current value of the form control
as a literal in the SQL string, like this:

Dim strSQL As String

strSQL = _
"SELECT <some fields> FROM <some table expression> " & _
"WHERE CriteriaField=" & Forms!FormName!ControlName

Set rs = CurrentDb.OpenRecordset(strSQL)

If the field in question is text or a date field, you need to include
the proper delimiters in building your string; e.g.,

strSQL = _
"SELECT <some fields> FROM <some table expression> " & _
"WHERE CriteriaField='" & Forms!FormName!ControlName & "'"

strSQL = _
"SELECT <some fields> FROM <some table expression> " & _
"WHERE CriteriaField=#" & Forms!FormName!ControlName & "#"
 
Back
Top