Question Trapping errors when no query criteria is given in MS Access

[josephbupe]

I am writing a small database using MS Access 2007. I have in it a function for running queries and printing reports. Printing works just fine if any one of the multiple query criteria is give.


I also want to trap errors and present a message if the user clicks the "Print" button without a query criteria. At the moment I only get a run-time syntax error message which has a "debug" button for accessing the entire code. I want a message that will say "Sorry, there was no search criteria".

This is the code for printing:

Private Sub cmdPrint_Click() 
Dim varWhere As Variant     
varWhere = BuildFilter      

' Update the record source     
Me.RecordSource = "SELECT * FROM q_Vehicles " & varWhere     
 
' Requery the form     
Me.Requery     

 ' Check if there is a filter to return...     
If IsNull(varWhere) Then         
varWhere = ""     Else     

'The WHERE clause does NOT need the word "WHERE"          
' strip off "WHERE " in the filter         
varWhere = Mid(varWhere, 7)     End If  DoCmd.OpenReport "rpt_Vehicles", acPreview, , varWhere 
End Sub

I thank you.

Joseph

 

[Trevor G]

Open the report in design view and the report properties, then under the Event Tab you have a property On No Data which you can place a message box and use a Cancel event.

 

[josephbupe]

Open the report in design view and the report properties, then under the Event Tab you have a property On No Data which you can place a message box and use a Cancel event.

I followed your advice. But still I can't get a custom error message.

Private Sub Report_NoData(Cancel As Integer)

MsgBox "Sorry, there was no search criteria"

Cancel = True

End Sub

By the way, the database does not open preview of reports, clicking the PRINT button only sends the report to the printer.

 

[JHB]

Do the test before the report is open.

 

[Trevor G]

How have you tested it? Suggestion. Create a Blank Report and then add the Message Box and test it when you look to Open it perhaps to Print Preview.

 

[josephbupe]

Thanx Trevor.

I have only added an error handler behind the PRINT button. I am not seeing the need to have the error trapper behind the report itself since the database does not show preview of the report upon pressing the PRINT button. It only sends to the printer.

This is the new code:

On Error GoTo Err_Msg
varWhere = BuildFilter

' Update the record source
Me.RecordSource = "SELECT * FROM q_Vehicles " & varWhere

' Requery the form
Me.Requery

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
'The WHERE clause does NOT need the word "WHERE"

' strip off "WHERE " in the filter
varWhere = Mid(varWhere, 7)
End If

DoCmd.OpenReport "rpt_Vehicles", acPreview, , varWhere

Err_Msg_EXIT:
Exit Sub

Err_Msg:
MsgBox "OOPS! Too many data to print." & vbCrLf & vbLf & _
"Please, search before printing.", vbExclamation, "Motor Vehicles Database"
Resume Err_Msg_EXIT
End Sub

I am yet to see how that will work.

Joseph

 

[Trevor G]

Your error trap won't work. I would also suggest that it goes into Print Preview for this very reason.

 

[JHB]

As mention in post #4, do the test before the report is open.

  varWhere = BuildFilter
  
  ' Update the record source
  Me.RecordSource = "SELECT * FROM q_Vehicles " & varWhere
  
  ' Requery the form
  Me.Requery
  
  ' Check if there is a filter to return...
  If IsNull(varWhere) Then
    MsgBox "OOPS! Too many data to print." & vbCrLf & vbLf & _
    "Please, search before printing.", vbExclamation, "Motor Vehicles Database"
  Else
    'The WHERE clause does NOT need the word "WHERE"
    
    ' strip off "WHERE " in the filter
    varWhere = Mid(varWhere, 7)
    DoCmd.OpenReport "rpt_Vehicles", acPreview, , varWhere
  End If