I have a query that uses a value from a form as a criteria. For
example looking for all customers belonging to a particular party.
the criteria on the QBE looks like [Forms]![frmParty]![PartyID]
I am trying to open the query in VBA using DAO. If I simply use
OpenRecordset (with the form open that contains the criteria value) it
fails. If I hard code a value in the query instead e.g. customerID 33
its fine. Alternatively if I change the criterion to a parameter then
use a querydef passing a value in for the parameter its fine.
This is the SQL of the query I'm trying to open.
SELECT DISTINCT qryHirePrice.HirePriceID, qryHirePrice.HirePrice,
Sum(qryPayments.PaymentAmount) AS SumOfPaymentAmount, [HirePrice]-
[SumOfPaymentAmount] AS Balance, qryHirePrice.PartyID
FROM qryHirePrice INNER JOIN qryPayments ON qryHirePrice.HirePriceID =
qryPayments.HirePriceID
GROUP BY qryHirePrice.HirePriceID, qryHirePrice.HirePrice,
qryHirePrice.PartyID
HAVING (((qryHirePrice.PartyID)=[Forms]![frmParty]![PartyID]));
this is the VBA code
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
set rst = db.OpenRecordset("qryHirePricePayment")
thanks
Allan