Access Autocomplete Fails after Compact-and-Repair or Reopening Access

[W4NMP3]

Hello from Pennsylvania, with my first posting. I am just getting back into Access after a long layoff. Now, my Combo Boxes ‘Autocomplete’ work perfectly until “Compact and Repair” or the app is closed and reopened (then “The text you entered isn’t an item in the list” appears). Does anyone know why?

[NeoPa]

Hi there. Welcome to Bytes.com :-)

We may need a little more info from you. Very common when new members start posting. Does the error message appear in spite of the items being in the list? Or is the actual problem that your ComboBox somehow has different data available after a C&R?

[W4NMP3]

When typing in the combo box (except immediately after creating a form and combo box) the only thing that appears is the letters entered. Hitting tab or enter gives the “The text you entered…” message. After cancelling the error message the list of names appearing starting at the top of the list, as if using the pull-down.

A new discovery: I copied two tables from my new program into a copy of an Access program I wrote a few years ago, then created a new form in it using the two tables (to replicate the problem). The combo boxes worked as designed. Next, after copying the problem form (with the combo box problem) into the older program, the combo boxes in the older program have the autocomplete problem.

Both the new and old programs show “”Access 365 Version 2406 - Build 16.0.17726.2010 8 64-bit. My PC shows: Windows 10 Home Version 22H2 - Build 10.0.19045.4529 64-bit

[NeoPa]

Hi again.

I'm still not clear on the precise answer to my question, though I clearly see real intent to give as much info as possible, for which I commend you :-)

I'm trying to determine whether the ComboBox is behaving in a way that is undefined, such that it might be a corruption or some such, or in a way that actually fits the data available - but that data isn't what you expect.

When you see the error message can you see (as opposed to simply expecting it to be there without visual verification) an item in the list that matches what you've typed but which isn't selected when you type it?

The only other item I can think of (other than corruption or data mismatch) is the setting of the ComboBox.LimitT oList property. Not sure even if that would affect this situation.

To be honest, nothing obvious springs to mind other than corruption. Hard to diagnose at a distance, obviously, but assuming all you've told me is accurate the fact that putting the same into another database seems to cause it to work as expected leads me to discount everything else I think of.

I'm happy to look further if you wanted to send me a ZIPped copy of the two different files (Cut down as much as possible please so they only include the basics necessary to illustrate the behaviour.) in a ZIP. Feel free to attach it to a PM if you prefer.

[jimatqsi]

Well now, this is interesting. Let's get some more information.

It's especially interesting because you refer to combo boxes, plural. So I'm thinking the problem may lie outside those objects. But first, about those objects.

How many boxes are we talking about? Are there some that do not fail?
What is the row source type or types (Table/Query, Value List, or Field List)? All the same type ?
Please share the Row Source of at least one.
Do you have code that dynamically changes the Row Source or any property of the objects?
If the Row Source is a query, try saving that query as a separate object and run it outside the form. Do you get the expected result at all times?
Is the object bound to a field?

Maybe add a button to your form that opens the query, if that's what it is. Click the button before and after you try the combo box. You could use code like this:

Private Sub CheckComboSource()
Dim qry As DAO.QueryDef
Dim strSQL As String
Dim dbs As DAO.Database

Set dbs = CurrentDb
Set qry = dbs.QueryDefs("Temp_qry")  ' create some temporary select query for use here 
strSQL = Me.comboboxname.RowSource  ' get the rowsource of the object
Debug.Print strSQL                                      ' or msgbox strsql if you like
qry.SQL = strSql                                           ' change the sql code of the query
DoCmd.OpenQuery qry.Name                    ' whatever result you get here is what should be in the dropdown combo
Set qry = Nothing
Set dbs = Nothing

End Sub

Other points of interest:
Do you have any form level events that might be impacting this, such as On Mouse Down, On Key Press, etc....

You said closing the app creates the problem. I wonder if you mean the form or the database itself. If you close the form and open it again, does the problem present itself?

After reading your second post again, it gives the impression that there are two combo boxes and each uses a table for the Row Source. Is there something in the design of the tables that might be impacting this? Some relationship between the two, some unusual (to me, anyway) element in the tables?

What do you mean by " the combo boxes in the older program have the autocomplete problem."? Are you saying that the problem has spread to other forms with combo boxes?

This is a very curious one. I would also participate in examination of your database if you care to send a copy to me.

Good luck. It's a fascinating situation.

[W4NMP3]

My "older program": I dug out an Access program I wrote a few years back and opened it. The Combo Boxes worked just fine. Since the program I'm working on now is short and simple I created two of the tables (used in the "new" program) in the "old" program and combo boxes in this form WORK as designed. I will be continuing that process (rewriting the program in a cleaned up copy). Here is a link to ZIP'd (partial) copies of the functioning and non-function form

Thank you for your help

https://drive.google.com/drive/folders/1HXywqemE40gFnp_5Qb-JuY8uwqyqVLUw?usp=drive_link

[W4NMP3]

Thia link to the compressed files should work better, I hope

https://drive.google.com/drive/folders/1HXywqemE40gFnp_5Qb-JuY8uwqyqVLUw?usp=sharing

[NeoPa]

Hi Dick.

We have Jim on board now too, which never hurts.

I'm coming up to a busy few days at this time so while I can confirm I've downloaded your databases, I probably won't get time for a serious delve just yet unfortunately. I'm reassured you have a workaround for now at least, but I hope to dig further when things normalise here.

It may be that it works in the new database because the old one has a corruption somewhere. Just a guess for now but corruptions do happen in Access databases - more frequently than we'd like.

[W4NMP3]

Yes, I have a workaround so no urgency at this point. I do not understand (but certainly accept) one Access db on my both desktop and laptop having the issue while others do not. Take your time; now it is just a point of curiosity, not keeping me from (slowly) developing the app

[jimatqsi]

I use listbox every day, or nearly so, but I don't work much with the combobox object.

I could not solve the problem in the database I received from the original poster. I created a new database and imported the forms, tables and macro from the non-working database.

Then I added this code to the GotFocus and OnChange events of the one of the comboboxes

Me.cbo_Find_ID_Driver.Dropdown

That change works fine in my new database. It did not help in the original database. It could be something is corrupted there. I tried running the form without going through the menu, and also without using the Autoexec macro, but nothing helped.

It remains a mystery.

[NeoPa]

A few of points :

1. Although using a PM was not something the OP (Original Poster - That's you in this case Dick.) could know was a problem, this is only allowed for technical questions where privacy is to be protected. Passing of databases are included in this exception. All explanations must be included in the thread, because otherwise the thread can prove useless & impossible to understand for other parties. Feel free to refer to a passed database in the main thread so other readers at least know they are missing a part of the puzzle.
2. Databases can be passed into the thread by attaching them as a ZIPped file.
3. Apologies again for my absence. Jim kindly included me in his latest PM so I know the work he's done & suspect any work I were to do would reach a similar conclusion. Unless you particularly want me to repeat it I won't at this stage.
4. I'm going to go ahead & mark Jim's latest post as Best Answer now as it seems to fit the bill.
5. Like Jim, I suspect this only went wrong due to an all-too-frequent corruption of the database file. These are a pain, but common enough to make them a consideration for all complicated issues. A by-product of this conclusion is that it indicates the OP got nothing really wrong in the first place.

[NeoPa]

NeoPa:
4. I'm going to go ahead & mark Jim's latest post as Best Answer now as it seems to fit the bill.

Ooops. It appears that section of the site has been removed silently. Well, at least we know it was Jim who provided the help here :-)

[W4NMP3]

Thank you very much for digging into my problem and how it works for you

[W4NMP3]

I meant to say thank you both