By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

"Too Few Parameters" error

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function SendMail()
  4.  
  5. Dim db As DAO.Database
  6. Dim MailList As DAO.Recordset
  7. Dim MyOutlook As Outlook.Application
  8. Dim MyMail As Outlook.MailItem
  9. Dim Subjectline As String
  10. Dim BodyFile As String
  11. Dim fso As FileSystemObject
  12. Dim MyBody As TextStream
  13. Dim MyBodyText As String
  14.  
  15. Set fso = New FileSystemObject
  16.  
  17. Set MyOutlook = New Outlook.Application
  18.  
  19. Set db = CurrentDb()
  20.  
  21. Set MailList = db.OpenRecordset("qryVBATest")
  22.  
  23. Set MyMail = MyOutlook.CreateItem(olMailItem)
  24.  
  25. Do Until MailList.EOF
  26.  
  27. If IsNull(MailList("email")) Then MailList.MoveNext
  28.  
  29. If IsNull(MailList("email")) Then MailList.MoveNext
  30.  
  31. If IsNull(MailList("email")) Then MailList.MoveNext
  32.  
  33. MyMail.Recipients.Add MailList("email")
  34.  
  35. MailList.MoveNext
  36.  
  37. Loop
  38.  
  39. 'MyMail.Send
  40.  
  41. On Error Resume Next
  42.  
  43. MyMail.Display
  44.  
  45.  
  46. Set MyMail = Nothing
  47.  
  48. Set MyOutlook = Nothing
  49.  
  50. MailList.Close
  51. Set MailList = Nothing
  52. db.Close
  53. Set db = Nothing
  54.  
  55. End Function
Feb 18 '10 #1
Share this Question
Share on Google+
7 Replies


Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. Set MailList = db.OpenRecordset("qryVBATest")
  2.  
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.
Feb 18 '10 #2

P: 3
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.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 db = CurrentDb


Set db = CurrentDb
Set qdf = db.QueryDefs("SendEmail")


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


Set qdf = Nothing
Set db = Nothing




Set fso = New FileSystemObject

Set MyOutlook = New Outlook.Application

Set db = CurrentDb()

Set MailList = db.OpenRecordset("SendEmail")

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 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.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 db = CurrentDb


Set db = CurrentDb
Set qdf = db.QueryDefs("SendEmail")


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

DoCmd.OpenQuery ("SendEmail")

Set qdf = Nothing
Set db = Nothing

Set fso = New FileSystemObject

Set MyOutlook = New Outlook.Application

Set db = CurrentDb()

Set MailList = db.OpenRecordset("SendEmail")

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 Sub

Thanks for your help!
Feb 19 '10 #3

Delerna
Expert 100+
P: 1,134
"Run-time Error '3061': Too Few Parameters. Expected 1".
But which line is that occuring on?
Feb 19 '10 #4

Delerna
Expert 100+
P: 1,134
what heppens if you remove this
Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
  2. Set qdf = db.QueryDefs("SendEmail")
  3. strSql = "SELECT * FROM Contacts WHERE Source = Forms.SendEmail.cmbReferalSource;"
  4. qdf.SQL = strSql
  5.  
  6. DoCmd.OpenQuery ("SendEmail")
  7. Set qdf = Nothing
  8. Set db = Nothing
  9.  
and change this

Expand|Select|Wrap|Line Numbers
  1. Set MailList = db.OpenRecordset("SendEmail")
  2.  
  3.  
to this

Expand|Select|Wrap|Line Numbers
  1. Set MailList = db.OpenRecordset("SELECT * FROM Contacts WHERE Source = Forms.SendEmail.cmbReferalSource;")
  2.  
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
Feb 19 '10 #5

Delerna
Expert 100+
P: 1,134
Untested cleaned up code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.    Dim db As DAO.Database , MailList As DAO.Recordset
  3.    Dim MyOutlook As Outlook.Application , MyMail As Outlook.MailItem
  4.    Dim Subjectline As String , BodyFile As String , MyBodyText As String
  5.    Dim MyBody As TextStream
  6.  
  7.    Set db = CurrentDb
  8.    Set MyOutlook = New Outlook.Application
  9.    Set MyMail = MyOutlook.CreateItem(olMailItem)
  10.  
  11.    Set MailList = db.OpenRecordset("SELECT email FROM Contacts WHERE Source = Forms!SendEmail.cmbReferalSource and email is not null;")
  12.    Do Until MailList.EOF
  13.       MyMail.Recipients.Add MailList("email")
  14.       MailList.MoveNext
  15.    Loop
  16.  
  17.    'MyMail.Send
  18.  
  19.    On Error Resume Next
  20.  
  21.    MyMail.Display
  22.  
  23.    Set MyMail = Nothing
  24.    Set MyOutlook = Nothing
  25.    MailList.Close : Set MailList = Nothing
  26.    db.Close : Set db = Nothing
  27. End Sub
  28.  
Feb 19 '10 #6

P: 3
In the original code the error occurs here:
Set MailList = db.OpenRecordset("SendEmail")
In the cleaned up version the error occurs here:

Set MailList = db.OpenRecordset("SELECT email FROM Contacts WHERE Source = Forms!SendEmail.cmbReferalSource 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.
Feb 19 '10 #7

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Set MailList = db.OpenRecordset ( _
  2. "SELECT email FROM Contacts WHERE Source = '" & _
  3. 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
Feb 20 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.