combo box item not in list (!)

[keith_pyrasoft]

I posted this problem some months ago and attracted no replies, so I've tried to explain it better:

I distribute sizeable applications in the form of Access 97 .mde files.

The problem relates to combo boxes where the bound item is a numeric item queried from a table. Typing a value into the combo will give the message 'The text you entered isn't an item in the list' even though it is. The data item may only be selected by scrolling down the combo list and clicking on it.

I am completely perplexed by the fact that this behaviour differs from one PC to another! This was made particularly evident recently when I replaced a PC on a customer network - the problem appeared on the new PC only! This from the same compiled file on similar operating systems.

Problem certainly encountered so far on WIN98 and XP. Text fields always work.

Has anyone else encountered this problem please??

Thanks

Keith

 

[dcx693]

Sounds like a similar problem to the one I had here: Combo box rowsource data type question .

To date, I have no solution, and no one came up with any good ideas.

 

[WayneRyan]

Keith and DCX,

Wow, that's an interesting problem. I "fixed" it by shutting
the stupid error message off. Well its sort of a fix.

I also don't see why an unbound combo should act like that.

Wayne

 

[keith_pyrasoft]

Thanks for the interest dcx693 and Wayne - I still can't get my head round what's happening here or rather, why.

Wayne, even if I turned off the error message (can you do that in Access 97?) it wouldn't help, because the combo actually doesn't accept anything typed into it..... it really does fail the match.

One observation though - for those installations that display the problem, ALL numerically-inclined combos within the database misbehave identically. It has always behaved like something environmental, but I can't identify what!

 

[dcx693]

keith_pyrasoft, I found the behavior to be intermittent. It never happened to me on Access 97, but started on 2000. I'm not sure if it happened to me on XP or not...don't recall. But I'd open up the form, experience the problem with numeric entries in the combo box, then close the form and try again, and it would then work. I just decided to live with it.

 

[keith_pyrasoft]

That's curious, dcx693. Those intermittent problems tend to make me suspect database bloat.... I've had some pretty variable behaviour myself. However, this particular combo problem is 100% reproduceable: ie, on computer A it will always occur, on computer B never.

 

[keith_pyrasoft]

frustration.....

I'm still no nearer to solving this one........ is it just not happening to anyone out there??

 

[keith_pyrasoft]

In case it throws any more light on the matter:

On one of my applications I have removed Limit to List from one of the misbehaving combos, and this alleviates the problem as it allows a valid entry to be typed in. However, the underlying problem is still there, because auto expand doesn't happen.

I'm having to experiment blind, because the behaviour doesn't happen on any of my PCs!!

 

[samcoinc]

Here is something that I have found. I have a form that lets you type in a die number and then finds that record on the form. Also on the form is code to make up a file path to a autocad drawing stored on another drive letter. It then allows you to click on a button to view that autocad drawing in a active x control on a separate form.

Now if I do not have the drive mapped I get that error 'The text you entered isn't an item in the list' even though it is in the list. The second I map the drive letter then the combo box filter works. I haven't looked into it much more than I know that the drive mapping has something to do with my problem.

sam

 

[keith_pyrasoft]

Still unresolved

I still encounter this problem... I recently experienced it on a PC that had not been displaying it and now is - I currently suspect some sort of windows update. Anyone else getting this effect?

 

[Junyer]

Small hope

I've been encountering this problem pretty regularly. I have found that if the database is moved, the combos loose their functionality. I think something hidden doesn't get copied. I also notice that when one combo "breaks", all of them on the same form break. One thing I do that isn't pretty but works is to create a new combo with a row source to anything and then hide the new combo. All of a sudden, all of the combos begin to work correctly. If I remove the new hidden combo, they all break again. Like I said, it isn't pretty, but it works.

 

[codehead]

Found it!!! At least for my situation.

Not sure if this applies to '97 (I'm using 2003), but if you have your query option set to use ANSI '92 SQL, then you have to have the combo box's Row Source set to a Distinct query.
I was experiencing the exact same problem as mentioned in the first thread, was pulling my hair out, then it dawned on me that this is the same issue as the 'Auto Expand not working issue' when the ANSI '92 option is set, as stated here by MS: http://support.microsoft.com/kb/824189.

Ahhh, relief!

 

[mistyblu]

combo box item not in list - but it IS (!)

I am having exactly the same problem as Keith first posted 5 years ago! An Access97 .mde application which behaves on some pc's but not on others. Only numerical combo boxes display this behaviour.

On the particular pc in question (an XP machine), it was working fine one day but not the next so I have to agree with Keith that it feels environmental and quite possibly due to an update. I've yet to find a significant difference between a machine that works and one that doesn't. Access97 has been reinstalled and all patches and updates applied.

Codehead - I couldn't find the ANSI option in 97 but surely it must be something similar or is that just wishful thinking?

This is unbelievably frustrating and no-one else seems to be talking about it. Any help would be so much appreciated.

 

[codehead]

Mistyblu: do you have the option of getting ALL users on the same version, say the Access 2003, level? Age-old Access '97 isn't going to offer having query options set to use ANSI '92 SQL, which is what I'm used to. I believe that started with ver 2000. Otherwise, can't really speak to '97 in this respect.

BTW: these days I'm a HUGE advocate of developing Access Applications as true client/server .adp's (Access Data Projects). Practicing this is possible within our own (local) environments if we first setup the freely downloadable MSDE. Access offers a sample Northwind adp under its Help/Northwind Sample Access Project. If you'd like (or intend) to develop at the Enterprise level, go there!
Codehead.

 

[mistyblu]

Thanks Codehead. Unfortunately I have little control over this. It's an old application at a client site and they are not prepared to spend money on upgrading or developing the application. I have no choice but to maintain it as it is but fortunately it very rarely goes wrong. This is such a silly little issue but it's causing the customer a real headache (and me!).

As a company, we moved away from database design/development a few years ago so I haven't kept up with progress. This client has just upgraded to Office 2007 and I probably should be looking at how easy/difficult it is to upgrade the application perhaps as a goodwill gesture. Thank you for your pointers. You've stirred up my interest after all these years!

MB

 

[codehead]

Mistyblu: two thoughts here:
FIRST: (actually not my best suggestion, the SECOND one IS, but unless this gets redundant, you may want to read on anyway
I re-read the top thread (since you identified w/it), and because of the peculiar things happening with the same app over different work stations, I was going to suggest assuring EVERYONE has their DB's linked via UNC Links! Not sure where you're at, but UNC stands for "Universal Naming Convention," and represents an Absolute network address, as opposed to a PC user's "mapped" address, the latter being local to ONLY THEIR PC!!! This may or may not have anything to do with your issue..., I'm stabbing in the dark here, but IT IS ALWAYS GOOD PRACTICE TO USE UNC LINKS for all your user's shortcuts. And if you're a bit Geeky like me (hey, they're supposed to be calling us Guru's these days ), then use the UNC's everywhere! Seriously, there's never any doubt when you're linking using UNC's, and they can take the place of any 'mapped' drive.
SECOND: Definitely, but carefully, go for the upgrade!
Geez, this is a no-brainer. Though I've NOT used Access 2007 at all, compliance-wise, MS should assure that their old stuff (yeah, even '97 vintage,) is compatible with their latest stuff! SO, if/when the client has Access 2007 installed, try this:
1) Backup the existing .mdb!
2) Make another COPY of the .mdb, and name it with original name and post-fix the date, or whatever. This is going to be the (potentially) new version that'll fix things up.
3) run the upgrade Wizard! Hey, you're only using a COPY, there's no damage done if things go wrong.
4) if all goes good this far, test that dropdown!
Let me know how things go...,

 

[gemma-the-husky]

if the list is unbound then it may be something to do with text/numeric, in that there is a difference between a number of 1, and a text string of "1"

i would expect an unbound combo box will format by default as text

i've not checked this at all, just a thought

 

[Colin_B]

If anyone is still trying to get a resolution to this kind of problem I may have a solution as I had the same problem.

I had a table called Clients with the first 3 fields as ClientID, AccountNo & AccountName. On one of my forms I had a combobox, using these 3 fields and bound to the AccountNo field, where I could type in an AccountNo and it would select the correct client. Suddenly, when I typed in some AccountNo's the combo worked fine but then when typed others I got the "item not in list" error, but scrolling down the list the item was actually there.

When I checked through the records in my database I found that in one record the AccountNo field was empty and it was any records after this particular record that caused the error.

I added the AccountNo field back in manually and now everything works as it should.

Hope this helps and points people in the right direction

Colin

 

[ddobier]

I've experienced this before as well and it was due to having a null value in the first visible field in the combo box (the one your typing in a value to match on). Once I removed that record all was well and typing in a value worked as expected. Can't explain why a null value would cause it to behave this way but it does.

Good luck!

Darren

 

[Dixo]

I also found this problem with null values. If there is a problem with null values appearing then setting a criteria of Is Not Null to the first visible column in the rowsource could be used as a work around.

Peter