Filtered results opening recor - Open Form in Microsoft Access


	Back to Open Form Comments List

Filtered results opening recor Upload Images Link Email

Dan Pickle

7 months ago

I have a search field in my database which works well except when the results come up they are filtered for the 1 record.  My boss wants to be able to go to the previous or next record easily however since the results are filtered it only shows the 1 record so there is not a next or previous record to go to.  here is the cope of the code that I am currently using

Private Sub WireNumSearch_AfterUpdate()

    Dim ID As Long
    ID = Nz(DLookup("ID", "WirelistT", "Num=""" & WireNumSearch & """"), 0)
    If ID = 0 Then
        ' Wire number doesn't Exist
        If MsgBox("Wire Number not in Database") Then
    Else
        ' Do Nothing
        Exit Sub
    End If
    Else
        'Wire Number Exists
    DoCmd.OpenForm "WirelistF", , , "ID=" & ID
    End If

End Sub

	Sami Shamma 7 months ago
He can click on the filter clear icon at the bottom of the form.

	Kevin Robertson 7 months ago
I usually add a button or a text link to clear the filter.

	Dan Pickle 7 months ago
I tried that but when you click to unfilter the results then it takes you to the first record......not a good thing when you have 18k of records

	Kevin Robertson 7 months ago
Turn Off Filter

	Dan Pickle 7 months ago
Kevin that is exactly what I was looking for thanks! now the question is can I incorporate that into the initial search code that I have above?

	Kevin Yip 7 months ago
Hi Dan, you can use the FindFirst command to move to the desired record without using filtering, so all records will still be visible. E.g.: Me.Recordset.FindFirst "[field1] = " & searchstr where "Me" is your current form. Note that it only takes you to the first record that satisfies the criteria. If you have multiple records that satisfy the criteria, you also need to use FindNext.

	Dan Pickle 7 months ago
Kevin 2 questions on your solution. the search string would be coming from a different form so how would I point to that? second where would this go? in the on load event?

	Kevin Yip 7 months ago
You just add extra code right after the OpenForm command in your code above. First you need to check if the form is actually opened first, with this: Do: Loop Until CurrentProject.AllForms("WirelistF").IsLoaded Then you use FindFirst as I showed earlier, but with a direct reference to the form that just opened: Forms!WirelistF.Form.Recordset.FindFirst "ID=" & ID

	Dan Pickle 7 months ago
Kevin that worked like a charm! Thank you!

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