Combo box autocomplete problem when unbound to lookup field

I have to make this somewhat quick, but I have been able to duplicate the error on multiple machines running OS:XP Pro/Access 2003. Paul, you said you're using Access 2000 on XP Pro, so I'll be interested to hear your experience with this new, simple db I whipped up to demonstrate the problem. Wiz, of course I'm always interested to hear what happens on your machine, too, since you also have an Access 2003 installation.

There is only one form, OrderForm. It has the order number (autonumber, not editable), the customer name, and the product they ordered. The Orders use a foreign key to lookup the customer name and the product from tables called --- amazingly --- Customers and Products.

For some reason, autoexpand does not work on the Customer or Product fields on my machine. :confused: See if the same is true for you.

My best guess is that the autoexpand is failing due to some quality about the queries that populate these combo boxes...

Ed
 

Attachments

It must be in 2k3 format, as I can't open it. I can test from home later, where I have XP Home/Access 2k3.
 
omg, I have to try this immediately, will report in a few minutes...

Ed
 
Bobby Bobby,
He's our guy,
If he can't do it
Nobody can!
 
Paul: Sorry, you're right it's in 2K3 format, oops. You've been on this from the very beginning (like 5 mins. after I posted!) Your help has been great!

Wiz: Thx for confirming the problem wasn't exclusive to my machine...led me to try it on a co-worker's machine and then I eliminated the "my Access installation may be corrupt" possibility!

Bob: Holy heck! I was going mad trying to figure this out (see the "total newbie" ranking?). Indeed, I tried what you suggested, and voila, autoexpand is now working on the main db. You have no idea the trouble you've saved me! Thanks!

If it weren't for all of you guys, I'd still be calling it autocomplete. You did a good job edumakating me.

Ed
 
Whew! Glad to hear you are finally at peace. I totally can relate to those feelings of, "it's GOTTA work, so why isn't it!"
 
Wouldn't have mattered Ed, since it would have worked fine for me again anyway. I'm surprised service packs haven't sorted that issue out by now though. In any case, glad Bob found the answer for you.
 
That's excellent news. I was playing around with it at work today and had come to the conclusion that it had to have something to do with how the query was populating the combo - since all other possibilities had been looked at. But as usual, Bob to the rescue. :)
 
Yes, and it perfectly explains away why it was working fine for Paul, but not for Wiz and I: we chose to implement the ANSI-92 syntax option. Like a typical user, I thought to myself "I'm just using the stock MS Access, I haven't got any custom settings that should make mine different from Paul." Yet it was a single checkbox (!) that caused radically different --- and totally unrelated --- behavior between our clients.

This was my first go at posting to the forums. I lurk here a lot, trying to take things in. Bob, this kind of experience leads a fellow to want to give back to the community that saved his bacon. I'll try to be more of a contributor when others raise questions.

And Paul, thanks for sticking with me to the end, it was nice feeling like I wasn't alone in the investigation.

Ed
P.S. This REALLY should have been fixed in a service pack...
 
From the "bang-one-gopher-head-and-another-pops-up" department...

I'm not soliciting answers for this, I'm just posting it in case someone reads this thread later and takes the actions outlined above. I have the problem, and the solution.

I changed the queries to have "SELECT DISTINCT" in them and my combo boxes all began to behave properly, as we expected. :) But then later I went in to try and add records and the form I use to do that wouldn't let me! It was weird, too, because that form is supposed to come up in Add Mode (I even set the Data Entry property to 'Yes' on it to make double sure) but instead it would come up with all of the existing records! And on top of that, I couldn't edit a single darned field it it or even my normal 'Edit' form! :confused:

It took me about half the day to figure it out. In retrospect, it should have been completely obvious. The only reason one would not be able to add records or edit fields is if the underlying query for the form was somehow 'disconnected' from the underlying fields in the tables. And in fact, the query that populates that form is based on some of the queries that I added the DISTINCT qualifier to. Well, clearly you potentially lose a 1-to-1 relationship between the form fields and the table fields in doing so, so the form stopped letting me edit the related fields. Stay tuned, because here's the lesson...

Certainly sometimes it is necessary to based a form's underlying query on other queries (and in my case, one of the queries was necessary) but in general do try to JOIN only to tables for lookups!!! Queries only add overhead and a new dependency to worry about. Stick to tables where you can.

Thanks again to all who helped me here! :D
 
As an FYI, I forgot to tell you that and I used a separate query (not saved one, but the one you can build within the combo box) to do it so it didn't affect the other query you originally had. I was just so "psyched" about coming up with the main answer I forgot to include that other info.
 
I would have been psyched to have come up with such an arcane answer as well. :D
 

Users who are viewing this thread

Back
Top Bottom