Query records by me!date then email results in body of email on button click

[TWIRCH75]

I am trying to query my records by the current records selected date then send the results in the body of an email on click. I believe I am close but I think there is a problem with the date format because I am getting 3421 Data type conversion error. Any help would be appreciated. Here is what I have:

Private Sub eMail_Click()
On Error GoTo EH
Dim dbExceptions As Database
Dim rstExceptions As Recordset
Dim dbDate As Database
Dim rstDate As Recordset
Dim intExceptionsRecords As Integer
Dim strSQL As String
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strBody As String
 
Set dbExceptions = CurrentDb()
 
strSQL = "SELECT * FROM Exceptions ORDER BY [Date];"
Set rstExceptions = dbExceptions.OpenRecordset(strSQL, dbOpenDynaset)
If Not rstExceptions.EOF Then
rstExceptions.MoveFirst
Do While Not rstExceptions.EOF
strTo = ""
strCC = ""
strSubject = "Exceptions" & "~" & "" & "~" & Me![Date]
strBody = Me![Date] & vbCrLf & _
Me![StartTime] & vbCrLf & _
Me![EndTime] & vbCrLf & _
Me![AuxCode] & vbCrLf & _
Me![Reason] & vbCrLf & _
Me![PreApproved] & vbCrLf & _
Me![Notes] & vbCrLf & vbCrLf
 
strSQL = "SELECT * FROM Exceptions WHERE [Date] = '" & rstExceptions(Me![Date]) & " '"
Set rstDate = dbExceptions.OpenRecordset(strSQL, dbOpenDynaset)
If Not rstDate.EOF Then
rstDate.MoveLast
intExceptionsRecords = rstDate.RecordCount
rstDate.MoveFirst
Else
intExceptionsRecords = 0
End If
strBody = strBody & ExceptionsRecords & _
" records on file with us" & vbCrLf & vbCrLf
Do While Not rstDate.EOF
strBody = strBody & rstDate("NotificationItem") & _
rstDate("Notification Date") & vbCrLf
Loop
rstDate.Close
strBody = strBody
DoCmd.SendObject , , , stEmail, , , stSubject, stMsgBody, True
Loop
dbDate.Close
rstExceptions.Close
dbExceptions.Close
End If
Exit Sub
EH:
MsgBox Err.Number & " " & Err.Description
Exit Sub
Exit Sub
 
 
End Sub

 

[pr2-eugin]

Hello TWIRCH75, Welcome to AWF

Which line is the error occurring? Can you highlight it?

PS: Please use Code Tags when posting VBA Code

 

[TWIRCH75]

Thank you for the reply and sorry about the CODE. I am pretty new to this but I think it is in this line. I think it's having trouble with the Me![Date] but i'm not sure since the debug doesnt specifically point it out:

Private Sub eMail_Click()
On Error GoTo EH
Dim dbExceptions As Database
Dim rstExceptions As Recordset
Dim dbDate As Database
Dim rstDate As Recordset
Dim intExceptionsRecords As Integer
Dim strSQL As String
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strBody As String
 
Set dbExceptions = CurrentDb()
 
strSQL = "SELECT * FROM Exceptions ORDER BY [Date];"
Set rstExceptions = dbExceptions.OpenRecordset(strSQL, dbOpenDynaset)
If Not rstExceptions.EOF Then
rstExceptions.MoveFirst
Do While Not rstExceptions.EOF
strTo = ""
strCC = ""
strSubject = "Exceptions" & "~" & "" & "~" & Me![Date]
strBody = Me![Date] & vbCrLf & _
Me![StartTime] & vbCrLf & _
Me![EndTime] & vbCrLf & _
Me![AuxCode] & vbCrLf & _
Me![Reason] & vbCrLf & _
Me![PreApproved] & vbCrLf & _
Me![Notes] & vbCrLf & vbCrLf
 
[COLOR=red][U]strSQL = "SELECT * FROM Exceptions WHERE [Date] = '" & rstExceptions(Me![Date])[/U] & " '"[/COLOR]
Set rstDate = dbExceptions.OpenRecordset(strSQL, dbOpenDynaset)
If Not rstDate.EOF Then
rstDate.MoveLast
intExceptionsRecords = rstDate.RecordCount
rstDate.MoveFirst
Else
intExceptionsRecords = 0
End If
strBody = strBody & ExceptionsRecords & _
" records on file with us" & vbCrLf & vbCrLf
Do While Not rstDate.EOF
strBody = strBody & rstDate("NotificationItem") & _
rstDate("Notification Date") & vbCrLf
Loop
rstDate.Close
strBody = strBody
DoCmd.SendObject , , , stEmail, , , stSubject, stMsgBody, True
Loop
dbDate.Close
rstExceptions.Close
dbExceptions.Close
End If
Exit Sub
EH:
MsgBox Err.Number & " " & Err.Description
Exit Sub
Exit Sub
 
 
End Sub

 

[pr2-eugin]

Since [Date] (a terrible terrible field name BTW, see the thread : MS Access Naming Conventions), is an actual date field in the table, you need to wrap them in # not '

BTW, this would make code reading so much easier !

Private Sub eMail_Click()
On Error GoTo EH
    Dim dbExceptions As Database, rstExceptions As Recordset
    Dim dbDate As Database, rstDate As Recordset
    Dim intExceptionsRecords As Integer
    Dim strSQL As String, strTo As String
    Dim strCC As String, strSubject As String, strBody As String
 
    Set dbExceptions = CurrentDb()
    
    strSQL = "SELECT * FROM Exceptions ORDER BY [Date];"
    Set rstExceptions = dbExceptions.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rstExceptions.EOF Then
        rstExceptions.MoveFirst
        Do While Not rstExceptions.EOF
            strTo = ""
            strCC = ""
            strSubject = "Exceptions" & "~" & "" & "~" & Me![Date]
            strBody = Me![Date] & vbCrLf & _
                        Me![StartTime] & vbCrLf & _
                        Me![EndTime] & vbCrLf & _
                        Me![AuxCode] & vbCrLf & _
                        Me![Reason] & vbCrLf & _
                        Me![PreApproved] & vbCrLf & _
                        Me![Notes] & vbCrLf & vbCrLf
     
            [COLOR=Red][B]strSQL = "SELECT * FROM Exceptions WHERE [Date] = " & Format(Me![Date], "\#mm\/dd\/yyyy\#")[/B][/COLOR]
            Set rstDate = dbExceptions.OpenRecordset(strSQL, dbOpenDynaset)
            If Not rstDate.EOF Then
                rstDate.MoveLast
                intExceptionsRecords = rstDate.RecordCount
                rstDate.MoveFirst
            Else
                intExceptionsRecords = 0
            End If
            
            strBody = strBody & ExceptionsRecords & _
                        " records on file with us" & vbCrLf & vbCrLf
            Do While Not rstDate.EOF
                strBody = strBody & rstDate("NotificationItem") & _
                            rstDate("Notification Date") & vbCrLf
            Loop
            rstDate.Close
            strBody = strBody
            DoCmd.SendObject , , , stEmail, , , stSubject, stMsgBody, True
        Loop
        dbDate.Close
        rstExceptions.Close
        dbExceptions.Close
    End If
[COLOR=Red][B]exSub:[/B][/COLOR]
    Exit Sub
[COLOR=Red][B]EH:[/B][/COLOR]
    MsgBox Err.Number & " " & Err.Description
    [COLOR=Red][B]Resume exSub[/B][/COLOR]
End Sub