Error trapping

[josephbupe]

Hi,

Please, I have limited logic capability on this.

The text boxes for search criteria and the SEARCH button are on the main form while the search results are on a pop up form.

I do not want the details form to open if the provided criteria does not match with any record in the database. Instead I want the error message "Sorry, no search criteria" to pop up, but in my current state when there are no matching results/no criteria the details form still opens with some records together with the error message.

Here is the code behind the SEARCH button:

Private Sub btnSearch_Click()
On Error GoTo Err_Msg

If Not IsNull(Me.Form) Then

    DoCmd.OpenForm "f_search"
    ' Update the record source
    Forms.f_search!f_search_sub.Form.RecordSource = "SELECT * FROM q_vehicles " & BuildFilter
    
    Me.Requery
Else
 
Exit_btnSearch_Click:
Exit Sub

Err_Msg:
MsgBox "Sorry, no search criteria.", vbInformation, "MV Clearance"
End If
End Sub

 

[JHB]

Then run the query before the pop up form is called, (you can use a recordset object), if returned record set is null, then show the message else open the pop up form.

 

[josephbupe]

Then run the query before the pop up form is called, (you can use a recordset object), if returned record set is null, then show the message else open the pop up form.

Hi JHB,

I don't follow. How do I set the recordset?

 

[JHB]

Hi JHB,

I don't follow. How do I set the recordset?

Like shown below, (but if you can use "BuildFilter" in the recordset depend on how it is composed, references to forms or controls on forms can't be used):

  Dim dbs As DAO.Database, rst As Recordset

  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT * FROM q_vehicles " & BuildFilter)
  If Not rst.EOF Then
    DoCmd.OpenForm "f_search"
    ' Update the record source
    Forms.f_search!f_search_sub.Form.RecordSource = "SELECT * FROM q_vehicles " & BuildFilter
    Me.Requery
  Else
    MsgBox "Sorry, no data match for the search criteria found.", vbInformation, "MV Clearance"
  End If

 

[josephbupe]

Thank you JHB.

Stay well.