Recordset error 3061

[315hughes]

Good evening all
I have a recordset that is created from a sql query the sql query is as follows

SELECT Item.OrderID, Item.ProdID, Item.Quantity
FROM Item
WHERE (((Item.OrderID)=[Forms]![frmPickOrder]![Text14]));

When i run the SQL i get the correct results but when i run my vba to use this as a recordset it comes up with the error 3061 too few parameters, expected 1. So i change the where clause in the SQL to be just

WHERE Item.OrderID=1

And that works fine so i am stumped. The frmPickOrder is the form which is open with the VBA running.
Any help would be greatly apprciated.
Kind Regards

[Seth Schrock]

When you run your code, is frmPickOrder open? The parameter is the WHERE clause. The fact that it works if you just use the value of 1 means that it isn't getting a value from Forms!frmPickOr der!Text14. This could be because of a typo or the form not being open.

[315hughes]

Afternonn Seth, thanks for the reply. Yes the page is open as the value is entered onto the page for which this vba runs on. Just abit more info if i enter into text14 the value 1 and run the query it comes back with the same results as when the value is set directly to 1 in the sql query.
So could it possiably be that Access cant use a SQL Query that requries values from a user form as a recordeset?? Also just to provide more info the SQL query has been created through the wizard so i am actually calling the sql name ie

Set rs = db.OpenRecordset("sqlOrderDetails")

[Seth Schrock]

Try doing it this way:

Dim strQuery as String

strQuery = "SELECT OrderID, ProdID, Quantity " & _
           "FROM Item WHERE OrderID = " & Me.Text14

Set rs = db.OpenRecordset(strQuery)

Even though it should be possible to do it your way, this makes it a little easier to troublshoot. Let me know how this works.

[zmbd]

315hughes:
Now is this a query that you've created thru the query designer or is this an SQL string that you're creating in VBA?

If it is in VBA, then please post the VBA code as there can be a few other issues.

[315hughes]

Evening Seth, thanks for that it now works. It must be because i was calling a Query that was created using Query designer. I wonder why this is??
Anyway thanks for all your help. And zmbd thanks for the response aswell.

[Seth Schrock]

You can certainly use querydefs (queries made with the query designer) as recordsets, but for some reason, the parameter wasn't getting its value. I'm not sure why. Anyway, glad my solution worked for you.

[315hughes]

Hi Seth, thanks for your help with this but i managed to find some code that can solve this issue without typing the full SQL manually. below is the code i have used. It is not the code for this scenario i was asking for but its the same pricipal just to help anyone in the future if they get this issue.

Set dbSample = CurrentDb()
Set qdfSample = dbSample.QueryDefs("sqlEnoughStock")

'the below two lines add the query value from a form 
qdfSample![Forms!frmPickOrder!cboOrderID] = _
Forms![frmPickOrder]![cboOrderID]

    ordervalue = Forms![frmPickOrder]![cboOrderID]
    qdfSample![Forms!frmPickOrder!cboOrderID] = ordervalue
    
    Set rst = qdfSample.OpenRecordset()

While Not rst.EOF