Combo box does not retain selected values

[monica_123]

Hey guys,

We have recently migrated the access database to sql server. Frontend is still Access.   Since the migration , few of the combo boxes in some forms are not retaining the selected values. The selected value disappears after a macro in After update event is executed. the data is coming from a query in Row source and all the values in Column widths and Column count are set properly. Any ideas are welcome!

Thanks,

Monica.

[#2]

@monica_123 We'd have to see what those macros are actually doing....

One thing that "might" be involved is the difference in timing of record creation between Access and SQL Server. 

In Access, a new AutoNumber value is generated (and therefore available) as soon as you START a new record, whether or not you go on to save that value.

In SQL Server, a new Identify value is generated WHEN the record is committed to the table and it is, therefore, only available if the record has been committed.

So, if that macro code is based on the assumption that a new value will be available based on the the behavior of Access, that might be the problem. Of course, there could be other reasons, but that one comes to mind as a frequently encountered issue when upgrading to SQL Server.

[#3]

@George Hepworth  Thank you for the reply. Macros used in after update event are GoToControl and FindRecord. There are no new records created, we are just retrieving existing record based on the value selected from the combo box. The data is retrieved properly , but the value selected in the combo box disappears after the update event. We would like to retain it.

[#4]

Again, it would be nice to SEE what the macro is actually doing. Sometimes it's hard to picture that accurately from an overview explanation.

[#5]

@George Hepworth would a screenshot help ?

[#6]

@monica_123I don't see anything in that macro that requeries the combo box. Is there another place which does that? Maybe the current event of the form itself?

[#7]

@George Hepworth  I´m not sure if this is what you are looking for , but the Substratauswahl in the Find What field is the name of the combo box.

[#8]

Hello Monica,

We were experiencing similar symptoms although we are not using macros, but VBA. We solved it with a combination of fixes. The first was eliminating any entry that started with an apostrophe (we only had one). The second was checking the Auto-Correct settings. I believe we turned this function off. The Auto-Correct seemed to be resetting the combo boxes so that the previously entered data was not being retained. 

Let us know of any progress.

Thank you,

Albert_SD

[#9]

@monica_123 No, I assumed that was the name of a control.

What I'm thinking is that there is another event that is requerying or otherwise resetting the value of the selected row in the combo box. 

[#10]

@Albert_SD That's interesting. I can't see how either of those two would matter, but Access is sometimes a bit mysterious unless we can actually see the entire accdb and the code (macros and VBA) in it.

Having a leading apostrophe in the value of a row might confuse Access into interpreting as a wild card delimiter, but it shouldn't CLEAR the value on a requery. 

Autocorrect is entirely different. It's going to change misspellings, perhaps. Again, it's hard to see how that is related to this particular problem. Combo boxes are designed to work with closed domains of values, so misspellings would simply be rejected, I would expect, not changed. 

[#11]

@George Hepworth 

Hello George, 

I should have mentioned that we use Access as the front-end to an SQLServer database. So that might have been the problem with the apostrophe. We have an inventory form with a separate search form. The search form has 32 fields/parameters, so a bit much to post. But I just went back and added a name that started with a apostrophe, and it did not retain the selection. Yes, kind of strange.

I also went in and restored auto-correct, and it didn't have any effect. So, you might be right about that one.

Also, Monica might want to check that the bound column of the combobox is a identity field. I think if there are duplications, that might also be an issue. But, we have some duplicate names in one bound field and they don't seem to be a problem.

Thank you & take care,

Albert