Empty record when search fails - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Empty record when search fails

Peter van Soest

5 years ago

When I use this code to search for a record, Acces creates a new (empty) record, when the search fails.

How can i fix that?

Private Sub ZoekAchternaamBtn_Click()

        Dim s As String
        s = InputBox("Vul(deel van) de naam in", "Achternaam", "")
        If s = "" Then Exit Sub
        Me.Filter = "Achternaam Like ""*" & s & "*"" "
        Me.FilterOn = True


End Sub

Kevin Robertson @Reply

5 years ago

I think what is happening is the filter is returning no results therefore displaying no results. If you don't want your form showing no records try this.

    Dim S As String
    Dim numRecords As Long

    S = InputBox("Enter (Part of) the name", "LastName", "")
    If S = "" Then Exit Sub

    numRecords = Nz(DCount("CustomerID", "CustomerT", "LastName LIKE ""*" & S & "*"""), 0)

    If numRecords = 0 Then
        MsgBox "No records found"
        Me.FilterOn = False
    Else
        Me.Filter = "LastName Like ""*" & S & "*"""
        Me.FilterOn = True
    End If

	Richard Rost @Reply 5 years ago
Nice solution.

	Peter van Soest OP @Reply 5 years ago
Works like a charm, tnx!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View