I have this code for a button that previews a report.


Private Sub cmdPreviewWeek_Click()
On Error GoTo Err_cmdPreviewWeek_Click

Call Checks.CheckMonthYear

Dim stDocName As String

stDocName = "rptMonthlyWeek"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewWeek_Click:
Exit Sub

Err_cmdPreviewWeek_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewWeek_Click

End Sub


I also have a module (Checks)that checks a listbox and a text box on the form to make sure that a value has been choosen/entered.


Public Function CheckMonthYear()

On Err GoTo errCheck:

Dim ExitCheck As Boolean


If IsNull(Forms![frmMonthReport]![LstMonth]) Then
MsgBox "You must choose a month!", vbExclamation, "Choose Month!"
Forms![frmMonthReport]![LstMonth].SetFocus
Exit Function
End If

If IsNull(Forms![frmMonthReport]![txtYear]) Then
MsgBox "You must enter a Year!", vbExclamation, "Enter Year!"
Forms![frmMonthReport]![txtYear].SetFocus
Exit Function
End If

errCheck:
Exit Function

End Function


My question - Is there (from the module) to tell the OnClick event for the buttun to exit (Exit Sub) if the listbox or textbox don't have a value (i.e. IsNull(...) returns True)??

Cos...

Have you considered utilizing the On No Data event of the Report??

Yes, but I want the OnNoData even of the report to only give the user a messagebox one a user has choose a month and year and say "No data for Month/Year Choosen".

The reason for this code, is so that the query and report won't even run until the criteria (via the Listbox and TexeBox) is choosen. And to tell the user what they are doing wrong.

You could move the Public CheckMonthYear() function to the forms module
(make it private) if the function is only being called from the from.

Also, I would check if the text box or combo box is null at the begining of your
OnClick event and then exit the sub if they are Null.Private Sub cmdPreviewWeek_Click()
On Error GoTo Err_cmdPreviewWeek_Click

If IsNull(Textbox) Or TextBox = "" Or IsNull(ListBox) Or ListBox = "" Then
MsgBox "Invalid Data - TextBox or ListBox is empty!"
Exit Sub
End If

Call Checks.CheckMonthYear

Dim stDocName As String

stDocName = "rptMonthlyWeek"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewWeek_Click:
Exit Sub

Err_cmdPreviewWeek_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewWeek_Click

End SubHTH

ghudson,


If IsNull(Textbox) Or TextBox = "" Or IsNull(ListBox) Or ListBox = "" Then
MsgBox "Invalid Data - TextBox or ListBox is empty!"
Exit Sub
End If

Call Checks.CheckMonthYear


Doing this is redundant as Checks.CheckMonthYear is doing the same thing as the IF Statement.

I was trying to avoid have to type the IF statemane for each report button I have on the form (I have 4 buttons - Preview, Print, OutputTo and Email). Plus I have the same set for each report (I have 5 on the form). And YES, I know I could use a combobox or listbox to choose a report, but this is how the user wants it. Plus there are only a few reports so the form isn't too crowded.

ghudson,

I added this to the form module


Private Function CheckMonthYear()

If IsNull(Forms![frmMonthReport]![LstMonth])
And IsNull(Forms![frmMonthReport]![txtYear]) Then
MsgBox "You must choose a month and enter a Year!",
vbExclamation, "Choose Month and Enter Year!"
Forms![frmMonthReport]![LstMonth].SetFocus
ExitCheck = True
Exit Function
Else
ExitCheck = False
End If

If IsNull(Forms![frmMonthReport]![LstMonth]) Then
MsgBox "You must choose a month!", vbExclamation,
"Choose Month!"
Forms![frmMonthReport]![LstMonth].SetFocus
ExitCheck = True
Exit Function
Else
ExitCheck = False
End If

If IsNull(Forms![frmMonthReport]![txtYear]) Then
MsgBox "You must enter a Year!", vbExclamation, "Enter Year!"
Forms![frmMonthReport]![txtYear].SetFocus
ExitCheck = True
Exit Function
Else
ExitCheck = False
End If

End Function


and this to each button


Call CheckMonthYear
If ExitCheck = True Then
Exit Sub
End If


and at the beginning of the form module


Option Compare Database
Dim ExitCheck As Boolean


It works! Thanks,ghudson!

OK, it does work but now I have another problem.
:(

The report that I am generating consists of a Chart only, and it still generates a preview even there is no data (from query) to chart. Is there a way to tell if there is data to chart?
:confused:

I suggest that you use the On No Data event of the report.Private Sub Report_NoData(Cancel As Integer)

MsgBox "Your request has no data to print.", vbInformation, "No Data"
Cancel = True

End SubAlso, I suggest that you remove the first IFs in your function and
only test the fields individually to reduce some redundancy.Private Function CheckMonthYear()

If IsNull(LstMonth) Or LstMonth = "" Then
MsgBox "You must choose a month!", vbExclamation, "Choose Month!"
Me.LstMonth.SetFocus
ExitCheck = True
Exit Function
Else
ExitCheck = False
End If

If IsNull(txtYear) Or txtYear = "" Then
MsgBox "You must enter a Year!", vbExclamation, "Enter Year!"
Me.txtYear.SetFocus
ExitCheck = True
Exit Function
Else
ExitCheck = False
End If

End FunctionHTH

ghudson ,

I did use the On No Data event of the report and it didn't work. I guess it works differently for charts in a report. I think I will try a query that counts the number of records in the source query for the chart and if <0 then don't preview/print the report.