Run-time Error 2427 When No Records Exist

C

Curtis

I am using Microsoft Access 2003. I have a report {rptPreliminary Reports
Still Opened} that when there are records, the report displays as normal.
However, my issue is when there are no records, I get the msg: “Run-time
error 2427. You entered an expression that has no value.†As I run the
debug, the following is displayed. If there are no records, I want to bypass
this error with a message “There are no Preliminary Reports that are
outstanding. Have a Pleasant Day!†and be returned back to the
{frmSwitchboard}. I was wondering if someone could provide a suggestion in
the coding. I have attached the SQL Select Query {qryPreliminary Reports
Still Opened} below that is the source for my report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (Date - [DateOfExam] > [PRptDays]) Then
[PRptDays].ForeColor = vbRed
Else
[PRptDays].ForeColor = vbBlack
End If

End Sub

SELECT [tblMFS Master Data List].Name, IIf([PPE]=True,"PrePlacement
Exams",IIf([PPEOA]=True,"Overseas
Exams",IIf([DeploymentExam]=True,"Deployment
Exams",IIf([PSExam]=True,"Periodic Surveillance Exams","0")))) AS TypeOfExam,
IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",IIf([PSWSPJobTitle]<>"NA",[PSWSPJobTitle],IIf([PPEJobTitle]<>"NA",[PPEJobTitle],IIf([OEJobTitle]<>"NA",[OEJobTitle],IIf([DeployJobTitle]<>"NA",[DeployJobTitle],"Others"))))))
AS TypeOfJobTitle, [tblMFS Master Data List].DateOfExam, [tblMFS Master Data
List].PRpt, [tblMFS Master Data List].PRptDays, [tblMFS Master Data
List].PRCMT1, IIf(IsNull([PRMedicalStaff])=0,[PRMedicalStaff],"Left Blank")
AS PRMedicalStaff1, [tblMFS Master Data List].PRDate, [tblMFS Master Data
List].FRpt, [tblMFS Master Data List].FRComment1, [tblMFS Master Data
List].FRMedicalStaff, [tblMFS Master Data List].FRDate
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PRpt)=Yes))
ORDER BY [tblMFS Master Data List].Name;
 
J

johnvon

Curtis,

Use the OnNoData event of the report to pop up the message and cancel the
report.

Private Sub Report_NoData(Cancel As Integer)
Beep
MsgBox ""There are no Preliminary Reports that are outstanding. Have a
Pleasant Day!"
Cancel = True
End Sub

John

Curtis said:
I am using Microsoft Access 2003. I have a report {rptPreliminary Reports
Still Opened} that when there are records, the report displays as normal.
However, my issue is when there are no records, I get the msg: "Run-time
error 2427. You entered an expression that has no value." As I run the
debug, the following is displayed. If there are no records, I want to bypass
this error with a message "There are no Preliminary Reports that are
outstanding. Have a Pleasant Day!" and be returned back to the
{frmSwitchboard}. I was wondering if someone could provide a suggestion in
the coding. I have attached the SQL Select Query {qryPreliminary Reports
Still Opened} below that is the source for my report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (Date - [DateOfExam] > [PRptDays]) Then
[PRptDays].ForeColor = vbRed
Else
[PRptDays].ForeColor = vbBlack
End If

End Sub

SELECT [tblMFS Master Data List].Name, IIf([PPE]=True,"PrePlacement
Exams",IIf([PPEOA]=True,"Overseas
Exams",IIf([DeploymentExam]=True,"Deployment
Exams",IIf([PSExam]=True,"Periodic Surveillance Exams","0")))) AS TypeOfExam,
IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",IIf([PSWSPJobTitl
e]<>"NA",[PSWSPJobTitle],IIf([PPEJobTitle]<>"NA",[PPEJobTitle],IIf([OEJobTit
le] said:
AS TypeOfJobTitle, [tblMFS Master Data List].DateOfExam, [tblMFS Master Data
List].PRpt, [tblMFS Master Data List].PRptDays, [tblMFS Master Data
List].PRCMT1, IIf(IsNull([PRMedicalStaff])=0,[PRMedicalStaff],"Left Blank")
AS PRMedicalStaff1, [tblMFS Master Data List].PRDate, [tblMFS Master Data
List].FRpt, [tblMFS Master Data List].FRComment1, [tblMFS Master Data
List].FRMedicalStaff, [tblMFS Master Data List].FRDate
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PRpt)=Yes))
ORDER BY [tblMFS Master Data List].Name;
 
C

Curtis

Thanks for responding, Johnvon. I copied your suggestion into the "OnNoData"
event and I still get the Run-time Error 2427 message. At another website
someone responded and suggested the datediff function using the following
code:

If DateDiff("d", [DateOfExam]()) > [PRptDays] Then

When I put this datediff function in the "OnNoData" event, the Run-time
error 2427 does not show, however it directs me to the VB Coding,
highlighting "DateDiff" with a Compile Error: Argument not optional. After
pressing the OK, it directs me to the "Private Sub Detail_Format (Cancel As
Integer, FormatCount As Integer) which is highlighted in yellow. He further
suggested to look at the "OnNoData" event property, of course it was
originally blank. I have used both suggestions without a good outcome yet.
Thanks....

johnvon said:
Curtis,

Use the OnNoData event of the report to pop up the message and cancel the
report.

Private Sub Report_NoData(Cancel As Integer)
Beep
MsgBox ""There are no Preliminary Reports that are outstanding. Have a
Pleasant Day!"
Cancel = True
End Sub

John

Curtis said:
I am using Microsoft Access 2003. I have a report {rptPreliminary Reports
Still Opened} that when there are records, the report displays as normal.
However, my issue is when there are no records, I get the msg: "Run-time
error 2427. You entered an expression that has no value." As I run the
debug, the following is displayed. If there are no records, I want to bypass
this error with a message "There are no Preliminary Reports that are
outstanding. Have a Pleasant Day!" and be returned back to the
{frmSwitchboard}. I was wondering if someone could provide a suggestion in
the coding. I have attached the SQL Select Query {qryPreliminary Reports
Still Opened} below that is the source for my report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (Date - [DateOfExam] > [PRptDays]) Then
[PRptDays].ForeColor = vbRed
Else
[PRptDays].ForeColor = vbBlack
End If

End Sub

SELECT [tblMFS Master Data List].Name, IIf([PPE]=True,"PrePlacement
Exams",IIf([PPEOA]=True,"Overseas
Exams",IIf([DeploymentExam]=True,"Deployment
Exams",IIf([PSExam]=True,"Periodic Surveillance Exams","0")))) AS TypeOfExam,
IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",IIf([PSWSPJobTitl
e]<>"NA",[PSWSPJobTitle],IIf([PPEJobTitle]<>"NA",[PPEJobTitle],IIf([OEJobTit
le] said:
AS TypeOfJobTitle, [tblMFS Master Data List].DateOfExam, [tblMFS Master Data
List].PRpt, [tblMFS Master Data List].PRptDays, [tblMFS Master Data
List].PRCMT1, IIf(IsNull([PRMedicalStaff])=0,[PRMedicalStaff],"Left Blank")
AS PRMedicalStaff1, [tblMFS Master Data List].PRDate, [tblMFS Master Data
List].FRpt, [tblMFS Master Data List].FRComment1, [tblMFS Master Data
List].FRMedicalStaff, [tblMFS Master Data List].FRDate
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PRpt)=Yes))
ORDER BY [tblMFS Master Data List].Name;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top