Auto-Complete Form article by The Gunny followup question



Hi everyone! I normally just lurk, but have been assigned a project that has
me stumped and is making my head hurt.

I created a form that would auto-complete some fields, based upon an article
I found here by The Gunny:

I duplicated this methodology exactly and got the form running, and then
added some additional fields to tblContractors and tblInvoices (that I don't
think affect the functionality described in the original article). For
example, when I select a contractor in the combo-box it will now pull the
contractor's address, state and zip code into the form. So far so good!

However, my challenge is this. I am trying in vain to try to get the combo
box in the form (that shows all the different contractors' names and lets you
pick one) to sort alphabetically by vendor name. Instead, no matter what I
have tried, it sorts by the auto-numbered ContractorID (that isn't even
shown), thus making it a challenge to find the Contractor I am looking for.
The contractor table currently contains 128 entries.

For example, I have tried changing the query Qryinvoices to sort by the
ContractorName field in tblContractors. Weirdest I think is, even when I am
actually in the form and use the 'sort ascending' or 'sort descending'
buttons on the toolbar, nothing happens (if the query is empty) or it only
changes the first item in the combo box (if the query has one or more records
because there are records in tblInvoice). The last thing I tried to do is
change the 'Order By' in the properties sheet to [Lookup_Contractor#].[Vendor
Name] DESC. (I also tried changing it to just [Vendor Name] and
tblContractors.[Vendor Name]).

In no case can I get the contractor list to change order, except in the case
where the query Qryinvoices has records in it. But even in that case, it
only changes the very first value in the Contractor list.

I'm definitely an Access novice who occasionally is assigned the task of
creating databases that are beyond me.=) I've normally been able to muddle
by and I try to use the help function and Google as much as possible.
However, this problem has me stumped, and I would appreciate any help anyone
can offer!

Dirk Goldgar

tbiaslorin said:
Thank you for your response, I will try those items!

For reference, this was the article, I had a brain cramp when I posted

That gives me more to go on. The Row Source property of the combo box
should be:

SELECT ContractorID, ContractorName FROM tblContractors
ORDER BY ContractorName;

Note that I posted that on two lines for clarity, but it should really be
entered all on one line in the Row Source property.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question