Solved Combo Box Issue Selecting Item versus Typing Item (1 Viewer)

[spaLOGICng]

Hey Folks,

I am a seasoned Access Developer, and I ran into a situation that I have never encountered before.

I have an Access App that is connected to a relatively small SQL Server Database hosted by [blank] in the cloud.

I have a View that is linked to the DB with the respective Columns for the Combo Box. The Form is fairly complex and uses the Navigation Control to display several screens as subforms. The Combo Box is on the Parent Form.

The Combo Box works great when I scroll down the list of Items and make a selection with my Down Arrow + Enter or with my Mouse. The Navigation Control and Target Subforms work fantastic.

The issue occurs if I decide to type the Value to auto-populate the value I need to search for. When I go this route the Screen freezes (not responding).

I have tested it with local data populating the Combo Box and the behavior is the same.

Any insight would be greatly appreciated. Thanks!

 

[theDBguy]

Just a quick test, can you create a new combo and test that?

 

[Josef P.]

I have tested it with local data populating the Combo Box and the behavior is the same.

This excludes the cause cloud server. (For cloud server I would have recommended a disconnected ADODB record set for testing).
Since the problem also occurs with a local table, can you show an example db?

But there is no code running as a reaction from Combobox.Change or similar?

 

[GPGeorge]

This excludes the cause cloud server. (For cloud server I would have recommended a disconnected ADODB record set for testing).
Since the problem also occurs with a local table, can you show an example db?

But there is no code running as a reaction from Combobox.Change or similar?

I would definitely suspect some code running on the Change event of the combo box first. Once that is confirmed or eliminated as a possible source of the delay, you can look at other factors in the form, such as one or more subforms being impacted, i.e. "The Form is fairly complex and uses the Navigation Control to display several screens as subforms."

Try running this combo box on a simple form with no other controls on it.

 

[spaLOGICng]

This excludes the cause cloud server. (For cloud server I would have recommended a disconnected ADODB record set for testing).
Since the problem also occurs with a local table, can you show an example db?

But there is no code running as a reaction from Combobox.Change or similar?

I have an NDA on the DB so I cannot show it in any fashion.

There is no code other than the After_Update Event that sets the Global Variables for the Filters on the Subform that is in focus on the Navigation Control. The Parent Form is not bound to anything record source. It only acts as a Dashboard for Searching. The Subforms are very responsive.

The issue is specific to the Combo Box, i.e. Selecting a Value with the Mouse versus Typing a Value.

 

[spaLOGICng]

Just a quick test, can you create a new combo and test that?

I have not done this. I will try it.

 

[spaLOGICng]

I would definitely suspect some code running on the Change event of the combo box first. Once that is confirmed or eliminated as a possible source of the delay, you can look at other factors in the form, such as one or more subforms being impacted, i.e. "The Form is fairly complex and uses the Navigation Control to display several screens as subforms."

Try running this combo box on a simple form with no other controls on it.

There is no code running other than setting the global variables on After Update.

As I mentioned, the Combo Box performs perfectly when scrolling down through the list with either the arrow button or mouse and pressing enter. It only occurs when the value is typed into the combo box.

There are no other events triggered except for the after update.

 

[spaLOGICng]

I will add that the Row Source is a Query, not a SELECT Statement.

The Recordset Type Property is set to Snapshot.

The Combo Box is Limited to List.

 

[June7]

Well, can you replicate this issue in another db that does not use your NDA db naming convention and data? If so, then could you provide that one?

 

[spaLOGICng]

I would definitely suspect some code running on the Change event of the combo box first. Once that is confirmed or eliminated as a possible source of the delay, you can look at other factors in the form, such as one or more subforms being impacted, i.e. "The Form is fairly complex and uses the Navigation Control to display several screens as subforms."

Try running this combo box on a simple form with no other controls on it.

I altered the Dashboard to have a single tab in the Navigation Control. I removed all combo boxes on the subform (x3) just to render data. I also limited the data to 10 records on the SQL Source Table.

The behavior is just the same.

 

[spaLOGICng]

Well, can you replicate this issue in another db that does not use your NDA db naming convention and data? If so, then could you provide that one?

I use the same methodology in all of my applications. I have apps connected to Azure SQL DB with various DTU levels, as well as AWS, SQL Server over the Internet running on Dell PowerEdge Servers behind a variety of VPN's and have never run into this issue before.

I did not create this database. I am building off an existing DB and Access Application. I have ruled out the VPN.

I am on a Dell XPS 8950 32g Ram, 12th gen i7, 1tb SSD. I have 500mbps download speed on internet. Windows 11 Pro, 64-bit Office. No issue with resources.

I do use Access Theming and Quick style. I have turned off Track Name Auto Information, disabled Layout View, Enabled BigInt, turned off Caching. Anything that I can think of.

I personally feel that the issue is with the Indexing. I have created an Index and included the relevant Fields pertaining to Lookup. I have tried any number of indexing options.

I have run the VIEW through the Tuning Advisor and there are no recommendations with that or the Execution Plan. This is when I decided to test the Combo Box with Local Data and the behavior was the same. This is why I lean on the combo box itself as the issue.

It is just baffling.

 

[moke123]

This is why I lean on the combo box itself as the issue.

Did you replace it as suggested?

 

[spaLOGICng]

What a morning!


Here are my notes from this morning. It was painful, but it is solved.

I think the conclusion is that there must have been some corruption associated with the original Screen, and maybe one of the Sub forms in the Navigation Control (NC). I did not work with any of the sub forms to solve this conundrum. The Original Screen used the Tab Control which I replaced with the NC.

The highlights are for the client.

 

[spaLOGICng]

Did you replace it as suggested?

Please see my final last comment.

 

[arnelgp]

Please see my final last comment.

..that will be US$400 (4 hrs x US$100 per hour)