CODEWITHSUNDEEP
vbams-access
PhoenixJay
PhoenixJay

Reputation: 161

Access 2007 Search Button

I'm having trouble trying to get this to work for me, I have a inputbox to locate an account number in my table, but i want it to continue to keep locating that account number as my table with have more than one line with the same account number. This is what i currently have but I cant figure out how to do a find next record with the same account number.

Dim strAccount As String
Dim rstQA As Recordset

strAccount = InputBox("Enter Account Number")

If strAccount = "" Then Exit Sub
Set rstQA = Me.Recordset.Clone
rstQA.FindFirst "[Account Number]='" & strAccount & "'"
If rstQA.NoMatch Then
    MsgBox "No record of account"
Else
Me.Bookmark = rstQA.Bookmark
End If

rstQA.Close
Set rstQA = Nothing

Upvotes: 0

Views: 232

Answers (1)

Simon1979
Simon1979

Reputation: 2108

Assuming you want to display the matching accounts in the form your best option is probably to filter the form:

Dim strAccount As String

strAccount = InputBox("Enter Account Number")

If strAccount = "" Then Exit Sub
Me.Filter  "[Account Number]='" & strAccount & "'"
Me.FilterOn = -1

To return to unfiltered code a separate button:

Me.Filter = ""
Me.FilterOn = 0

Don't forget to put your Search and Unfilter buttons in either the header or footer of the form, otherwise if you filter returns no records you'll have a blank form.

Upvotes: 1

Related Questions