Total query

[eacollie]

I'm trying to open a recordset in a form using a query (NET_qrytotals) in which I total all records:

SELECT Sum(NET_tblReservation.NoParticipants) AS SumOfNoParticipants, NET_tblReservation.RRID
HAVING (((NET_tblReservation.RRID)=[Forms]![NET_frmRoomReservationRequest]![RoomRequestID]));

I get an error in the form:

Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM NET_qrytotals;")

Run-time error 3061: Too few parameters. Expected 1.

Any suggestions?

Thanks!

 

[Beetle]

When you run a parameterized query through the Access UI, Access provides an expression service that evaluates the parameters before the query is passed to the Jet/Ace database engine. When you run the same query through VBA, it is passed directly to Jet/Ace, which knows nothing about your form controls.

To resolve this, you can use QueryDefs to evaluate the parameters to be passed to the database engine. Example (air code);

Dim qdf As QueryDef
Dim prm As Parameter

Set qdf = CurrentDb.QueryDefs("NET_qryTotals")

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

Set rst2 = qdf.OpenRecordSet(dbOpenSnapshot)

You can find a bit more info here.