Find first record using distinct query

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)?

Thank you.
thandelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
vendor is text? if so, then its value needs to be delimited with quote marks -- single ' or double "
   ... and 0 would not be an appropriate default value

also, instead of looking for a 0 that might not exist if the control is empty, why not test it first?
   dim sVendor as string
   with me. cVendorSelect
      if IsNull(.value) then exit sub
         sVendor  = .value
      end if
   end with
   With Me
      'find the first value that matches
      .RecordsetClone.FindFirst "Vendor= """ & sVendor & """"
      
      'if a matching record was found, then move to it
      If Not .RecordsetClone.NoMatch Then
         .Bookmark = .RecordsetClone.Bookmark
      End If
   End With

Open in new window

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thandelAuthor Commented:
Thanks that worked I just needed to  split:

If IsNull(.value) then exit sub

TO

If IsNull(.value) then
     exit sub
else
      sVendor  = .value
End if
0
thandelAuthor Commented:
Question is there any advantage or disadvantage for your code to find a record vs. what I had using Set rs = Me.Recordset.Clone etc?
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi,

you're welcome -- glad you caught that! ... aircode ...

> "is there any advantage or disadvantage for your code to find a record vs. what I had "

I like to set values the procedure will use to variables up front -- personal habit to help me know what a procedure needs, especially when looking back from the future. Also, a control might change between the beginning and the end ... for instance, the .Value of a control might be set to Null if it is unbound (being used for lookup) ... and, anyway, a user may move to a different record making what is there incorrect so why should the search value stay on the form? could it be misleading? what is its purpose?

as for using "With Me" -- this is for performance since Me (the form object) will be used > 1 time ... maybe With Me.RecordsetClone since that is used >1 too and then Me.Bookmark in the code ... consider what you are using and what seems best to "hold"

With object ... think of it this way ...
if you are going to throw a ball up in the air 10 times, do you pick it up from the ground once and then throw it up 10x, or do you pick it up, throw it up in the air, catch it, put it down, ... pick it up, ... which is more efficient?

While setting to a specific object on the form like Me.RecordsetClone gains some traction, I have not done any speed tests to know if Me. is also better for performance** ... just that it makes the code easier to read -- indenting is important too.

** 6 of one and a half dozen of the other?

 ... another good thing about using With SomeObject ... End SomeObject is that it makes the code easier to read  -- especially if there is a reference with lots of characters.  Even if the reference is short, why repeat it? ... aside from performance considerations

"NoMatch"

I also tested for NoMatch -- so if what was being searched was not found, then nothing will happen.  What if the user filtered the records? Or the source of choices has more than what the form can show?

> "Set rs"

no need to create*** and set object variables (which the code posted fails to release anyway) ... WITH can be used with confidence ~

*** and therefore, allocate unnecessary space

> "each product  only has one vendor"

isn't this a bit unrealistic?

then this opens up the questions about differences and what to call things ... vendors, suppliers, manufacturers, ... perhaps a contact has multiple roles?

A cross-reference table between products and vendors (or whatever they may be called) might have ProductID, ContactID, Ordr (integer, default value is null, description = preference where 0 may be the default, 1 for first preference for alternate, 2 and higher -- additional suppliers****), etc ...

**** and this brings in pricing ... conditional on lead time, payment arrangements,  and other factors ... hence why an Access application is never 'done' ... just done enough for now ~

> " simple table with various products and vendors... each product  only has one vendor and there are many vendors"

"simple" and "flat" are not the same in Access.

One table where there should be more creates issues if the purpose of the Access application is longer than temporary, or slicing and dicing is desired.  I see  Products, Vendors, ProductVendors, and more ...

have an awesome day,
crystal
0
thandelAuthor Commented:
Thanks for your help!
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ respect that you desire understanding ... may this be a year of enlightenment, peace, and joy for you ~
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.