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.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

[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.HirePriceID..." _
& "...HAVING qryHirePrice.PartID = " _
& [Forms]![frmParty]![PartyID]

Set rst = db.OpenRecordset(strSQL)

hth
"allanx38" <al******@yahoo.co.ukwrote in message
news:11**********************@e65g2000hsc.googlegr oups.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.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

[storrboy]

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

[allanx38]

On Mar 19, 1:13 am, "storrboy" <[e-mail]:

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" <[e-mail]:

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