run-time error 3061 too few parameters expected 1 Set rs = db.OpenRecordset

[baruc308]

I cant run this. please help! the error is

run-time error 3061 too few parameters expected 1

in this line --------->> Set rs = db.OpenRecordse t <---- below

Public Function SendEmailAdvice() As String

Dim db As DAO.Database, rs As DAO.Recordset
Dim sSQL As String

   'Set environment
   Set db = CurrentDb
   DoCmd.SetWarnings False
   
   'Open the controlling recordset
   Set rs = db.OpenRecordset("qrySendEmailAdvice")
   While Not rs.EOF
       
       'For each record (Booking) send an email
      'For each record (Booking) send an email
             
       
       DoCmd.SendObject acSendNoObject, , , rs![Emails], "steveo@lifetimebrands.com; larryo@lifetimebrands.com; richo@lifetimebrands.com; baruc.estrada@lifetimebrands.com", , "Service Reminder " & rs![Type] & " # " & rs![Truck_ID], "Service for " & rs![Type] & " # " & rs![Truck_ID] & "  has been scheduled for " & rs![Day] & " " & rs![Scheduled Date] & Chr$(13) & Chr$(13) & _
       "This is a reminder." & Chr$(13) & Chr$(13) & _
       "If you cannot send the truck down for service please let us know before 12 noon today."
       
       
       'Update the SM table so that emails are not duplicated
       sSQL = "UPDATE SM and PM Monthly Schedule SET EmailSent=-1 WHERE ID=" & rs![ID]
   
       
       'Cycle on to the next record
       rs.MoveNext
   Wend
   rs.Close
   Set rs = Nothing
   'Control recordset now closed
   
   'Reset environment
   DoCmd.SetWarnings True

End Function

[Seth Schrock]

Please use the [CODE/] tags around your code so that it is easier to view.

It is telling you that your query qrySendEmailAdv ice is requesting a parameter (there is a WHERE clause in your query that needs a value passed to it). There are two options to fix this. The complexity of the your query would the the deciding factor for me as to which one to choose. You could type out your query in VBA and thus have the value entered into the query string before you try to open the recordset. However, if the query is quite large in the amount of text, then it might be simpler to just remove the WHERE clause from the query and then open the recordset and then filter the recordset in VBA. However, this wouldn't work if other objects are depending on the query to have the WHERE clause.

Also, your UPDATE query toward the end is never ran plus it would fail if it did run because the syntax is wrong.

[NeoPa]

Some good points.

However, while the likelihood is that the parameter is required for a reference within the WHERE clause, it can actually come from anywhere in the SQL. Any reference that the SQL engine (Jet in MS Access) cannot determine is treated as an enterable parameter.

I suggest you run your query from the database window and note the prompt. That will tell you what is not recognised by SQL and what you need to fix in your query.

NB. It is never a good idea to use a QueryDef from code that you haven't already tested this way.