Error if Null field

[renenger]

I would like a MsgBox to pop up for the user if the Priority field is blank and the box count is not. I tried doing this on the report but the report will just not run. So I considered adding a module to the query.

However, I get a Data type mismatch. Below is the module and query. Can someone help me with this?

Function ErrorPriorityReport(ByVal BackPriority As Integer, ByVal BackBoxes As Integer, ByVal Priority As Integer, ByVal SumOfBoxes As Integer) As String
If BackPriority Is Null And BackBoxes <> Null Then
MsgBox "Report will be inaccurate! There are blank priorities. Please run report on Customer Menu!", vbOKOnly, "Missing Priority"
End If

If Priority Is Null And SumOfBoxes <> Null Then
MsgBox "Report will be inaccurate! There are blank priorities. Please run report on Customer Menu!", vbOKOnly, "Missing Priority"
End If
End Function


Here is the field in the query calling the module:
ErrorCheck: ErrorPriorityReport([BackPriority],[BackBoxes],[Priority],[SumofBoxes])

 

[Lister]

If IsNull(Me.Priority) And Not IsNull(Me.SumOfBoxes) Then
MsgBox "Your Message", vbInformation, "Your Banner"
End If

Hope you get the idea and I hope it helps

 

[Rich]

You cannot use a query for this, you'll have to do what you want at Form level

 

[renenger]

Stop Report

Ok. I got my message to come up, however, it continues to pop up as the report formats. I am listing my code below. Basically, if this message pops up, I want to cancel the report. ?????

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.BackPriority) And Not IsNull(Me.BackBoxes) Then
MsgBox "Report will be inaccurate! There are blank priorities. Please run report on Customer Menu!", vbInformation, "Missing Priority"
Exit Sub
End If
If IsNull(Me.Priority) And Not IsNull(Me.Boxes) Then
MsgBox "Report will be inaccurate! There are blank priorities. Please run report on Customer Menu!", vbInformation, "Missing Priority"
Exit Sub
End If

If Me.BackBoxes > 0 Then
Me.BackBoxes.Visible = True
Me.Boxes.Visible = False
Else
Me.BackBoxes.Visible = False
Me.Boxes.Visible = True
End If
End Sub

 

[Lister]

So you are now running your "validation" of data on the format of the report?
This is not the best place to do it, and Exiting the Sub will not stop the report from running.
Which is probably what is creating the error.
Follow Rich's advice and validate your dataset on the form view (ie: OnClick event) and only run the report if the data set is "clean".

Hope this helps.