I am using MS Access 2003 and I have a simple table with various products and vendors... each product only has one vendor and there are many vendors.
The table is sorted by vendor so I want to have the user use a combo box to select a vendor and then jump to the first record /product for that vendor. (like a quick jump on list to a section)
The query I have working for the combo box (cVendorSelect) is:
SELECT DISTINCT TCl.Vendor FROM TCl ORDER BY TCl.Vendor;
However the search is getting an error 113:
Set rs = Me.Recordset.Clone
rs.FindFirst "[Vendor] = " & Str(Nz(Me![cVendorSelect], 0)) <---causing the error
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
If I change the query and the combo box to:
SELECT DISTINCT TCl.ID, TCl.Vendor FROM TCl ORDER BY TCl.Vendor;
rs.FindFirst "[ID] = " & Str(Nz(Me![cVendorSelect], 0))
The search works but in the combo box the vendors are repeated (as there are many ID for a given product/vendor).
Any way to just search as to list only unique vendors and then jump to the first vendor it finds (Table is sorted by product/vendor already)?