G
Guest
I keep getting this error "Invalid use of null"...can anyone please tell me
what is wrong with this code? Thanks!
Option Compare Database
Private Sub cmdSendReport_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAcountStatus As String
Dim Email As String
Dim cono As String
Dim fOk As Boolean
strSQL = "SELECT cono, Email From [rptlstusers]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
DoCmd.OpenReport "rptEmployeeData", acPreview
Reports![rptEmployeeData].FilterOn = True
Do While Not rst.EOF
Email = rst.Fields("Email")
cono = rst.Fields("cono")
Call FilterReport("rptEmployeeData", cono)
DoEvents
fOk = SendReportByEmail("rptEmployeeData", Email)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & Email
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Option Compare Database
Function SendReportByEmail(rptEmployeeData As String, Email As String) As
Boolean
Dim s As String
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = Email
strSubject = Reports(rptEmployeeData).Caption
strMessageBody = "Snapshot Attached"
DoCmd.SendObject acSendReport, rptEmployeeData, acFormatSNP,
strRecipient, , , strSubject, strMessageBody, False
SendReportByEmail = True
PROC_EXIT:
Exit Function
PROC_ERR:
SendReportByEmail = False
If Err.Number = 2501 Then
Call MsgBox("The email was not sent for " & strEmail & ".", vbOKOnly
+ vbExclamation + vbDefaultButton1, , , "User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function
Option Compare Database
Public Sub FilterReport(rptEmployeeData As String, cono As String)
On Error GoTo PROC_ERR
Dim strSQL As String
strSQL = "cono " & " = '" & cono & "'"
Reports(rptEmployeeData).Filter = strSQL
Reports(rptEmployeeData).FilterOn = True
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
what is wrong with this code? Thanks!
Option Compare Database
Private Sub cmdSendReport_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAcountStatus As String
Dim Email As String
Dim cono As String
Dim fOk As Boolean
strSQL = "SELECT cono, Email From [rptlstusers]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
DoCmd.OpenReport "rptEmployeeData", acPreview
Reports![rptEmployeeData].FilterOn = True
Do While Not rst.EOF
Email = rst.Fields("Email")
cono = rst.Fields("cono")
Call FilterReport("rptEmployeeData", cono)
DoEvents
fOk = SendReportByEmail("rptEmployeeData", Email)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & Email
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Option Compare Database
Function SendReportByEmail(rptEmployeeData As String, Email As String) As
Boolean
Dim s As String
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = Email
strSubject = Reports(rptEmployeeData).Caption
strMessageBody = "Snapshot Attached"
DoCmd.SendObject acSendReport, rptEmployeeData, acFormatSNP,
strRecipient, , , strSubject, strMessageBody, False
SendReportByEmail = True
PROC_EXIT:
Exit Function
PROC_ERR:
SendReportByEmail = False
If Err.Number = 2501 Then
Call MsgBox("The email was not sent for " & strEmail & ".", vbOKOnly
+ vbExclamation + vbDefaultButton1, , , "User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function
Option Compare Database
Public Sub FilterReport(rptEmployeeData As String, cono As String)
On Error GoTo PROC_ERR
Dim strSQL As String
strSQL = "cono " & " = '" & cono & "'"
Reports(rptEmployeeData).Filter = strSQL
Reports(rptEmployeeData).FilterOn = True
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub