Runtime Error 3061. Too few parameters, expected 2

[Gina Maylone]

I'm moving the program off of a sharepoint server and onto a local server. Sharepoint does have some quirks.

[RuralGuy]

No problem. This is a tuffy one. I can't figure out where the problem lies.

[Dal Jeanis]

Just using NZ on the two parameters in the query fixes the issue in RuralGuy's version of the database:

EmployeeEmailQuery:
SELECT MoversScheduleQuery.EmailAddress, MoversScheduleQuery.FullName
FROM MoversScheduleQuery
WHERE (((MoversScheduleQuery.[Move Date]) Between NZ([Forms]![EmailEmployees]![fromdate]) And NZ([Forms]![EmailEmployees]![todate])));

That didn't solve the underlying problem, but it demonstrated that, for some unknown reason, Jet is having problems finding the controls on the form. I checked and verified the syntax, which is correct, and works except when invoked from VBA.

Along the way, I noticed that the parameters for EmployeeEmailQuery are redundant, because the underlying MoversScheduleQuery has identical parameters. Meaning that EmployeeEmailQuery doesn't need to exist, just use MoversScheduleQuery and use only the field you want.

I managed to make a working version by cloning to MoversScheduleQuery3 and changing the parameters to being explicit parameters, and setting their values before the call, from the form.

MoversScheduleQuery3:
SELECT TC.[Full Name], TC.[Move Date], TE.FullName, TC.[Start Time], TM.AddtoStartTime, TE.EmailAddress
FROM 
  (Employees AS TE 
   INNER JOIN 
   MoversSchedule AS TM 
   ON TE.EmployeeID = TM.EmployeeID) 
      INNER JOIN 
      [Customer Data] AS TC 
      ON TM.MoveID = TC.ID
WHERE 
  ( (TC.[Move Date]) BETWEEN ([Enter Date 1]) AND ([Enter Date 2]) );

Then this is the VBA to invoke the query:

   Set Db = CurrentDb
   Set qDef = Db.QueryDefs("MoversScheduleQuery3") 
   qDef.Parameters("Enter Date 1") = ([Forms]![EmailEmployees]![fromdate]) ' #01/01/2011#
   qDef.Parameters("Enter Date 2") = ([Forms]![EmailEmployees]![todate]) ' #01/01/2014#
   Set rsEmail = qDef.OpenRecordset(dbOpenSnapshot, dbReadOnly)

I also noticed that I'm getting strange intermittent messages looking for Excel.EXE. I tried to turn off all macros and VB that might have invoked it, but did not find the routine that's causing it.

[RuralGuy]

Interesting Dal. Nz() huh? Great catch!

[Gina Maylone]

Just using NZ on the two parameters in the query fixes the issue in RuralGuy's version of the database:

EmployeeEmailQuery:
SELECT MoversScheduleQuery.EmailAddress, MoversScheduleQuery.FullName
FROM MoversScheduleQuery
WHERE (((MoversScheduleQuery.[Move Date]) Between NZ([Forms]![EmailEmployees]![fromdate]) And NZ([Forms]![EmailEmployees]![todate])));

That didn't solve the underlying problem, but it demonstrated that, for some unknown reason, Jet is having problems finding the controls on the form. I checked and verified the syntax, which is correct, and works except when invoked from VBA.

Along the way, I noticed that the parameters for EmployeeEmailQuery are redundant, because the underlying MoversScheduleQuery has identical parameters. Meaning that EmployeeEmailQuery doesn't need to exist, just use MoversScheduleQuery and use only the field you want.

I managed to make a working version by cloning to MoversScheduleQuery3 and changing the parameters to being explicit parameters, and setting their values before the call, from the form.

MoversScheduleQuery3:
SELECT TC.[Full Name], TC.[Move Date], TE.FullName, TC.[Start Time], TM.AddtoStartTime, TE.EmailAddress
FROM 
  (Employees AS TE 
   INNER JOIN 
   MoversSchedule AS TM 
   ON TE.EmployeeID = TM.EmployeeID) 
      INNER JOIN 
      [Customer Data] AS TC 
      ON TM.MoveID = TC.ID
WHERE 
  ( (TC.[Move Date]) BETWEEN ([Enter Date 1]) AND ([Enter Date 2]) );

Then this is the VBA to invoke the query:

   Set Db = CurrentDb
   Set qDef = Db.QueryDefs("MoversScheduleQuery3") 
   qDef.Parameters("Enter Date 1") = ([Forms]![EmailEmployees]![fromdate]) ' #01/01/2011#
   qDef.Parameters("Enter Date 2") = ([Forms]![EmailEmployees]![todate]) ' #01/01/2014#
   Set rsEmail = qDef.OpenRecordset(dbOpenSnapshot, dbReadOnly)

I also noticed that I'm getting strange intermittent messages looking for Excel.EXE. I tried to turn off all macros and VB that might have invoked it, but did not find the routine that's causing it.

WEIRD all the way around! Is it just me, or is this version of Access, 2013, quirky as heck? I'll have something work one minute, and not the next.

[Dal Jeanis]

Oh, this db was created on 2013? Access isn't backwards-compatible, and I'm on 2010, so that may explain all the weird error popups I'm getting. Of course, Access is always quirky as heck. You just have to grok the quirks and either avoid or exploit them, as appropriate.

I created a new 2010 accdb file and imported the tables, queries and forms, and still got the intermittent errors, so the errors are intrinsic to one of the application objects, not the system objects. I noticed you have various macros. I avoid them, because it's nearly impossible (for me) to find out which macro code is causing what behavior to happen. The code hides behind too many curtains.

FYI, I never buy a MS product in the year of first release. Operating system, I always wait for service pack 2, which is usually the first stable and competent release. Application, I generally wait two years (and service pack 2 for the related OS). In other words, I usually buy MS stuff during the marketing push for the next version.

By the way, Gina, when quoting a prior poster, please delete all but the most relevant and necessary quotes. No problem this time, but that practice provides a much cleaner conversation, and helps the reader to know at a glance what part of the prior post is relevant to yours.

[rpeare]

Try this:

Set Db = CurrentDb

sSQL = "SELECT [Customer Data].[Full Name], [Customer Data].[Move Date], Employees.FullName, [Customer Data].[Start Time], MoversSchedule.AddtoStartTime, Employees.EmailAddress "
sSQL = sSQL & "FROM (Employees INNER JOIN MoversSchedule ON Employees.EmployeeID = MoversSchedule.EmployeeID) INNER JOIN [Customer Data] ON MoversSchedule.MoveID = [Customer Data].ID "
sSQL = sSQL & "WHERE ((([Customer Data].[Move Date]) Between #" & fromdate & "# And #" & [todate] & "#)); "

Set rsEmail = Db.OpenRecordset(sSQL)

Basically building your query in the code itself rather than referencing an external query.

I'd also recommend that in your loop you have something like

Do While Not rsEmail.EOF
    strEmail = rsEmail.Fields("emailaddress").Value
    If Not IsNull(strEmail) Then
        'send your email in here
    End If
    rsEmail.MoveNext
Loop

I should mention, I'm just copying the SQL code from your query MoversScheduleQuery and substituting the date values on your form into it.

[rpeare]

One addendum, I don't know if this was working before you arrived or not, but I had read that using sharepoint sort of exluded you from being able to use VBA code, so if you are using sharepoint it might prevent this from working (if the code wasn't there before you arrived)

[Gina Maylone]

Again, thank you all for your time and effort. I got this code to work well, however, "greeting" ends up in the BCC field in Outlook, "Subject" ends up in subject correctly, and "message" ends up in the message body correctly. How do I get greeting down into the message body? I have tried various commas in different locations which only causes errors. I'm sorry - I don't have the "code" icon available right now, so I'm posting without it.
[code]
Dim qDef As DAO.QueryDef
Dim rsEmail As DAO.Recordset

Dim strEmail As String
Dim strSubject As String
Dim strContactName As String

Set Db = CurrentDb
Set qDef = Db.QueryDefs("EmployeeEmailQuery")

Set rsEmail = qDef.OpenRecordset

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("emailaddress").Value

'strContactName = rsEmail.Fields("fullName").Value

DoCmd.SendObject acSendReport, "schedule", formatpdf, strEmail, , "" & Me!Subject, "" & Me!Greeting, "" & Me!Message
'DDoCmd.SendObject acSendReport, "schedule", formatpdf, strEmail, , , """" & Me!Subject & """", """" & Me!Greeting & """", """" & Me!Message & """"oCmd.SendObject acSendReport, "schedule", formatpdf, strEmail, , , """" & Me!Subject & """", """" & Me!Greeting & """", """" & Me!Message & """"
' False

rsEmail.MoveNext

Loop

Set rsEmail = Nothing
Set qDef = Nothing
Set Db = Nothing

MsgBox "Emails have been sent"




End Sub
[code]

[Dal Jeanis]

Gina - I pointed out that parameter problem in post 5. Review that post and put the missing comma wherever I told you to insert it.

[Gina Maylone]

I didI didI did

[Gina Maylone]

Whoops, sorry, I did insert it where you suggested and I get an error message, trying to duplicate it now so I can let you know exactly what it says.

[Gina Maylone]

I get a syntax error. An expression you entered is the wrong data type for one of the arguments.

[Dal Jeanis]

DoCmd.SendObject acSendReport, "schedule", formatpdf, strEmail, , , "" & Me!Subject, "" & Me!Greeting, "" & Me!Message

template:
expression.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

http://msdn.microsoft.com/en-us/libr.../ff197046.aspx

[Gina Maylone]

I get an expression you entered is the wrong data type for one of the arguments?