Combi Box Auto-Expand Property stopped working

[Paul Nottingham UK]

Hi Guys

I have a form which is used, in Datasheet view, to rapidly enter new records. One of the fields is a Combi Box used to enter an 'Additional Note ' about what the record refers to. The RowSource property is a query which lists all the entries from previous records for the 'Additional Note' field. The idea behind this is so that as an Additional Note is entered on a new record, I can see what has been enetered previously for a particular 'type' of record and enter a new Note accordingly. The AutoExpand property is set to 'Yes' so that as I type in the Additional Note box, any entry in the list which matches what I type will be displayed in the taxt box of the CombiBox. The problem I am having is that the feature has stopped working - if I open the list and type while it is open, records will still appear that match what I type but they are not displayed in the text box. It's a nuisance because it slows down data entry. This issue has arisen since I bought a new lap-top and upgraded from Win 10 to Win 11. The issues is all the more puzzling because other Combi Boxes on the same datasheet continue to work OK.

I have tried various things to correct the problem: deleted the combi box and created a new one - this hasn't worked. I have copied a CombiBox that I know works and changed the settings to those needed for the Additional Note field (essentially the underlying RowSource query). This hasn't resolved the issue.

From what I can see the problem seems to be caused by the number of entries in the list - the table from which the RowSource query for the Additional Note CombiBox is created has more than 20,000 records in it, and this seems to be why it doesn't work (other CombiBoxes have only a couple of dozen list entries). I suspect that when I upgrased to Win 11 the 'new' Windows Registry in the background has been set so that a much lower number of records only can be read in a Combi Box list. The thing is, it worked fine until I upgraded to Win 11 so logically, it's a fixable problem.



Can anyone help please?

Many thanks in advance.

Paul

[CJ_London]

suspect it is a data volume/performance issue - combo's technically can store 65k records, so far as I am aware that has not changed.

What is the sql to your combo rowsource?
Is the underlying field indexed?
Do you have any code attached to the combo control?
'for a particular 'type' of record' implies you are using a cascading combo - so how do you determine which type the additional note field is associated with to display in the combo dropdown

[Micron]

I think you've taken the wrong design approach (which I think is what cj is saying). IMO, to see what comments were previously entered you should be looking at a comments subform.

[CJ_London]

which I think is what cj is saying

Not really - I use what sounds like a similar technique in my accounting software where transaction details can be picked from a list filtered by the account number and sometimes other constraints such as supplier or customer (depends on where being used)

Also should have asked about the datatype - is it short text or long text?

[Paul Nottingham UK]

Hi, thanks for your help

The RowSource SQL is:

"SELECT DISTINCT [T01 - Main].TransactionNotesCashBal FROM [T01 - Main] WHERE ((([T01 - Main].TransactionNotesCashBal) Is Not Null)) ORDER BY [T01 - Main].TransactionNotesCashBal;"

The field in question, [TransactionNotesCashBal] (named ‘Additional Notes’ on the input form) is Short Text data type and is Indexed (Note: when I was using Win10, the field wasn’t indexed, but I changed it thinking it might resolve the problem, but it hasn’t!

There is no code attached to the CombiBox – the only code on the form is a requery of the CombiBox, attached to the OnEntry event of the form.

Maybe ‘for a particular type of record’ was not quite the right description – it is not in any way a cascading CombiBox. It is really quite a simple data entry form (in datasheet view) that I have been using for many years to enter all my day-to day financials – essentially my income, my outgoings and my various bank accounts/investment vehicles.

My own (tentative!) view is that something has changed since my transition from Win10 to Win11 – my Access version hasn’t changed. The database file is stored in OneDrive so an instance of the file would have to be downloaded into memory (I have 16GB RAM); I’m not sure if that would affect performance but it wan’t an issue under Win10. I’m convinced it has something to do with the Registry but I can’t find anything that might help.

[Micron]

as I type in the Additional Note box, any entry in the list which matches what I type will be displayed in the taxt box of the CombiBox

To be clear, you're typing in a textbox and as you do that you're filtering the combo box? A bit confusing to refer to "the text box of the combo box". It's just a combo box.
How many records are returned to the combo? Hate to ask, but when you upgraded to 11, nothing changed regarding Trusted Locations for this db?

[Paul Nottingham UK]

A CombiBox has 2 parts: a list part and a text box part that you type in - that's why it's called a CombiBox: it combines the features of a List Box and a Text Box.

I am typing a Note into the text box of the Combi Box and as I type each character any item in the list that matches what I type should be displayed in the text box portion so that, for example, if an item comes p that is suitable for entry, I just hit <Enter>. The problem is, as I type, any matching items do not display in the text box. I confirm the AutoExpand property is set to 'Yes'. I am trying to find a solution to this error. Nothing has changed since I upgraded from Win10 to Win11. There are upwards of 20,000 items in the list (and getting bigger). BUT, the CombiBox worked fine before but doesn't now. So, why? As I said previously, I suspect there's a Windows Regiatry setting that needs to be changed so that Windows can manage the large number of records (e.g., memory resourcing etc.). So far, I haven't been able to find a solution.

[Micron]

IMO it does not combine any feature from a list box - that is totally different from a combo or a textbox. A combo box is a combo box. It has a list; it does not have a textbox. If a combo box had a textbox, that portion would have all the properties and/or methods peculiar to a textbox, but it does not.
We can agree to disagree. Unfortunately, I can't think of any reason related to Windows upgrade that would cause your issue. I'd create a new form, add a combo (do not copy/paste from the existing form), set the same properties - including the same rowsource and see what happens. Maybe your old control is corrupted.

[CJ_London]

The database file is stored in OneDrive

Onedrive is not a good place to store your file. As a sole user you may be OK, but I wouldn't guarantee it. It is quite possible it has introduced a small corruption causing your problem.

Suggest try creating a new db on your local machine, not onedrive, link to your data (located on your machine, not one drive) and create a new, simple form with just the basics to get your combo to work - if that works as before then that is a strong indication of corruption. And please don't say 'but it's worked for years'. I have a client who for several years had a setup similar to yours until one day it stopped working. I was called in to sort it out. They had no real backup, lost about 3 months of data and took several weeks to get it back up and running.

so further questions:
is the application split into BE and FE?
and if so are both files on Onedrive or just the BE?
is your form recordsource a table or a query - if a query what is the sql?

the only code on the form is a requery of the CombiBox, attached to the OnEntry event of the form.

surely that should be the on exit event of the combo? Otherwise new entries will not appear in the combo list for subsequent records.

[Paul Nottingham UK]

Good evening

Thanks for further advice.

My DB is a single file, no front & back end. The form is created from a single table not a query.

I note your concerns about OneDrive and have moved the file to the local machine.

I have also tried the suggestions - FE and BE, a simnple form with ComboBox (properties set exactly as beofre), but the problem persists.

While messing with the new for and combo box, I have noticed that if I typr 'A' then the first entry in the lost beginning with 'A' will display in the text box part and will continue to retrieve the next list entry according to what I type; however, when I start with 'B', again, the first entry in the list beginning with 'B' will display but none after that.

I'm stumped.If you have any other things I can try I would appreciate it.

Thanks

Paul