CODEWITHSUNDEEP
ms-access
user1868306
user1868306

Reputation: 75

MS Access Error Trapping

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

Upvotes: 0

Views: 63

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8414

You can easily do a search first, and then open the form only if that search returns results:

Private Sub btnSearch_Click()
Dim rec as Recordset
Dim db as Database
On Error GoTo Err_Msg

Set db = CurrentDB

If Not IsNull(Me.Form) Then
    'First open the recordset
    Set rec = db.OpenRecordset("SELECT * FROM q_vehicles " & BuildFilter & "")

    'Check to make sure the recordset isn't empty.  If it is, exit the sub
    If rec.EOF = true then
        GoTo Err_Msg
    EndIf

    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:
    Set db = Nothing
    Set rec = Nothing
Exit Sub

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

Upvotes: 1

Related Questions