Msg if no record; (1 Viewer)

[theinviter]

HI
i Have a form which filter after entering code number, as code below;
but i wannt to show msg box if no record found and cancel filter, how to modify the code below:


Private Sub filterThisForm1()
Dim strFilter As String
On Error GoTo err_handler
If Len(Trim$(Me!Combo79 & "")) Then
strFilter = "code = '" & Me!Combo79.Column(2) & "' Or [Other Code] = '" & Me!Combo79.Column(3) & "'"

Else
strFilter = "code like '" & Me!FindCode & "' Or [Other Code] like '" & Me!FindCode & "'"

End If
With Me



.Filter = strFilter
.FilterOn = True
![Table1 Subform].Form.FilterOn = False
Me.Table1_Subform.SetFocus
DoCmd.GoToControl ("[Qnty_IN]")
DoCmd.GoToRecord , , acNewRec
End With
exit_here:
Exit Sub
err_handler:
MsgBox Err.Number & ": " & Err.Description
Resume exit_here
End Sub

 

[bob fitz]

Indenting your code makes in much easier to read.

Private Sub filterThisForm1()
    Dim strFilter As String
    On Error GoTo err_handler
    If Len(Trim$(Me!Combo79 & "")) Then
        strFilter = "code = '" & Me!Combo79.Column(2) & "' Or [Other Code] = '" & Me!Combo79.Column(3) & "'"

    Else
        strFilter = "code like '" & Me!FindCode & "' Or [Other Code] like '" & Me!FindCode & "'"

    End If
    With Me



        .Filter = strFilter
        .FilterOn = True
        ![Table1 Subform].Form.FilterOn = False
        Me.Table1_Subform.SetFocus
        DoCmd.GoToControl ("[Qnty_IN]")
        DoCmd.GoToRecord , , acNewRec
    End With
exit_here:
    Exit Sub
err_handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume exit_here
End Sub

 

[bob fitz]

You try something like:

If Me.Recordset.RecordCount = 0 Then
   Me.FilterOn = False
End If

immediately after you turn the filter on.

 

[ebs17]

but i wannt to show msg box if no record found

My users feel sufficiently informed when they see the blank form. But these users would go crazy if they had to constantly click away superfluous messages.

 

[theinviter]

You try something like:

If Me.Recordset.RecordCount = 0 Then
   Me.FilterOn = False
End If

immediately after you turn the filter on.

but if i want the show msgbox " no record found" and set focus on "Findcode".

 

[bob fitz]

but if i want the show msgbox " no record found" and set focus on "Findcode".

Put your Msgbox code on the line after Me.FilterOn = False and on the next line set the focus with something like:
Me.[NameOfControlToBeSet].SetFocus

 

[Pat Hartman]

I'm with @ebs17 . You don't want to train your users to ignore your messages. Save them for something important that you want them to pay attention to.