• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

Combo-box problem - Coming up with "The text you entered isn't in the list" - when it is.

I have an Access form with several combo-boxes, all with "Limit To List" set to True.  When I first login and enter something that is not in the list, I have some code triggered by "On Not in List" that asks me if I wish to add it, I select yes and it adds it correctly, with no extra prompts.

However, my form/application allows users to switch to other projects/back-ends.  These back-ends are all very structurally similar but have different sets of data, dependant on the projects and need to remain completely separate.  As soon as the applications re-links to the chosen back-end, the "On Not in List" event that worked originally, now has an issue.  It comes up with the message, "The text you entered isn't in the list.", which is the problem.  If I look at the new entry, it is in the drop-down list and if I select it from the list - it all works fine (but obviously, I shouldn't need to reselect it from the list).  

Simply closing the form and re-opening it - doesn't work.  I actually need to exit the Access application completely, re-open it - and once again, it works.

The combo-boxes are pretty much all text fields (not just numbers / date), and the combo-box fields themselves are bound to their respective fields in the main table.  However, the "row source" of the combo-boxes is a separate table that is much smaller, grouped and indexed.
0
Andy Brown
Asked:
Andy Brown
  • 11
  • 5
  • 2
  • +2
6 Solutions
 
Gustav BrockCIOCommented:
It sounds like you need to requery the combobox after having switched database.
0
 
Andy BrownDeveloperAuthor Commented:
Hi again - I think I've tried that - but am re-checking everything.  However, you actually have to quit the application (not just the form), for it to work properly again.  That is until you re-link to another back-end....
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Probably something in your code prevents from correctly requering the combo box...or maybe your code doesn't correctly writes the new data to the rowsource table...when you encounter this error just open the table from another Access and check the entries...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Andy BrownDeveloperAuthor Commented:
Thanks John - will do, but the entries are definitely there, as I can look at the combo-box, with the new entry already there (and can re-select it from the list without leaving the record/form).  Also, when I go back to the home screen, and back in - it's still there, but the same error comes back-up when I add another new value.  The error doesn't go away until I exit completely and go back in.  

Incidentally, when I go back in, it doesn't do anything to the tables/links/queries - it just opens up from where it left off and works.
0
 
Andy BrownDeveloperAuthor Commented:
I'm 90% sure there is something funny going on with Access.  Just in case I'm right - is there another way that I could use to emulate the noinlist event?
0
 
EirmanChief Operations ManagerCommented:
You could try ComboNAME.Requery in various places such as
On Current
On Load
After Update
etc.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
It makes me curious...can you give us the piece of code you are using.?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The combo-boxes are pretty much all text fields (not just numbers / date)>>

<< However, the "row source" of the combo-boxes is a separate table that is much smaller, grouped and indexed.>>

<<However, my form/application allows users to switch to other projects/back-ends>>

 I'm wondering if it's related to database format....is this table the combo is tied to shared in a common backend, or is it a separate table in each backend?

 It's not clear to me how you can add a new item to this list, switch backends, and then still see it in the list unless something is being shared.   But then I don't understand why a switch would trip it up.

 Little more detail would be helpful.

 I'm assuming when you switch back ends, all forms, reports, etc are closed and you just have a menu open.

Jim.
0
 
Andy BrownDeveloperAuthor Commented:
So just to summarise - our clients use a single front-end to manage multiple projects, which need to be kept completely separate from one another.  

Each project has it's own folder on the server and two back-end .accdb databases.  The main/larger back-end contains the contact/project information - and sits on the server (in its own folder).  The second/smaller back-end is only used for project settings and a couple of tables used for an Undo function.  This back-end is copied to the local drive - into the same folder as the front-end (it used to also contain the drop-down master table, but I ended up moving that to the main back-end).  

When a user switches projects, it links to the relevant, larger back-end first.  It then copies the smaller/settings back-end to the local drive and off they go.

What I discovered this morning......

I noticed that when the NotInList issue occurred, the "AutoExpand" function would also stop working (until you came completely out of the application and went back in).  So, I wanted to establish the minimum change/function that caused this to happen.  It turned out a simple requery command on the form was all that was needed (I'm sure other things would also).

To isolate things further, I created a brand new form, added a button that simply ran the following: Me.Requery added a combo-box (it didn't matter whether it was bound to a field or not), as soon as I pressed the Requery button, "AutoExpand" stopped working.  So, I started experimenting with the combo-boxes rowsource, which is where the problem lies (on the surface).

Initially, the rowsource was set to query called "qryDD_RecordGroup", this query took the data straight from a table called tblDropdowns, which is the master table I have been using as the source for each of my dropdowns and listboxes:

SELECT tblDropdowns.Description FROM tblDropdowns WHERE (((tblDropdowns.Category)="RecordGroup")) GROUP BY tblDropdowns.Description;

When the rowsource is set as above - we get the issues

If I change the query to pull the data from the main contact table:

SELECT [_tblMain].RecordGroup FROM _tblMain GROUP BY [_tblMain].RecordGroup HAVING ((([_tblMain].RecordGroup) Is Not Null)) ORDER BY [_tblMain].RecordGroup;

It works - EVERY TIME.

I'm not quite sure what this means, and I'd rather not pull the data directly from this table as there could be 100k records in there - but I feel that I'm getting somewhere now.

If you spot anything obvious from my above ramblings - please let me know.  I'll update this post if I spot anything.

Thanks as always everyone for your help.
0
 
Gustav BrockCIOCommented:
I guess Access is caching some data, and that cache is not aware of the change of backends.
0
 
Andy BrownDeveloperAuthor Commented:
Could be - thanks Gustav
0
 
Andy BrownDeveloperAuthor Commented:
It looks like it has something to do with the structure of `tblDropdowns`
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Do a dbEngine.idle dbRefreshCache after you link to a new BE and see if that clears it up.

Jim.
1
 
Gustav BrockCIOCommented:
A lookup-up field? I never use those.
0
 
Andy BrownDeveloperAuthor Commented:
How would you do it Gustav?
0
 
Gustav BrockCIOCommented:
I just create normal queries - as you had to in previous versions without that feature.
0
 
Andy BrownDeveloperAuthor Commented:
But if you've 100k records (_tblMain), and the combo box is just trying to provide, 10 unique values (RecordGroup) - doesn't that slow things down?  For example:

SELECT [_tblMain].RecordGroup FROM _tblMain GROUP BY [_tblMain].RecordGroup HAVING ((([_tblMain].RecordGroup) Is Not Null)) ORDER BY [_tblMain].RecordGroup;
0
 
Gustav BrockCIOCommented:
Perhaps, but then you could maintain a small table of the RecordGroups in use.
0
 
Andy BrownDeveloperAuthor Commented:
That's kind of what it is - but I use this table for all lookups (combo/listbox).  This way, if in some worst case scenario, you've got 100K db, with several thousand unique job titles (I know - definitely not recommended),  even in this case, you are only pulling in data from a table with four fields (in my case), rather than running a query on the main field/db each time.
0
 
Andy BrownDeveloperAuthor Commented:
Arrrrggghhhhh - but it's fixed!!!

After changing the rowsource queries from:

SELECT tblDropdowns.Description FROM tblDropdowns WHERE (((tblDropdowns.Description) Is Not Null) AND ((tblDropdowns.Category)="RecordGroup")) ORDER BY tblDropdowns.Description;

to

SELECT tblDropdowns.Description FROM tblDropdowns WHERE (((tblDropdowns.Category)="RecordGroup")) GROUP BY tblDropdowns.Description HAVING (((tblDropdowns.Description) Is Not Null)) ORDER BY tblDropdowns.Description;

Everything worked.  I can't believe it took so long.
0
 
Andy BrownDeveloperAuthor Commented:
As always - when you know - you know......

Thank you, everyone, for your help and perseverance.  Also a special shout to Jim for the extra code snippet (I should have had that in there already).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now