I'm moving the program off of a sharepoint server and onto a local server. Sharepoint does have some quirks.
I'm moving the program off of a sharepoint server and onto a local server. Sharepoint does have some quirks.
No problem. This is a tuffy one. I can't figure out where the problem lies.
Just using NZ on the two parameters in the query fixes the issue in RuralGuy's version of the database:
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.Code:EmployeeEmailQuery: SELECT MoversScheduleQuery.EmailAddress, MoversScheduleQuery.FullName FROM MoversScheduleQuery WHERE (((MoversScheduleQuery.[Move Date]) Between NZ([Forms]![EmailEmployees]![fromdate]) And NZ([Forms]![EmailEmployees]![todate])));
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.Then this is the VBA to invoke the query:Code: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]) );
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.Code: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)
Interesting Dal. Nz() huh? Great catch!
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.Just using NZ on the two parameters in the query fixes the issue in RuralGuy's version of the database:
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.Code:EmployeeEmailQuery: SELECT MoversScheduleQuery.EmailAddress, MoversScheduleQuery.FullName FROM MoversScheduleQuery WHERE (((MoversScheduleQuery.[Move Date]) Between NZ([Forms]![EmailEmployees]![fromdate]) And NZ([Forms]![EmailEmployees]![todate])));
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.Then this is the VBA to invoke the query:Code: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]) );
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.Code: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)
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.
Try this:
Basically building your query in the code itself rather than referencing an external query.Code: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)
I'd also recommend that in your loop you have something like
I should mention, I'm just copying the SQL code from your query MoversScheduleQuery and substituting the date values on your form into it.Code: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
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)
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]
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.
I didI didI did
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.
I get a syntax error. An expression you entered is the wrong data type for one of the arguments.
template:Code:DoCmd.SendObject acSendReport, "schedule", formatpdf, strEmail, , , "" & Me!Subject, "" & Me!Greeting, "" & Me!Message
expression.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
http://msdn.microsoft.com/en-us/libr.../ff197046.aspx
I get an expression you entered is the wrong data type for one of the arguments?