Creating a Recordset based on a Parameter Query?

[ridzz]

I am trying to create a recordset based on a parameter query. The query is getting two dates from a form. I can create a recordset based on a normal select query but get an error using the Parameter query saying that "Too few parameters - two expected" using the parameter query.

 

[jebry]

Hi!

We really need to see the code to tell what is going on, but this problem usually is caused by including the reference to the text boxes inside the quotes of the string.
Here's what you would need to do if that is the problem:

Dim sql As String
Dim rst As DOS.Recordset

sql = "Select Yourfields From YourTable Where YourDateField Between #" & Me!YourFirstTextBox & "# And #" & Me!YourSecondTextBox & "#"

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Rolliee]

Jeff,
looks to me like you need a final "" I am not sure you need the seim colon but you do need the parenthassssssess.

rollie@bwsys.net

 

[jebry]

Hi Rollie!

Actually, the parentheses are unnecessary also, though they can at times make the SQL more readable. They are needed only when you want the program to determine clauses in a non-standard order.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[VoodooRageII]

Thanks for the help guys.

Here is the code.

Form beginning date is Forms!FrmCombo24
Form Ending date is Forms!FrmCombo26

The SQL for the query is as follows
SELECT UPC_PRODUCT_HRS.Date, UPC_PRODUCT_HRS.UPC, UPC_PRODUCT_HRS.Type, UPC_PRODUCT_HRS.Prod, UPC_PRODUCT_HRS.Ovt, UPC_PRODUCT_HRS.UnReptHrs

FROM UPC_PRODUCT_HRS
WHERE (((UPC_PRODUCT_HRS.Date)>=[Forms]![FrmViewUPCCodes]![Combo24] And (UPC_PRODUCT_HRS.Date)<=[Forms]![FrmViewUPCCodes]![Combo26]))

ORDER BY UPC_PRODUCT_HRS.Date;

I tried pasting this but the syntax is off.

 

[VoodooRageII]

Thanks for the help guys.

Here is the code.

Form beginning date is Forms!FrmCombo24
Form Ending date is Forms!FrmCombo26

The SQL for the query is as follows
SELECT UPC_PRODUCT_HRS.Date, UPC_PRODUCT_HRS.UPC, UPC_PRODUCT_HRS.Type, UPC_PRODUCT_HRS.Prod, UPC_PRODUCT_HRS.Ovt, UPC_PRODUCT_HRS.UnReptHrs

FROM UPC_PRODUCT_HRS
WHERE (((UPC_PRODUCT_HRS.Date)>=[Forms]![FrmViewUPCCodes]![Combo24] And (UPC_PRODUCT_HRS.Date)<=[Forms]![FrmViewUPCCodes]![Combo26]))

ORDER BY UPC_PRODUCT_HRS.Date;

I tried pasting this but the syntax is off.

 

[VoodooRageII]

Thanks for the help guys.

Here is the code.

Form beginning date is Forms!FrmCombo24
Form Ending date is Forms!FrmCombo26

The SQL for the query is as follows
SELECT UPC_PRODUCT_HRS.Date, UPC_PRODUCT_HRS.UPC, UPC_PRODUCT_HRS.Type, UPC_PRODUCT_HRS.Prod, UPC_PRODUCT_HRS.Ovt, UPC_PRODUCT_HRS.UnReptHrs

FROM UPC_PRODUCT_HRS
WHERE (((UPC_PRODUCT_HRS.Date)>=[Forms]![FrmViewUPCCodes]![Combo24] And (UPC_PRODUCT_HRS.Date)<=[Forms]![FrmViewUPCCodes]![Combo26]))

ORDER BY UPC_PRODUCT_HRS.Date;

I tried pasting this but the syntax is off.

 

[VoodooRageII]

Thanks for the help guys.

Here is the code.

Form beginning date is Forms!FrmCombo24
Form Ending date is Forms!FrmCombo26

The SQL for the query is as follows
SELECT UPC_PRODUCT_HRS.Date, UPC_PRODUCT_HRS.UPC, UPC_PRODUCT_HRS.Type, UPC_PRODUCT_HRS.Prod, UPC_PRODUCT_HRS.Ovt, UPC_PRODUCT_HRS.UnReptHrs

FROM UPC_PRODUCT_HRS
WHERE (((UPC_PRODUCT_HRS.Date)>=[Forms]![FrmViewUPCCodes]![Combo24] And (UPC_PRODUCT_HRS.Date)<=[Forms]![FrmViewUPCCodes]![Combo26]))

ORDER BY UPC_PRODUCT_HRS.Date

I tried pasting this but the syntax is off.

 

[oharab]

Hi Voodoo.
I am assuming that you are using a saved query and want to make a recordset based on that. To do that, you need to explicitly pass the parameters.

All this code is in DAO. make sure you have set a reference:

Method 1 (the proper way!):
Dim db As DAO.Database
Dim qd as DAO.QueryDef
Dim rs as DAO.Recordset

Set db=currentdb
set qd=db.querydefs(&quot;QueryName&quot
qd(&quot;[Forms]![FrmViewUPCCodes]![Combo24]&quot=[Forms]![FrmViewUPCCodes]![Combo24]
qd(&quot;[Forms]![FrmViewUPCCodes]![Combo26]&quot=[Forms]![FrmViewUPCCodes]![Combo26]

set rs=qd.openrecordset

Method 2 (The quicker way):
Dim db As DAO.Database
Dim qd as DAO.QueryDef
Dim rs as DAO.Recordset
Dim prm as DAO.Parameter

Set db=currentdb
set qd=db.querydefs(&quot;QueryName&quot

for each prm in qd.parameters
prm.value=eval(prm.value)
next prm

Method 3 (An alternative way):
Take a butchers at faq701-1964.
This is my preferred way, for no particular reason.

Some of the syntax may ba a bit out, but I hope you get the picture.

HTH

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[jebry]

Hi again!

If the query is a stored procedure then all you need to do is this:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(&quot;YourQuery&quot;, dbOpenDynaset)

hth
Jeff Bridgham
bridgham@purdue.edu

 

[oharab]

A stored query is not the same as a stored procedure. Apart from anything else, Stored Procedures are SQL Server specific.
Just opening &quot;YourQuery&quot; as a recordset will not work if it has parameters. That is why youmust open it as a query first, supply it with the parameters and then open it as a recordset.

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[jebry]

Hi Ben!

Normally I would agree with you but, if the parameters aren't really parameters, as in this case, then you can open the query this way. As Voodoo has indicated the information that the query needs is in controls on a form and, as long as the form is open, you can open the query as a recordset the way I have indicated.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[oharab]

Nope!
When you open a query from the Access window, access passes the parameters to the Jet database engine. When you are using recordsets, you are manipluating data closer to the Jet engine level and have to pass the parameters explicitly. If you don't then you get a runtime error 3061: Too few parameters.
After your post Jebry, I wasn't sure so I tried your method & got the runtime error.

BTW: I've found a bug in my method 2:
for each prm in qd.parameters
prm.value=eval(prm.value)
next prm

should be

for each prm in qd.parameters
prm.value=eval(prm.name)
next prm


Regards

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[jebry]

Hi Ben!

Absolutely correct, I was confusing opening a recordset and RunQuery. Sometimes I can be dense!


Jeff Bridgham
bridgham@purdue.edu