By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

query question

P: n/a
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

Mar 19 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Mar 19 '07 #2

P: n/a

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

Mar 19 '07 #3

P: n/a
On Mar 19, 1:13 am, "storrboy" <storr...@sympatico.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

Mar 19 '07 #4

P: n/a
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.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

Mar 19 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.