Resolving query parameters

[Dennis Snelgrove]

I've got a report where I need to get the record count of the
RecordSource's query. The query itself pulls values from 4 different
textboxes on a form for it's criteria. I used the following code to attempt
to achieve the count, but I'm getting the usual error about needing values
for the 4 parameters when I hit the "Set rs = " line. I've run into this
problem many time before and I understand why it's happening. In the past I
have simply passed the SQL string directly to the OpenRecordset method and
substituted the values into the string, but in this case I need to use the
Me.RecordSource in case the query gets changed. I don't want to have to keep
re-opeing the VBA to reprogram the SQL string directly. Is it possible to
somehow tell the query to resolve the parameters?

Thanks for any insights...

Dim rs As DAO.Recordset
Dim QueryName As String

QueryName = Me.RecordSource

Set rs = CurrentDb.OpenRecordset(QueryName)
With rs
.MoveLast
.MoveFirst
End With

 

[[MVP] S.Clark]

In the report footer, add a textbox that counts the primary key.

e.g. =Count([PrimaryKeyFieldorOtherUniqueID])

 

[Allen Browne]

In a report, you can get the number of records with a text box that has a
Control Source of:
=Count("*")
Use that in the Report Header section if you need to know at the start.

If you need to use a parameter query in code, you must supply the parameters
before opening the recordset.
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set qdf = dbEngine(0)(0).QueryDefs("MyQuery")
qdf.Parameters("[Forms].[Form1].[Text1]") = Forms.Form1.Text1
'etc. for other parameters
qdf.OpenRecordset

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...