Goto instead of Filter ! (1 Viewer)

ECEK · Dec 18, 2018

I have a search field on my form as a filter.

Could anybody direct me to AfterUpdate the forms data set remains in the same order but the focus goes to the relevant "searched" record.

Oh PS. It's on a split form (sorry !!)

Minty · Dec 18, 2018

If you apply a filter by definition the order of the records on the form will change, normally based around the underlying PK of the data, unless you specifically apply some other sort order ?

Not sure I fully understand what your asking?

As a word of warning split forms are really awkward to work with, many of us have abandoned them as it's easier and much more flexible to use a traditional sub form.

arnelgp · Dec 18, 2018

you don't need to filter.
use your search textbox to search for the next record:

Code:

Dim strSavedSearch As String

Private Sub cmdSearch_Click()
    If Trim(Me.txtSEARCH & "") <> "" Then
        With Me.RecordsetClone
            If strSavedSearch = Me.txtSEARCH Then
                .FindNext "[FieldToSearch] LIKE '*" & Me.txtSEARCH & "*'"
            Else
                strSavedSearch = Me.txtSEARCH
                .FindFirst "[FieldToSearch] LIKE '*" & Me.txtSEARCH & "*'"
            End If
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
        
    End If
End Sub

ECEK · Dec 18, 2018

Thanks Arnelgp. It works fine however it takes over a minute to actually work !!!

arnelgp · Dec 18, 2018

maybe you can add Index on the Field you are searching.

ECEK · Dec 18, 2018

The field that I am searching IS the index !!

Minty · Dec 18, 2018

If it's unique then remove the wildcards form the search string, assuming you are only ever looking for a exact match.

ECEK · Dec 19, 2018

Hi Minty
I'm struggling to remove the * correctly !!!
Could you possibly help by editing this please?

the FieldToSearch is a Unique ID

.FindNext "[FieldToSearch] LIKE '*" & Me.txtSEARCH & "*'"

arnelgp · Dec 19, 2018

minty is telling to use equality operator.
partial search is slow if you have volume of data on your table.

.FindFirst "[FieldToSearch] = '" & Me.txtSEARCH & "'"
.FindNext "[FieldToSearch] = '" & Me.txtSEARCH & "'"

ECEK · Dec 19, 2018

I'm afraid Im getting no results from the changed code Arny ?

Minty · Dec 19, 2018

What's the code you are now using, and what is the data type you are searching for?

ECEK · Dec 19, 2018

This is my current code that doesn't error. It just doesn't work.
I have entered my own field names instead of Arny's generic names.

GotoCPID is the search field
ID is the field to search

Code:

Private Sub GotoCPID_AfterUpdate()

Dim strSavedSearch As String


    If Trim(Me.GotoCPID & "") <> "" Then
        With Me.RecordsetClone
        If strSavedSearch = Me.GotoCPID Then
        .FindNext "[ID] = '" & Me.GotoCPID & "'"
        Else
        strSavedSearch = Me.GotoCPID
       .FindNext "[ID] = '" & Me.GotoCPID & "'"
        End If
        If Not .NoMatch Then
        Me.Bookmark = .Bookmark
        End If
        End With
        
    End If
End Sub

The data type is text.

Minty · Dec 19, 2018

You've moved the

Code:

Dim strSavedSearch As String

into the sub so it's getting reset every time you run the code.

ECEK · Dec 19, 2018

I tried to put it before the code but when I debug its says that
"Only Comments may appear after end Sub, end property End function"
I then removed it all together but still no results !!

ECEK · Dec 20, 2018

FIXED
I found this.

With Me.Recordset
.FindFirst "[ID] = " & Me.GotoCPID
If .NoMatch Then MsgBox Me.GotoCPID & " was not found"
End With

Goto instead of Filter ! (1 Viewer)

ECEK

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

ECEK

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

ECEK

Registered User.

Similar threads

Users who are viewing this thread