Parameter queries not working

[dawhyte]

I am trying to get a parameter query working so I can gather field data using VBA.

I have a query (Qry_TotalOrderPrice) that uses one table and is as follows:

Field1=Order_ID
Criteria=[Forms]![Frm_Orders]![Order_ID] (form reference is valid)

Field2=Total of two fields using the SUM function

I am then using the following code in VBA to access this query but I keep on getting 'Too few parameters. Expected 1':

***
Sub CalculateTotalCost()

Dim db As Database
Dim rst As Recordset
Dim qry As QueryDef

Set db = DBEngine(0).Databases(0)

Set qry = db.QueryDefs("Qry_TotalOrderPrice")

qry![Forms!Frm_Orders!Order_ID] = [Forms]![Frm_Orders]![Order_ID]

Set rst = db.OpenRecordset("Qry_TotalOrderPrice")

End Sub
***

I did try a more simpler way which was to make the query a normal one (by removing all querydef references in VBA) then opening query via the openrecordset method but even then I get a 'type mismatch' error.

At the moment I`m using a hidden subform to get thi data (so it doesn't spoil the current look of the main form I`m using) but I would like to resolve this problem using a simpler method.


Any help appreciated


Derek

 

[Rich]

Can't figure out what you are trying to do, but why not Sum the fields on the form, or use DSum?

 

[dawhyte]

Thanks for the help. I ended up using the DSum function. This seems more efficient code wise than using parameter queries.

Thanks again.

 

[Rich]

It may seem more efficient in that it requires much less code but unfortunately life in Access is not that simple, depending on the size of the underlying recordset DSum may actually be the least efficient method. Worth remembering

 

[dawhyte]

I see, the recordset will obviously grow over time and I`d rather use the most efficient code now rather than having to change it later.

Should I stick with using parameter queries in VBA then ? Or is there a better alternative ?

 

[dawhyte]

Many thanks for the valuable responses.

I`ve resolved my problem using a parameter query and some VBA