469,071 Members | 1,922 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,071 developers. It's quick & easy.

Recordset error 3061

Good evening all
I have a recordset that is created from a sql query the sql query is as follows
Expand|Select|Wrap|Line Numbers
  1. SELECT Item.OrderID, Item.ProdID, Item.Quantity
  2. FROM Item
  3. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 20 '13 #1

✓ answered by Seth Schrock

Try doing it this way:
Expand|Select|Wrap|Line Numbers
  1. Dim strQuery as String
  2.  
  3. strQuery = "SELECT OrderID, ProdID, Quantity " & _
  4.            "FROM Item WHERE OrderID = " & Me.Text14
  5.  
  6. 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.

7 1877
Seth Schrock
2,957 Expert 2GB
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!frmPickOrder!Text14. This could be because of a typo or the form not being open.
Mar 20 '13 #2
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
Expand|Select|Wrap|Line Numbers
  1. Set rs = db.OpenRecordset("sqlOrderDetails")
Mar 21 '13 #3
Seth Schrock
2,957 Expert 2GB
Try doing it this way:
Expand|Select|Wrap|Line Numbers
  1. Dim strQuery as String
  2.  
  3. strQuery = "SELECT OrderID, ProdID, Quantity " & _
  4.            "FROM Item WHERE OrderID = " & Me.Text14
  5.  
  6. 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.
Mar 21 '13 #4
zmbd
5,400 Expert Mod 4TB
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.
Mar 21 '13 #5
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.
Mar 21 '13 #6
Seth Schrock
2,957 Expert 2GB
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.
Mar 22 '13 #7
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.
Expand|Select|Wrap|Line Numbers
  1. Set dbSample = CurrentDb()
  2. Set qdfSample = dbSample.QueryDefs("sqlEnoughStock")
  3.  
  4. 'the below two lines add the query value from a form 
  5. qdfSample![Forms!frmPickOrder!cboOrderID] = _
  6. Forms![frmPickOrder]![cboOrderID]
  7.  
  8.     ordervalue = Forms![frmPickOrder]![cboOrderID]
  9.     qdfSample![Forms!frmPickOrder!cboOrderID] = ordervalue
  10.  
  11.     Set rst = qdfSample.OpenRecordset()
  12.  
  13. While Not rst.EOF
Mar 26 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Miranda Evans | last post: by
2 posts views Thread by fanfromfla | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.