Form search using a combo selection

  • Thread starter Thread starter So-Ange
  • Start date Start date
S

So-Ange

Hi All,
Urgent code help! I am working on a form that has a Combo to select a
vendor and display the vendor record. This part is done. Taking it up
a notch: That vendor may have multiple contracts; So what I will like
to do is add a next/previous button base on the combo selection. I
could let the Users Filter by selection but They are new to access and
can't count on them doing it properly. to save myself a lot of trouble
long run I want to create a button that will do the work. I am using
Access 2000.
 
So-Ange,
I take it you're using the FindRecord function using the value in the
combo selection?

Try using the value from the combo to "filter" your recordset. The user
would end up with several records for that Vendor, and could use the normal
navigation buttons to find the record they need.

Something like this on the combo AfterUpdate...
Me.Filteron = False
Me.Filter = "Vendor = '" & cboYourVendorCombo & "'"
Me.Filteron = True

If that returns too many records, consider adding a more unique value to
your combo, to go more directly to the record exact record you want. Say...
a ContractNo or other key field.
 
Al,
Thank you so much! You were on point with exactly what I needed.
I wasn't sure where to insert the code, but it worked on first try.
Here is the workable code
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[VendorCompanyName] = '" & Me![Combo44] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
If Recordset.EOF = True Then
MsgBox "No more records to display"
Exit Sub
End If
Me.FilterOn = False
Me.Filter = "[VendorCompanyName] = '" & Me![Combo44] & "'"
Me.FilterOn = True
End Sub
Can I profess, I Love this User group.
 
Back
Top