Run-time error 3061 when trying to email query results that is linked to a form

[cykchanaa]

I am trying to create a form button that will automatically email each row of a query result to myself. At first the VBA code worked fine with a standard query. However when I use it with a query that contains a reference to a combobox form such as "<=[Forms]![Reminder]![Monthsleft].[Value] And >=0" I get the 3061 run-time error and "Too few parameters. Expected 1." I have included the VBA code below.

Private Sub Command9_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Query2", dbOpenDynaset)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
sToName = "CPPKENC"
sSubject = "Lease Reminder " & .Fields(4) & " in " & .Fields(3) & " will expire on " & .Fields(7)
sMessageBody = "We note that the lease of your Engineering Office in Hyderabad will expire on " & .Fields(7) & " If you intend to renew the lease, terms and conditions will need to be submitted for ECC for approval (regardless of changes or not in lease rates). If the terms have yet to be confirmed, it is important to begin the negotiation process as soon as possible with a target to provide the ECC submission at least two months prior to the commencement date of the renewed lease. To ensure sufficient time for ECC approval before the contract expiry date, please prepare the ECC paper and obtain necessary endorsements. Submission details can be found here. The ECC submission template and PSD Questionnaire could be found from this link. Please clearly presents the terms & rent of current and new lease for easy reference. Please do not hesitate to contact us should you require any assistance or guidance in respect of the lease renewal negotiations. Regards, Shirley"


DoCmd.SendObject acSendNoObject, , , _


sToName, , , sSubject, sMessageBody, False, False

.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

[pbaldy]

http://access.mvps.org/access/queries/qry0013.htm

[cykchanaa]

http://access.mvps.org/access/queries/qry0013.htm

Thanks for sending me the link, I think it explains what my problem is. However I don't know how to modify the code as I am new to VBA and copied this code from another forum post.

[June7]

Another alternative is to not use a dynamic parameterized query object (I don't). Construct the SQL statement in VBA. Something like:

Set rsEmail = MyDb.OpenRecordset("SELECT * FROM table/query WHERE fieldname BETWEEN 0 AND " & Me.Monthsleft, dbOpenDynaset)

[pbaldy]

Thanks for sending me the link, I think it explains what my problem is. However I don't know how to modify the code as I am new to VBA and copied this code from another forum post.

Here's the relevant bit from one of my apps (strQuery is a variable containing the name of a query):

  Set qdfStatement = db.QueryDefs(strQuery)
  qdfStatement![Forms!frmMainMenu!txtDate] = Forms![frmMainMenu]![txtDate]
  qdfStatement![Forms!frmMainMenu!txtEndDate] = Forms![frmMainMenu]![txtEndDate]


  Set rs = qdfStatement.OpenRecordset