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

[DonnaCulff]

This is great! I found a site which explained this just before your post and I am working on QueryDefs and trying to open up the email now.

Thanks again for your invaluable help

 

[DonnaCulff]

I feel like I have been constantly coding through the night!
The two pieces of code I have work to some extent.

The following code takes the work request puts it into a table and then calls a module to generate the email. It then time and date stamps it. Sent marker and user etc.

The bit i need to test more throughly is the DAO_Execute code in the module.

Private Sub cmdExitandUpdate_Click() 'button on frmCompletedWorkRequestsFROMteamsite form to update what the user has searched for, sends to a table and generates an email once module is called
 
Dim rst As Recordset
Dim rstForms As Recordset
Dim nIndex As Integer
 
On Error GoTo Err_Duplicate
 
Set rst = CurrentDb.OpenRecordset("tblWorkRequests")
Set rstForms = Forms("[frmCompletedWorkRequestsFROMteamsite]").RecordsetClone

rstForms.MoveFirst
Do While Not rstForms.EOF
    If rst.EOF Then
        rst.AddNew
        rst!WorkRequestID = rstForms!ID
        rst!AnalystName = rstForms!Assigned_To
        rst!DateOfRequest = rstForms!Start_Date
        rst!Requestor = rstForms!Work_Request_Created_by
        rst!WorkRequestTitle = rstForms!Title
        rst!WorkRequestDescription = rstForms!Description
        rst!RequestCompletionDate = rstForms!Due_Date
        rst!UserID = DADCurrentUser.UserID
        rst!SentDate = Now()
        rst!Sent = True
        rst.Update
        rstForms.MoveNext
    Else
        rst.Edit
        rst!WorkRequestID = rstForms!ID
        rst!AnalystName = rstForms!Assigned_To
        rst!DateOfRequest = rstForms!Start_Date
        rst!Requestor = rstForms!Work_Request_Created_by
        rst!WorkRequestTitle = rstForms!Title
        rst!WorkRequestDescription = rstForms!Description
        rst!RequestCompletionDate = rstForms!Due_Date
        rst!UserID = DADCurrentUser.UserID
        rst!SentDate = Now()
        rst!Sent = True
        rst.Update
    rstForms.MoveNext
    End If
Loop
 
Call DAO_Execute
 
rst.Close
rstForms.Close

Set rst = Nothing
Set rstForms = Nothing
 
DoCmd.Close acForm, Me.Name
 
Exit_Err_Duplicate:
    Exit Sub
 
Err_Duplicate:
    MsgBox Err.Description
    Resume Exit_Err_Duplicate

End Sub

This is the other piece of code

Public Sub DAO_Execute()
 
'Database Query and Recordset Decs
Dim db As Database
Dim qdef As QueryDef
Dim rsContacts As DAO.Recordset
Dim fld As String
 
'Declaire Outlook and message variables/recordsets
Dim objOutlook As New Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strLtrContent As String
Dim strEMailMsg As String
Dim ctlEmailBox As String
 
'Assigning values
ctlEmailBox = Nz(tLookup("SettingValue", "tblGlobalSettings", "SettingName = 'SpecialLetter'"), "Special Letter text not entered")
'rsContacts.ActiveConnection = CurrentProject.Connection *taken out as already done below
fld = "Assigned_To"
'Setting values
Set db = CurrentDb
Set qdef = db.QueryDefs("qryCompletedWorkRequests")
qdef.Parameters![Please enter a start date DD/MM/YYYY] = [Forms]!frmCompletedWorkRequestsFROMteamsite![Modified]
qdef.Parameters![Please enter an end date DD/MM/YYYY] = [Forms]!frmCompletedWorkRequestsFROMteamsite![Modified]
Set rsContacts = qdef.OpenRecordset(dbOpenDynaset)
 
Do While Not rsContacts.EOF
        
        strLtrContent = "Dear " & rsContacts("Created_By") & "," & Chr(13) & Chr(13) & ctlEmailBox & Chr(13)
        strLtrContent = strLtrContent & "You will be rating how " & rsContacts("Assigned_To")
        strLtrContent = strLtrContent & " performed for the work request you submitted on " & rsContacts("Created") & "." & Chr(13)
        strLtrContent = strLtrContent & "Named: '" & rsContacts("Title") & "'" & Chr(13)
        strLtrContent = strLtrContent & "Please click on the link below and enter your work request ID (" & rsContacts("ID") & ") 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("Created_By")
        objEmail.Subject = "Customer Satisfaction Survey BSC"
        objEmail.Body = strLtrContent
        objEmail.Importance = olImportanceHigh
        objEmail.BodyFormat = olFormatHTML
        objEmail.Display
        'objEmail.Send
        Debug.Print rsContacts!Assigned_To
        rsContacts.MoveNext
Loop
 
qdef.Close
rsContacts.Close
db.Close
 
End Sub

An email is generated for the date on the form, but as i do not have any other test data from the website that is not the 29th I do not know if any other date will work yet. Fingers crossed I have something that is workable for tomorrows deadline...

 

[DonnaCulff]

OK

I reconnected to copy of the linked table but where i could put different dates in...

I only produces one email. The loop cannot be working correctly. I do not get any error messages, any ideas?

 

[DonnaCulff]

qryCompleteWorkRequests has a field called Modified which is a date when the work request was complete. The user specifies a date range to email all the people who raised the work requested completed in say a week...

It works for the 1st record...but not the remaining. I have a feeling that it is something to do with my Parameter values as both are linked to modified which will just be one date and not two as the query pulls..


Field: ModDate: Left([Modified],10)
Criteria Between [Please enter a start date DD/MM/YYYY] And [Please enter an end date DD/MM/YYYY