Opening an ADODB.Recordset with an SQL statement,taking into account a date on a form (1 Viewer)

[DonnaCulff]

Good afternoon everyone ,

I hope you can help?
I am trying to generate emails based on a date on a form.

I have created a VBA module, connected to a recordset, generated emails to be sent depending on that recordset with a table successfully.

I want to change this so it only sends for a date range / date entered by the user now though and not loop through the entire table...hope that makes sence?

This is what I have come up with far...

----------MyCode-----------------------------------
Public Sub ControlOutlook()

Dim objOutlook As New Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strLtrContent As String
Dim strEMailMsg As String
Dim rsContacts As New ADODB.Recordset

Dim ctlEmailBox As String

ctlEmailBox = Nz(tLookup("SettingValue", "tblGlobalSettings", "SettingName = 'SpecialLetter'"), "Special Letter text not entered")
rsContacts.ActiveConnection = CurrentProject.Connection


strSQL = "SELECT qryCompletedWorkRequests.*, qryCompletedWorkRequests.Date" & _
" FROM qryCompletedWorkRequests" & _
" WHERE (((qryCompletedWorkRequests.Date)=Forms!frmCompletedWorkRequestsFROMteamsite!Modified));"

rsContacts.Open "strSQL"

'previous tries...please ignore
'rsContacts.Open "qryCompletedWorkRequests"
'rsContacts.Open "SELECT * FROM tblWorkRequests WHERE Forms![frmCompletedWorkRequestsFROMteamsite]![Modified] = Date.Value;"
'Set cmd.ActiveConnection = _
' CurrentProject.Connection
'cmd.CommandText = "qryCompletedWorkRequests"
'Set rsContacts = cmd.Execute(, _
'Array(Forms!frmCompletedWorkRequestsFROMteamsite!Date.Value), _
adCmdStoredProc)

Do While Not rsContacts.EOF
strLtrContent = "Dear " & rsContacts("Requestor") & "," & Chr(13) & Chr(13) & ctlEmailBox & Chr(13)
strLtrContent = strLtrContent & "You will be rating how " & rsContacts("AnalystName")
strLtrContent = strLtrContent & " performed for the work request you submitted on " & rsContacts("DateOfRequest") & "." & Chr(13)
strLtrContent = strLtrContent & "Named: '" & rsContacts("WorkRequestTitle") & "'" & Chr(13)
strLtrContent = strLtrContent & "Please click on the link below and enter your work request ID (" & rsContacts("WorkRequestID") & ") to begin. Complete all answers and press the 'Finish' button to complete." & Chr(13)
strLtrContent = strLtrContent & "Thank you for your time." & Chr(13)
strLtrContent = strLtrContent & "https://teams.cokecce.com/sites/Sys...ustomer%20Satisfaction%20Survey/overview.aspx" & Chr(13) & Chr(13)
strLtrContent = strLtrContent & "The content of this email is the confidential property of Coca-Cola Enterprises and should not be copied, modified, retransmitted, or used for any purpose except with written authorization. If you are not the intended recipient, please delete all copies and notify us immediately. " & Chr(13) & Chr(13) & _
"Coca-Cola Enterprises Limited - Registered in England: Company Number 27173 - Registered Office: Charter Place, Vine Street, Uxbridge UB8 1EZ" & Chr(13)
Set objEmail = objOutlook.CreateItem(olMailItem)
objEmail.Recipients.Add rsContacts("Requestor")
objEmail.Subject = "Customer Satisfaction Survey BSC"
objEmail.Body = strLtrContent
objEmail.Importance = olImportanceHigh
objEmail.BodyFormat = olFormatHTML
objEmail.Display
'objEmail.Send
rsContacts.MoveNext
Loop

End Sub

 

[namliam]

When posting code please use the code tags (press the # button on top of your post)

strSQL = "SELECT qryCompletedWorkRequests.* " & _
" FROM qryCompletedWorkRequests" & _
" WHERE (((qryCompletedWorkRequests.Date)=Forms!frmComplet edWorkRequestsFROMteamsite!Modified));"

Is almost right, except you need to
1) use ## around the date to indicate the date
2) Make sure the Modified field on the form is in US (MM/DD/YYYY) or ISO (YYYY/MM/DD) format
3) Keep your sql clear and avoid extending ( & _ ) lines
4) Substitute in your variable like so

strSQL = ""
strSQL = strSQL & " SELECT qryCompletedWorkRequests.* " 
strSQL = strSQL & " FROM qryCompletedWorkRequests "
strSQL = strSQL & " WHERE qryCompletedWorkRequests.Date=#" & Forms!frmCompletedWorkRequestsFROMteamsite!Modified & "#"

Also
5) It is not advisable to use a reserved word (like Date) as a column name.

 

[DonnaCulff]

Thank you namliam.

Your code makes much more sense and it a lot easier to read. I will take into account the #'s and Substitution an I should have known better about the reserved word. I will get that changed!

I have implemented your code and it is saying that it cannot find the form i am refering to. I put the code in a module and it's public and not private?, I am affraid I am a little new to VBA, could you help me understand what I need to do to fix the problem?

 

[DonnaCulff]

I kept the form open so it was accessible by the code and now I get the following message > "Invalid SQL statement expected; ‘DELETE’,’INSERT’,'UPDATE' or SELECT".

 

[namliam]

Yes you need the form open...

'Set rsContacts = cmd.Execute
I am not a ADO user, I much much much prefer DAO as it is the Native access type.
However... Execute sounds like it is an action query, hence the message? Sorry cant realy help you on the ADO part....

DAO would look something like:

....
Dim rs as DAO.Recordset
strSQL = ""
strSQL = ....
set rs = Currentdb.openrecordset(mySQL)
....

 

[DonnaCulff]

I gave DAO a go also. I also prefer DAO but I just couldn't get it to work and ADO did (when just a table was involved) Pot luck!

Error message when using DAO is: "The Microsoft Jet database engine cannot find the input table or query ". Make sure it exists and the name is spelt correctly"

The SQL refers to a query (so its a query within a query)
I opened that query first so that it knew it was there but it gets struck after the query results appear and doe not open up an email.

Here is my code... I simplfied it down for testing purposes:

#Public Sub ControlOutlook2()

Dim rst As DAO.Recordset

strSQL = ""
strSQL = strSQL & " SELECT qryCompletedWorkRequests.* "
strSQL = strSQL & " FROM qryCompletedWorkRequests "
strSQL = strSQL & " WHERE qryCompletedWorkRequests.ModDate=#" & Forms!frmCompletedWorkRequestsFROMteamsite!Modified & "#"
DoCmd.OpenQuery "qryCompletedWorkRequests"
Set rst = CurrentDb.OpenRecordset(mySQL)

With rst
Do While Not .EOF
Mailbody = Mailbody & ![Assigned_To] & " | " & ![Created] & " | " & ![Title] & vbCrLf
.MoveNext
Loop
End With

rst.Close
Set rst = Nothing

oMail.Body = Mailbody
End Sub#

 

[namliam]

LOL Nice attempt at working with the code wraps... You dont actually put a # there, but in the 'menu bar' above the post there is a # button you click....
It puts [ code ] and [ /code ] (without the spaces) around code and makes it look like my post.

What line is your error popping up on? I assume:
Set rst = CurrentDb.OpenRecordset(mySQL)

Which is quite logical (believe it or not) because
1) You probably have "option explicit" off
You should really turn this on, you will at the start hate me for it because most likely a lot of your code will fail at the start. Later on you will start to love it because it prevents silly errors like this one.
To turn it on:
- Go into any coding window (ALT + F11)
- Tools > Options, Tick the "Require variable Declaration"
- Click OK
- Verify that "Option exlicit" is just below the "Option Compare Database" in your code, if not add it.

Now compile your code and find .....
Your creating a query in strSQL then opening a query from the variable mySQL, which coincidentaly is my exact code, but obviously wrong !

 

[DonnaCulff]

Thanks! I didn't even realise it was not turned on

You dont need the the Option Explicit at the top of a module window though do you?
This is where my code is. I call it from the code behind the main form "frmCompletedWorkRequestsFROMteamsite"

If i run the code in my module then i get runtime error 2186 because it is looking for something on an open form and it highlights the correct bit. When I run it out of design view. It comes up with saying that the SQL is invalid. But no debug option?

Are the spaces required in the SQL statement. I tried putting them in taking them out and still no change. . . will post code in a second as i cant see a # button but that may be because i am using quick reply?

 

[DonnaCulff]

Public Sub ControlOutlook2()

    
Dim rst As DAO.Recordset
    
strSQL = ""
strSQL = strSQL & " SELECT qryCompletedWorkRequests.* "
strSQL = strSQL & " FROM qryCompletedWorkRequests "
strSQL = strSQL & " WHERE qryCompletedWorkRequests.ModDate=#" & Forms!frmCompletedWorkRequestsFROMteamsite!Modified & "#"
'DoCmd.OpenQuery "qryCompletedWorkRequests"
Set rst = CurrentDb.OpenRecordset(mySQL)
  
With rst
  Do While Not .EOF
    Mailbody = Mailbody & ![Assigned_To] & " | " & ![Created] & " | " & ![Title] & vbCrLf
      .MoveNext
  Loop
End With
  
rst.Close
Set rst = Nothing
  
oMail.Body = Mailbody
End Sub

 

[namliam]

[B]strSQL [/B]= ""
[B]strSQL [/B]= strSQL & " SELECT qryCompletedWorkRequests.* "
[B]strSQL [/B]= strSQL & " FROM qryCompletedWorkRequests "
[B]strSQL [/B]= strSQL & " WHERE qryCompletedWorkRequests.ModDate=#" & Forms!frmCompletedWorkRequestsFROMteamsite!Modified & "#"
'DoCmd.OpenQuery "qryCompletedWorkRequests"
Set rst = CurrentDb.OpenRecordset([B][U][SIZE="5"]mySQL[/SIZE][/U][/B])

And yes you need Option explicit on top of every module for it to work, you can turn off the Tick mark and type it your self every time.... Or tick it and have access do it for you, but you need it for it to work 'properly'

 

[DonnaCulff]

I have been changing and looking at code so long I didn't even see that. I will change and work through the errors. Thanks for your help

 

[DonnaCulff]

I can not get round the too few parameters. expected 1. It can't be getting the date from the form...? The field on the form frmCompletedWorkRequestsFROMteamsite is called Modified (the text control) with a control source of ModDate.

I am trying to say where the qryCompletedWorkRequests.ModDate = the ModDate shown on the form then run code...

strSQL = strSQL & " WHERE qryCompletedWorkRequests.ModDate=#" & Forms!frmCompletedWorkRequestsFROMteamsite!Modified & "#"

 

[namliam]

Try this:

strSQL = ""
strSQL = strSQL & " SELECT qryCompletedWorkRequests.* "
strSQL = strSQL & " FROM qryCompletedWorkRequests "
strSQL = strSQL & " WHERE qryCompletedWorkRequests.ModDate=#" & Forms!frmCompletedWorkRequestsFROMteamsite!Modified & "#"
Debug.print strSQL

Then bring up your immediate window (CTRL + G)

This should show your SQL, copy and paste that SQL into a query design window, run the query there and it should show you what parameter it is looking for... Likely some typo someplace ...

 

[DonnaCulff]

I haven't finished the start of the code. I got confused with switching from ADO to DAO and back so now trying to transflate one in the other but every example i have does it in a slighly different way?

Do you have any sample code?

 

[DonnaCulff]

This is what i have so far:

Public Sub ControlOutlook2()
    Dim objOutlook As New Outlook.Application
    Dim objEmail As Outlook.MailItem
    Dim strLtrContent As String
    Dim strEMailMsg As String
    Dim rsContacts As DAO.Recordset
    Dim strSql As String
    Dim ctlEmailBox As String
    
    ctlEmailBox = Nz(tLookup("SettingValue", "tblGlobalSettings", "SettingName = 'SpecialLetter'"), "Special Letter text not entered")
    'rsContacts.ActiveConnection = CurrentProject.Connection 'states data member not found so took out what is the dao equivelant?
    
    strSql = ""
    strSql = strSql & " SELECT qryCompletedWorkRequests.* "
    strSql = strSql & " FROM qryCompletedWorkRequests "
    strSql = strSql & " WHERE qryCompletedWorkRequests.ModDate=#" & Forms!frmCompletedWorkRequestsFROMteamsite!Modified & "#"
    Debug.Print strSql
    
    DoCmd.OpenQuery "qryCompletedWorkRequests"
    Set rsContacts = CurrentDb.OpenRecordset(strSql) ' states too few parameters expected 1, looking at data the date from the form i typed in was correct?
      
    With rsContacts
      Do While Not .EOF
        strLtrContent = "Dear " & rsContacts("Requestor") & "," & Chr(13) & Chr(13) & ctlEmailBox & Chr(13)
        strLtrContent = strLtrContent & "You will be rating how " & rsContacts("AnalystName")
        strLtrContent = strLtrContent & " performed for the work request you submitted on " & rsContacts("DateOfRequest") & "." & Chr(13)
        strLtrContent = strLtrContent & "Named: '" & rsContacts("WorkRequestTitle") & "'" & Chr(13)
        strLtrContent = strLtrContent & "Please click on the link below and enter your work request ID (" & rsContacts("WorkRequestID") & ") to begin. Complete all answers and press the 'Finish' button to complete." & Chr(13)
        strLtrContent = strLtrContent & "Thank you for your time." & Chr(13)
        strLtrContent = strLtrContent & "[URL]https://teams.cokecce.com/sites/SystemsInformationBSC/Lists/Customer%20Satisfaction%20Survey/NewForm.aspx?Source=https%3A%2F%2Fteams%2Ecokecce%2Ecom%2Fsites%2FSystemsInformationBSC%2FLists%2FCustomer%2520Satisfaction%2520Survey%2Foverview%2Easpx[/URL]" & Chr(13) & Chr(13)
        strLtrContent = strLtrContent & "The content of this email is the confidential property of Coca-Cola Enterprises and should not be copied, modified, retransmitted, or used for any purpose except with written authorization.  If you are not the intended recipient, please delete all copies and notify us immediately. " & Chr(13) & Chr(13) & _
        "Coca-Cola Enterprises Limited - Registered in England: Company Number 27173 - Registered Office: Charter Place,  Vine Street, Uxbridge UB8 1EZ" & Chr(13)
        Set objEmail = objOutlook.CreateItem(olMailItem)
        objEmail.Recipients.Add rsContacts("Requestor")
        objEmail.Subject = "Customer Satisfaction Survey BSC"
        objEmail.Body = strLtrContent
        objEmail.Importance = olImportanceHigh
        objEmail.BodyFormat = olFormatHTML
        objEmail.Display
        'objEmail.Send
        rsContacts.MoveNext
      Loop
    End With
      
    rsContacts.Close
    Set rsContacts = Nothing
       
    End Sub

 

[DonnaCulff]

Here is some background. . .

Main functionality - Generating an email from query results. Updating query results and sending the new emails out excluding the ones already sent


I have

• 2 Tables Work_Requests, tblWorkRequests,
• 1 Queries qryCompletedWorkRequests
• 1 Forms frmCompletedWorkRequestsFROMteamsite
• 2/3 Bits of Code Private Sub cmdExitandUpdate_Click()

Public Sub ControlOutlook2()
Public Sub ControlOutlook()

Work_Requests – Is a linked table to a SharePointSite where ‘work requests’ are submitted
tblWorkRequests – Is where the completed workrequests that the user wants to send an email to are stored. It also tells the user when the email was sent and who sent it.
The query links the two.
cmdExitandUpdate_Click() is the transfer code that loops round the form and updates the tblWorkRequeststable
ControlOutlook() is my atampt at ADODB recordsets
ControlOutlook2() is my attempt at DAO recordsets


I use the query to collate all the ones with a Status of “Complete” and the user enters the criteria for the date e.g. [Please enter a date DD/MM/YYYY] to bring back the days results to a form frmCompletedWorkRequestsFROMteamsite

A button on the form cmdExitandUpdate opens up a recordset for the 2nd table (Work_Requests is not editable) to store the users choice and details on sent work requests.

The code currently loops round the form results (the query as it’s the control source) and transfers it to tblWorkRequests (There is a primary key which prevents a whole day being sent again. The duplicate message appears)…

Changes required

• Query to ask for a beginning and end date as they plan to send out an email per work request weekly
• To take into account those already sent and not send again

The code in the exit and updates calls a piece of code in a module after it has transferred the data, to generate the emails. A date / time stamp, user and sent marker are also added once the loop is done.

 

[namliam]

what does " Debug.Print strSql" return in the immediate window?

and what happens when your run that SQL in a 'real' query?

 

[DonnaCulff]

It shows the SQL with the date I entered. So it seems to work.
When I copy the SQL into the Query design grid in SQL view and then change it to design view and then I run it, it also works... very confusing!

The query i refer to has a parameter of complete already specified and then I want the date to be picked up from the form or the box they enter it into (I dont kno how to do this hence why i linked it to the value on the form)

Maybe something is wrong with the design. I am looking through DAO examples where the email is generated from the form but not having much luck so far

 

[DonnaCulff]

OK, I found a DAO Example and put it in a seperate module and ran that based on my query.

I got an error saying "Too few parameters. Expecting 2" rather than one now. Instread of writing the SQL I reference the query itself

Set MailList = db.OpenRecordset("qryCompletedWorkRequests")

This is the SQL straight from the Query Builder

SELECT Work_Requests.ID, Work_Requests.Assigned_To, Work_Requests.Created, Work_Requests.Title, Work_Requests.Description, Left([Modified],10) AS ModDate, Work_Requests.Edit, Work_Requests.Priority, Work_Requests.Start_Date, Work_Requests.Due_Date, Work_Requests.Status, Work_Requests.Created_By, Work_Requests.Modified_By, Work_Requests.Work_Request_Created_by, Work_Requests.Area, Work_Requests.Category
FROM Work_Requests
WHERE (((Left([Modified],10)) Between [Please enter a start date DD/MM/YYYY] And [Please enter an end date DD/MM/YYYY]) AND ((Work_Requests.Status)="Completed"));

This is a little different as I am not asking for two dates but i have tested the query by itself and it works perfectly.

I thought if you have square brackets that would prompt a pop up box once the code is run to get the data it needs? This is the whole point? I do not want to hard code values but allow the user to specifiy what data they send.

 

[namliam]

You cannot have popup boxes on queries you run automaticaly, also you cannot have any reference to forms or what ever inside them... which is probably what is giving you a headache.

It will work manually, but fail automated. From the access help a sample on how to use parameters in automated queries:

Sub ParameterX()

    Dim dbsNorthwind As Database
    Dim qdfReport As QueryDef
    Dim prmBegin As Parameter
    Dim prmEnd As Parameter

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create temporary QueryDef object with two 
    ' parameters.
    Set qdfReport = dbsNorthwind.CreateQueryDef("", _
        "PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
        "SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
        "FROM Orders WHERE ShippedDate BETWEEN " & _
        "[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
        "ORDER BY EmployeeID")
    Set prmBegin = qdfReport.Parameters!dteBegin
    Set prmEnd = qdfReport.Parameters!dteEnd

    ' Print report using specified parameter values.
    ParametersChange qdfReport, prmBegin, #1/1/95#, _
        prmEnd, #6/30/95#
    ParametersChange qdfReport, prmBegin, #7/1/95#, _
        prmEnd, #12/31/95#

    dbsNorthwind.Close

End Sub