"Too Few Parameters" error

[Mitzvetsia]

I'm trying to allow database users to click on a button on a form and send e-mail to certain clients based on what criteria they select from a drop-down box on said form.

The send e-mail function works fine - as long as there are no criteria/parameters/clauses (in my case, these criteria reference the combo boxes on the form) in the query from which the e-mail addresses are pulled. Criteria results in an "too few parameters, expected X#" error.

I'm pretty new to both Access and working with VBA. I've seen recommendations for building the query in VBA, but that does not fix the parameters error for me.

Though it seems like a bandaid, I thought that if I could write code to make and save a table based on the query, and then run the e-mail function based on that table, that might work - but I can't figure out how to do it.

I hope this makes any sense at all. Thanks for any suggestions!


Here's the code for send email function, in case that helps:

Option Compare Database

Public Function SendMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

Set MyOutlook = New Outlook.Application

Set db = CurrentDb()

Set MailList = db.OpenRecordset("qryVBATest")

Set MyMail = MyOutlook.CreateItem(olMailItem)

Do Until MailList.EOF

If IsNull(MailList("email")) Then MailList.MoveNext

If IsNull(MailList("email")) Then MailList.MoveNext

If IsNull(MailList("email")) Then MailList.MoveNext

MyMail.Recipients.Add MailList("email")

MailList.MoveNext

Loop

'MyMail.Send

On Error Resume Next

MyMail.Display


Set MyMail = Nothing

Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

[Delerna]

So the query is referencing the combo boxes on the form.
When something is selected in them, that is when the error occurs.
Am I reading your post right?
Is it this line where the error occurs
[code=vba]
Set MailList = db.OpenRecordse t("qryVBATest ")
[/code]
If so
Therefore the error is actually occuring in the query and not in the VBA code.
Maybe you should post the query.


Or is the error happening when the button is clicked
Then post the button click code


whatever the case, your email function code is working, as you have stated.

[Mitzvetsia]

I'm sorry I wasn't clear - this is all so new to me.

Here is the code for running the Send Email function from a query after clicking a button on a form. This works just fine:

Option Compare Database

Private Sub Command0_Click( )
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim tbl As DAO.TableDef


Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Applica tion
Dim MyMail As Outlook.MailIte m
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObjec t
Dim MyBody As TextStream
Dim MyBodyText As String

Set db = CurrentDb


Set db = CurrentDb
Set qdf = db.QueryDefs("S endEmail")


strSql = "SELECT * FROM Contacts;"
qdf.SQL = strSql


Set qdf = Nothing
Set db = Nothing




Set fso = New FileSystemObjec t

Set MyOutlook = New Outlook.Applica tion

Set db = CurrentDb()

Set MailList = db.OpenRecordse t("SendEmail" )

Set MyMail = MyOutlook.Creat eItem(olMailIte m)

Do Until MailList.EOF

If IsNull(MailList ("email")) Then MailList.MoveNe xt

If IsNull(MailList ("email")) Then MailList.MoveNe xt

If IsNull(MailList ("email")) Then MailList.MoveNe xt

MyMail.Recipien ts.Add MailList("email ")

MailList.MoveNe xt

Loop

'MyMail.Send

On Error Resume Next

MyMail.Display


Set MyMail = Nothing

Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Sub

However, if I want to apply criteria to the query so that I can send e-mail to only some of the clients, the code gets upset over parameters and gives me "Run-time Error '3061': Too Few Parameters. Expected 1".

Here's the code for the query with criteria:

Option Compare Database

Private Sub Command0_Click( )
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim tbl As DAO.TableDef

Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Applica tion
Dim MyMail As Outlook.MailIte m
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObjec t
Dim MyBody As TextStream
Dim MyBodyText As String


Set db = CurrentDb


Set db = CurrentDb
Set qdf = db.QueryDefs("S endEmail")


strSql = "SELECT * FROM Contacts WHERE Source = Forms.SendEmail .cmbReferalSour ce;"
qdf.SQL = strSql

DoCmd.OpenQuery ("SendEmail" )

Set qdf = Nothing
Set db = Nothing

Set fso = New FileSystemObjec t

Set MyOutlook = New Outlook.Applica tion

Set db = CurrentDb()

Set MailList = db.OpenRecordse t("SendEmail" )

Set MyMail = MyOutlook.Creat eItem(olMailIte m)

Do Until MailList.EOF

If IsNull(MailList ("email")) Then MailList.MoveNe xt

If IsNull(MailList ("email")) Then MailList.MoveNe xt

If IsNull(MailList ("email")) Then MailList.MoveNe xt

MyMail.Recipien ts.Add MailList("email ")

MailList.MoveNe xt

Loop

'MyMail.Send

On Error Resume Next

MyMail.Display


Set MyMail = Nothing

Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing


End Sub

Thanks for your help!

[Delerna]

"Run-time Error '3061': Too Few Parameters. Expected 1".

But which line is that occuring on?

[Delerna]

what heppens if you remove this
[code=vba]
Set db = CurrentDb
Set qdf = db.QueryDefs("S endEmail")
strSql = "SELECT * FROM Contacts WHERE Source = Forms.SendEmail .cmbReferalSour ce;"
qdf.SQL = strSql

DoCmd.OpenQuery ("SendEmail" )
Set qdf = Nothing
Set db = Nothing
[/code]

and change this

[code=vba]
Set MailList = db.OpenRecordse t("SendEmail" )

[/code]

to this

[code=sql]
Set MailList = db.OpenRecordse t("SELECT * FROM Contacts WHERE Source = Forms.SendEmail .cmbReferalSour ce;")
[/code]

in your

Private Sub Command0_Click( )

I don't know, Im just stabbing in the dark because I don't know which line the error is occurring on


Also, if you look closely at that sub
you are opening and closing a connection to currentdb multiple times in the same sub
Why?
open it once at the start and close it once at the end and use it multiple times in the middle

[Delerna]

Untested cleaned up code
[code=vba]
Private Sub Command0_Click( )
Dim db As DAO.Database , MailList As DAO.Recordset
Dim MyOutlook As Outlook.Applica tion , MyMail As Outlook.MailIte m
Dim Subjectline As String , BodyFile As String , MyBodyText As String
Dim MyBody As TextStream

Set db = CurrentDb
Set MyOutlook = New Outlook.Applica tion
Set MyMail = MyOutlook.Creat eItem(olMailIte m)

Set MailList = db.OpenRecordse t("SELECT email FROM Contacts WHERE Source = Forms!SendEmail .cmbReferalSour ce and email is not null;")
Do Until MailList.EOF
MyMail.Recipien ts.Add MailList("email ")
MailList.MoveNe xt
Loop

'MyMail.Send

On Error Resume Next

MyMail.Display

Set MyMail = Nothing
Set MyOutlook = Nothing
MailList.Close : Set MailList = Nothing
db.Close : Set db = Nothing
End Sub
[/code]

[Mitzvetsia]

In the original code the error occurs here:

Set MailList = db.OpenRecordse t("SendEmail" )

In the cleaned up version the error occurs here:

Set MailList = db.OpenRecordse t("SELECT email FROM Contacts WHERE Source = Forms!SendEmail .cmbReferalSour ce and email is not null;")

The problem seems to stem from the WHERE clause - if I remove it from either version of code, everything works fine.

Thanks for the cleaned up code - I literally know next to nothing about vba and have put this together based on what I've learned from Google and forums - a process akin to using a dictionary to write something a foreign language - hence the repeated and unnecessary processes.

[Stewart Ross]

Hi. Access has a problem when referring to form controls within SQL statements. The query editor can interpret them OK in most circumstances, but they cannot be interpreted successfully from VB recordset operations.

Luckily, with a small modification to your code you should be able to place the value from the control into the Where clause in place of the explict reference to the control itself:

Set MailList = db.OpenRecordset ( _
"SELECT email FROM Contacts WHERE Source = '" & _
Forms!SendEmail.cmbReferalSource & "' and email is not null;")

The single-quotes will enclose the string value returned from the control, and it is essential that they are included. They would not be needed if a numeric value was being returned, but with strings they must be used.

-Stewart