query question

[allanx38]

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.Hi rePriceID, qryHirePrice.Hi rePrice,
Sum(qryPayments .PaymentAmount) AS SumOfPaymentAmo unt, [HirePrice]-
[SumOfPaymentAmo unt] AS Balance, qryHirePrice.Pa rtyID
FROM qryHirePrice INNER JOIN qryPayments ON qryHirePrice.Hi rePriceID =
qryPayments.Hir ePriceID
GROUP BY qryHirePrice.Hi rePriceID, qryHirePrice.Hi rePrice,
qryHirePrice.Pa rtyID
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.OpenRecordse t("qryHirePrice Payment")

thanks
Allan

[tina]

yes, that's correct; you have to hard-code the value or pass the value to a
querydef. a third alternative is to use a string variable to hold the SQL
statement and pass to the recordset, concatenating the value from the form
control into the string, as

Dim strSQL As String
strSQL = "SELECT DISTINCT qryHirePrice.Hi rePriceID..." _
& "...HAVING qryHirePrice.Pa rtID = " _
& [Forms]![frmParty]![PartyID]

Set rst = db.OpenRecordse t(strSQL)

hth
"allanx38" <al******@yahoo .co.ukwrote in message
news:11******** **************@ e65g2000hsc.goo glegroups.com.. .

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.Hi rePriceID, qryHirePrice.Hi rePrice,
Sum(qryPayments .PaymentAmount) AS SumOfPaymentAmo unt, [HirePrice]-
[SumOfPaymentAmo unt] AS Balance, qryHirePrice.Pa rtyID
FROM qryHirePrice INNER JOIN qryPayments ON qryHirePrice.Hi rePriceID =
qryPayments.Hir ePriceID
GROUP BY qryHirePrice.Hi rePriceID, qryHirePrice.Hi rePrice,
qryHirePrice.Pa rtyID
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.OpenRecordse t("qryHirePrice Payment")

thanks
Allan

[storrboy]

How does it fail? What error are you getting if any?

[allanx38]

On Mar 19, 1:13 am, "storrboy" <storr...@sympa tico.cawrote:

How does it fail? What error are you getting if any?

Thanks guys. I had fallen back on the SQL string idea but it strikes
me as a bit long winded, call me lazy ...

The original error I get is 3061, Too few parameters. Expected 1.
Maybe I can pass the parameter in using a position as opposed to via a
name as I usually do.

Allan

[bobh]

Does the query run as just as query???
bobh.
On Mar 18, 8:48 pm, "allanx38" <allan...@yahoo .co.ukwrote:

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.Hi rePriceID, qryHirePrice.Hi rePrice,
Sum(qryPayments .PaymentAmount) AS SumOfPaymentAmo unt, [HirePrice]-
[SumOfPaymentAmo unt] AS Balance, qryHirePrice.Pa rtyID
FROM qryHirePrice INNER JOIN qryPayments ON qryHirePrice.Hi rePriceID =
qryPayments.Hir ePriceID
GROUP BY qryHirePrice.Hi rePriceID, qryHirePrice.Hi rePrice,
qryHirePrice.Pa rtyID
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.OpenRecordse t("qryHirePrice Payment")

thanks
Allan