Too Few Parameters. Expected 1 (Recordset/Query)

[phill86]

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

[ADezii]

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:

Dim MySQL As String
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
 
'Assuming [SFEquipID] is Numeric     
MySQL = "SQL from Q_SelEquipTmpTbl with the WHERE Clause similar to [SomeField] Like " & _
[Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)

[NeoPa]

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) :

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.

[ADezii]

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) :

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).

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
 
Dim dbsClient As DAO.Database
Dim rstCheck As DAO.Recordset
Dim qdf As DAO.QueryDef
 
Set dbsClient = CurrentDb()
Set qdf = dbsClient.QueryDefs("Q_SelEquipTmpTbl")
 
qdf.Parameters(0) = [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
 
Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
 
Do While Not rstCheck.EOF
  'Loop code here, set to Read Only
  rstCheck.MoveNext
Loop
 
rstCheck.Close
Set rstCheck = Nothing

[NeoPa]

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.

[ADezii]

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.

[NeoPa]

... 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.

[phill86]

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).

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
 
Dim dbsClient As DAO.Database
Dim rstCheck As DAO.Recordset
Dim qdf As DAO.QueryDef
 
Set dbsClient = CurrentDb()
Set qdf = dbsClient.QueryDefs("Q_SelEquipTmpTbl")
 
qdf.Parameters(0) = [Forms]![F_EquipBooking].[SF_EquipBooking]![SFEquipID]
 
Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
 
Do While Not rstCheck.EOF
  'Loop code here, set to Read Only
  rstCheck.MoveNext
Loop
 
rstCheck.Close
Set rstCheck = Nothing

Hi ADezii

its sorted thanks for your help

Cheers Phill

[phill86]

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) :

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

[NeoPa]

Good for you Phill, and thanks for letting us know :)