Code not working when parameters added, too few parameters expected 3

[ryan langley]

I have a access query which i filter using 3 combo boxes within a form (day, location, id). I have created a function to populate a access email with email addresses form the query this worked fine until i added parameters to my query it now give the error "too few parameters expected 3 . I would really appreciate help on this. Please see code below and image of my query attached:

Private Sub BCCMail_Click()
On Error GoTo Err_BCCMail_Click
 
Dim rst As DAO.Recordset
Dim MyOutlook As New Outlook.Application
Dim MyMail As Outlook.MailItem
Dim StrBCC As String
Dim stDocName As String
 
   stDocName = "QEmail"
Set rst = CurrentDb.OpenRecordset(stDocName, dbOpenForwardOnly)
With rst
Do Until .EOF
StrBCC = StrBCC & ![E-mail] & ";"
.MoveNext
Loop
.Close
End With
StrBCC = Left(StrBCC, Len(StrBCC) - 1)
 
Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.BCC = StrBCC
MyMail.Display
 
Set MyOutlook = Nothing
Set rst = Nothing
 
Exit_BCCMail_Click:
    Exit Sub
 
Err_BCCMail_Click:
    MsgBox Err.Description
    Resume Exit_BCCMail_Click
 
End Sub
 

[Stewart Ross]

Hi. Problem is that the references to form controls in your where clause can't be interpreted by VBA as field values when executed in code as above. Solution to this is to supply explicit parameters to the query using suitable code, as outlined in the DAO querydef example about half-way down the following explanatory article from MSDN:

Everything About Using Parameters from Code

You would need to replace your current OpenRecordset method in line 11 with something based on the example shown by MSDN, where the recordset is opened from a querydef in which you supply the appropriate parameter values:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
 
Set db = CurrentDb
Set qdf = db.QueryDefs(stDocName)
qdf.Parameters(0) = Forms!yourformname!yourfield1
qdf.Parameters(1) = Forms!yourformname!yourfield2
qdf.Parameters(2) = Forms!yourformname!yourfield3
 
Set rst = qdf.OpenRecordset

I can't read the control names from the low definition jpeg you supply. I also cannot tell what the order of the three parameters in your query will be - you will need to find this out yourself.

-Stewart