DAO Recordset from Select Query - Runtime 3061 - Too Few Parameters. Expected 1.

[Viv Jones]

Hi

I am trying to update a recordset, however, I cannot get the code to run as i get Runtime Error: 3061. Too Few Parameters. Expected 1. Line 6 is highlighted when you debug. Any assistance would be greatly appreciated.

Sub CalcLoop()
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_FleetRS", dbOpenDynaset)
 
rs.MoveFirst
 
Do While Not rs.EOF
 
rs.MoveNext
Loop
 
Call FleetCalc
 
rs.Close
 
Set rs = Nothing
Set db = Nothing
 
End Sub
 
 

[TheSmileyCoder]

It sounds like there might be an error in the SQL of qry_FleetRS. Does the query run without problems if you open it manually?

[Viv Jones]

Hi,

Yes it does. The underlying query is selecting all fields from a table where one field is equal to a field on an open form.

Thanks
Viv

[Viv Jones]

Here is the query in SQL

SELECT tbl_NCBCalcs.QuoteID, tbl_NCBCalcs.REGISTRATIONNO, tbl_NCBCalcs.NCBE1, tbl_NCBCalcs.NCBE2, tbl_NCBCalcs.NCBE3, tbl_NCBCalcs.NCBE4, tbl_NCBCalcs.NCBE5, tbl_NCBCalcs.StartingNCB, tbl_NCBCalcs.POIS1, tbl_NCBCalcs.POIE1, tbl_NCBCalcs.IAF1F, tbl_NCBCalcs.POIS2, tbl_NCBCalcs.POIE2, tbl_NCBCalcs.IAF2F, tbl_NCBCalcs.POIS3, tbl_NCBCalcs.POIE3, tbl_NCBCalcs.IAF3F, tbl_NCBCalcs.POIS4, tbl_NCBCalcs.POIE4, tbl_NCBCalcs.IAF4F, tbl_NCBCalcs.POIS5, tbl_NCBCalcs.POIE5, tbl_NCBCalcs.IAF5F, tbl_NCBCalcs.StartPOI1, tbl_NCBCalcs.StartPOI2, tbl_NCBCalcs.StartPOI3, tbl_NCBCalcs.StartPOI4, tbl_NCBCalcs.StartPOI5, tbl_NCBCalcs.PercentageE1, tbl_NCBCalcs.PercentageE2, tbl_NCBCalcs.PercentageE3, tbl_NCBCalcs.PercentageE4, tbl_NCBCalcs.PercentageE5, tbl_NCBCalcs.StartPOIDate, tbl_NCBCalcs.StartPOI6, tbl_NCBCalcs.NCBID
FROM tbl_NCBCalcs
WHERE (((tbl_NCBCalcs.QuoteID)=[Forms]![frm_Fleet]![QuoteID]));
 

[TheSmileyCoder]

I don't often use form references in query, so I am a bit rusty in that regard.

What happens if you try:

Sub CalcLoop()
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
dim lngQuoteID as long
lngQuoteID=Forms!frm_Fleet!QuoteID
Set db = CurrentDb
Dim strSQL as string
strSQL="SELECT tbl_NCBCalcs.QuoteID, tbl_NCBCalcs.REGISTRATIONNO, tbl_NCBCalcs.NCBE1, tbl_NCBCalcs.NCBE2, tbl_NCBCalcs.NCBE3, tbl_NCBCalcs.NCBE4, tbl_NCBCalcs.NCBE5, tbl_NCBCalcs.StartingNCB, tbl_NCBCalcs.POIS1, tbl_NCBCalcs.POIE1, tbl_NCBCalcs.IAF1F, tbl_NCBCalcs.POIS2, tbl_NCBCalcs.POIE2, tbl_NCBCalcs.IAF2F, tbl_NCBCalcs.POIS3, tbl_NCBCalcs.POIE3, tbl_NCBCalcs.IAF3F, tbl_NCBCalcs.POIS4, tbl_NCBCalcs.POIE4, tbl_NCBCalcs.IAF4F, tbl_NCBCalcs.POIS5, tbl_NCBCalcs.POIE5, tbl_NCBCalcs.IAF5F, tbl_NCBCalcs.StartPOI1, tbl_NCBCalcs.StartPOI2, tbl_NCBCalcs.StartPOI3, tbl_NCBCalcs.StartPOI4, tbl_NCBCalcs.StartPOI5, tbl_NCBCalcs.PercentageE1, tbl_NCBCalcs.PercentageE2, tbl_NCBCalcs.PercentageE3, tbl_NCBCalcs.PercentageE4, tbl_NCBCalcs.PercentageE5, tbl_NCBCalcs.StartPOIDate, tbl_NCBCalcs.StartPOI6, tbl_NCBCalcs.NCBID " & _
     " FROM tbl_NCBCalcs " & _
     " WHERE (((tbl_NCBCalcs.QuoteID)=" & lngQuoteID & "));
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
 
rs.MoveFirst
 
Do While Not rs.EOF
 
rs.MoveNext
Loop
 
Call FleetCalc
 
rs.Close
 
Set rs = Nothing
Set db = Nothing
 
End Sub

[Viv Jones]

Hi

That works, insofar as it no longer gives me the parameter error, however it is not looping through and updating all records. It only updates the first record and then stops.

Any suggestions?

[Stewart Ross]

You have a loop which is not doing anything at all at present

Do While Not rs.EOF
 rs.MoveNext
Loop
 

This just loops to the end of file. You have not posted the Update part of your code / SQL; I presume that it's done by the Call Fleetcalc line (line 21 in your original post).

Shouldn't that call be within the loop? I cannot be sure about this, as if you have reached the end of file any call afterwards is surely invalid, but as you have not posted the relevant section it's hard to know for sure.

-Stewart

[Viv Jones]

Thanks Stewart,

I just cannot seem to get it to move to the next record and perform the updates.

I have a query set up which retrieves the same records as the SQlStr in my code, and this query is returning the correct number of records.

I have not posted the code for Sub FleetCalc, as this in turn runs a number of update queries and calls other procedures dependant on which conditions are true for a particular record.

I have amended my code (below), which is updating perfectly on the first record in the recordset. Any further suggestions would be welcomed. I am no expert, so any assistance is greatly appreciated.

Sub CalcLoop()
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngQuoteID As Integer
 
lngQuoteID = Me!QuoteID
'QuoteID = Me.QuoteID
 
Set db = CurrentDb
 
Dim strSQL As String
 
strSQL = "SELECT tbl_NCBCalcs.QuoteID, tbl_NCBCalcs.REGISTRATIONNO, tbl_NCBCalcs.NCBE1, tbl_NCBCalcs.NCBE2, tbl_NCBCalcs.NCBE3, tbl_NCBCalcs.NCBE4, tbl_NCBCalcs.NCBE5, tbl_NCBCalcs.StartingNCB, tbl_NCBCalcs.POIS1, tbl_NCBCalcs.POIE1, tbl_NCBCalcs.IAF1F, tbl_NCBCalcs.POIS2, tbl_NCBCalcs.POIE2, tbl_NCBCalcs.IAF2F, tbl_NCBCalcs.POIS3, tbl_NCBCalcs.POIE3, tbl_NCBCalcs.IAF3F, tbl_NCBCalcs.POIS4, tbl_NCBCalcs.POIE4, tbl_NCBCalcs.IAF4F, tbl_NCBCalcs.POIS5, tbl_NCBCalcs.POIE5, tbl_NCBCalcs.IAF5F, tbl_NCBCalcs.StartPOI1, tbl_NCBCalcs.StartPOI2, tbl_NCBCalcs.StartPOI3, tbl_NCBCalcs.StartPOI4, tbl_NCBCalcs.StartPOI5, tbl_NCBCalcs.PercentageE1, tbl_NCBCalcs.PercentageE2, tbl_NCBCalcs.PercentageE3, tbl_NCBCalcs.PercentageE4, tbl_NCBCalcs.PercentageE5, tbl_NCBCalcs.StartPOIDate, tbl_NCBCalcs.StartPOI6, tbl_NCBCalcs.NCBID " & _
     " FROM tbl_NCBCalcs " & _
     " WHERE (((tbl_NCBCalcs.QuoteID)=" & lngQuoteID & "));"
 
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
 
rs.MoveFirst
 
Do Until rs.EOF
 
    Call FleetCalc  'Calls multiple procedures and queries to update almost every field in the recordset, dependant on a range of conditions.
 
    rs.MoveNext
 
Loop
 
rs.Close
 
Set rs = Nothing
Set db = Nothing
 
End Sub
 

[Viv Jones]

Thanks Stewart and TheSmileyCoder.

Without your assistance I would not have made it this far.

I have managed to resolve the last problem. It appears that I was dealing with a .EOF issue. I still have one more error to fix, and once complete I will post the final working code.