Too Few Parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I'm sure someone has seen this before. I'm stuck. I have a module that
I'm using to send some reports automatically using a looping code. This
technique seemed to work fine in my other databases but suddenly is not.
Here's what I have:

Set db = CurrentDb
Set rsTM = db.OpenRecordset("SELECT * FROM SalesTerritory")

If Not rsTM.EOF Then
Do While Not rsTM.EOF

Set rsCore = db.OpenRecordset("SELECT * " & _
"FROM [vwPerformanceTerritory] WHERE
[TerritoryID] = rsTM![TerritoryID]")

CoreLBDValue = Nz(rsCore![ShipDollarsLBD], 0)
CoreMTDValue = Nz(rsCore![ShipDollarsMTD], 0)
etc...

The error is happening on the line entitled Set rsCore=...

I guess I'm supposed to define parameters or something but I'm not sure how.
Any suggestion would be great. Thanks!
 
Check out the where clause.

Set rsCore = db.OpenRecordset("SELECT * " & _
"FROM [vwPerformanceTerritory] " & _
"WHERE [TerritoryID] = " & rsTM![TerritoryID])

HTH, Graeme.
 
Set rsCore = db.OpenRecordset("SELECT * " & _
"FROM [vwPerformanceTerritory] WHERE
[TerritoryID] = rsTM![TerritoryID]")
Click to expand...

The problem is that the OpenRecordset method has no way of knowing
what rsTM!TerritoryID might be. Concatenate the *value* rather than
including the *name* of the recordset field reference:

Set rsCore = db.OpenRecordset("SELECT * " & _
"FROM [vwPerformanceTerritory] WHERE
[TerritoryID] = " & rsTM![TerritoryID])


John W. Vinson[MVP]
 
Thanks for the reply. I tried your suggestions which seems to make sense,
setting a value equal to a value, but for some reason I'm still getting "too
few parameters, expected 3".

John Vinson said:
Set rsCore = db.OpenRecordset("SELECT * " & _
"FROM [vwPerformanceTerritory] WHERE
[TerritoryID] = rsTM![TerritoryID]")
Click to expand...

The problem is that the OpenRecordset method has no way of knowing
what rsTM!TerritoryID might be. Concatenate the *value* rather than
including the *name* of the recordset field reference:

Set rsCore = db.OpenRecordset("SELECT * " & _
"FROM [vwPerformanceTerritory] WHERE
[TerritoryID] = " & rsTM![TerritoryID])


John W. Vinson[MVP]
Click to expand...
 
Thanks for the reply. I tried your suggestions which seems to make sense,
setting a value equal to a value, but for some reason I'm still getting "too
few parameters, expected 3".
Click to expand...

That suggests that the query you're using for the recordset has
parameters itself, which aren't being filled in. These "parameters"
might be unrecognized fieldnames; if Access sees a name in brackets
which is not recognized as a field in any table in the query, it will
treat it as a parameter. Please post the SQL view of the query and the
context of your code.

John W. Vinson[MVP]
 
John,

You nailed it. I can't thank you enough for the help, and the crystal clear
explanation. Have a great weekend!
 
Back
Top