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

Too Few Parameters. Expected 1 (Recordset/Query)

100+
P: 121
Hi,

I get the Too Few Parameters. Expected 1 error message when i run the following code
Expand|Select|Wrap|Line Numbers
  1. Set dbsClient = CurrentDb
  2.     Set rstCheck = _
  3.     dbsClient.OpenRecordset("Q_SelEquipTmpTbl", dbOpenDynaset)
I am referencing a combo box on a form in the query criteria
Expand|Select|Wrap|Line Numbers
  1. Like [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
when i take the reference to the combo box out of the query it works.

please help

Regards Phill
May 1 '08 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,601
Hi,

I get the Too Few Parameters. Expected 1 error message when i run the following code

Set dbsClient = CurrentDb
Set rstCheck = _
dbsClient.OpenRecordset("Q_SelEquipTmpTbl", dbOpenDynaset)

I am referencing a combo box on a form in the query criteria

Like [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]

when i take the reference to the combo box out of the query it works.

please help

Regards Phill
There is obviously a problem resolving the single Query Parameter. You can always Open the Recordset directly from the SQL in Q_SelEquipTmpTbl, namely:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2. Dim MyDB As DAO.Database
  3. Dim MyRS As DAO.Recordset
  4.  
  5. 'Assuming [SFEquipID] is Numeric     
  6. MySQL = "SQL from Q_SelEquipTmpTbl with the WHERE Clause similar to [SomeField] Like " & _
  7. [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
  8.  
  9. Set MyDB = CurrentDb()
  10. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
May 1 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
As a matter of interest, post what you get running the following SQL (in the same circumstances where you get the above error running your query) :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
You will have to run it from the SQL view as it (run) won't be available from any other view.
May 1 '08 #3

ADezii
Expert 5K+
P: 8,601
As a matter of interest, post what you get running the following SQL (in the same circumstances where you get the above error running your query) :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
You will have to run it from the SQL view as it (run) won't be available from any other view.
Hello NeoPa, I could be wrong but I think that the problem is a little more sinister than it actually appears. I don't think that you can create a Traditional Recordset on a QueryDef Object whose Parameter is a Control on a Form/Sub-Form. I don't think that it can be resolved in this fashion. The code in Post #2, however, will work and also the code posted below, as long as the Like Operator is not utilized. As always, let me know what you think - I'm probably wrong, anyway, ergo not directing the Reply to the OP. (LOL).
Expand|Select|Wrap|Line Numbers
  1. NOTE: The actual Prompt you use in Q_SelEquipTmpTbl is irrelevant, since the only Parameter is indicated by an Index, and not a literal String Value
  2.  
  3. Dim dbsClient As DAO.Database
  4. Dim rstCheck As DAO.Recordset
  5. Dim qdf As DAO.QueryDef
  6.  
  7. Set dbsClient = CurrentDb()
  8. Set qdf = dbsClient.QueryDefs("Q_SelEquipTmpTbl")
  9.  
  10. qdf.Parameters(0) = [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
  11.  
  12. Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
  13.  
  14. Do While Not rstCheck.EOF
  15.   'Loop code here, set to Read Only
  16.   rstCheck.MoveNext
  17. Loop
  18.  
  19. rstCheck.Close
  20. Set rstCheck = Nothing
May 2 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
ADezii, You posted the same thing twice so I deleted the first one.

The SQL I suggested should work. It's a little weird I know but give it a try ;)

It's not to fix the problem though. Simply to provide some information and hopefully identify what might NOT be the problem.
May 2 '08 #5

ADezii
Expert 5K+
P: 8,601
ADezii, You posted the same thing twice so I deleted the first one.

The SQL I suggested should work. It's a little weird I know but give it a try ;)

It's not to fix the problem though. Simply to provide some information and hopefully identify what might NOT be the problem.
You posted the same thing twice so I deleted the first one.
Thanks NeoPa
The SQL I suggested should work.
The SQL you posted will definately work. What I was referring to was the OP's logic, namely: Creating a Recordset on a Query whose Criteria is a Control on a Sub-Form.
May 2 '08 #6

NeoPa
Expert Mod 15k+
P: 31,186
... namely: Creating a Recordset on a Query whose Criteria is a Control on a Sub-Form.
Although I very rarely use that concept myself (I build up SQL strings in VBA where possible), I know that most people seem to use that as a standard way of filtering on selected (form) items. I think that's what you're referring to. Excuse me if I've misunderstood again.

For it to work, the reference must be an absolute one (usually means using the Forms() collection). This is because the SQL engine (processor) is not executing from within the current code but is independent of it.
May 2 '08 #7

100+
P: 121
Hello NeoPa, I could be wrong but I think that the problem is a little more sinister than it actually appears. I don't think that you can create a Traditional Recordset on a QueryDef Object whose Parameter is a Control on a Form/Sub-Form. I don't think that it can be resolved in this fashion. The code in Post #2, however, will work and also the code posted below, as long as the Like Operator is not utilized. As always, let me know what you think - I'm probably wrong, anyway, ergo not directing the Reply to the OP. (LOL).
Expand|Select|Wrap|Line Numbers
  1. NOTE: The actual Prompt you use in Q_SelEquipTmpTbl is irrelevant, since the only Parameter is indicated by an Index, and not a literal String Value
  2.  
  3. Dim dbsClient As DAO.Database
  4. Dim rstCheck As DAO.Recordset
  5. Dim qdf As DAO.QueryDef
  6.  
  7. Set dbsClient = CurrentDb()
  8. Set qdf = dbsClient.QueryDefs("Q_SelEquipTmpTbl")
  9.  
  10. qdf.Parameters(0) = [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
  11.  
  12. Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
  13.  
  14. Do While Not rstCheck.EOF
  15.   'Loop code here, set to Read Only
  16.   rstCheck.MoveNext
  17. Loop
  18.  
  19. rstCheck.Close
  20. Set rstCheck = Nothing
Hi ADezii

its sorted thanks for your help

Cheers Phill
May 2 '08 #8

100+
P: 121
As a matter of interest, post what you get running the following SQL (in the same circumstances where you get the above error running your query) :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
You will have to run it from the SQL view as it (run) won't be available from any other view.
Hi NeoPa,

Thanks its sorted

Cheers Phill
May 2 '08 #9

NeoPa
Expert Mod 15k+
P: 31,186
Good for you Phill, and thanks for letting us know :)
May 2 '08 #10

Post your reply

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