Today I had a weird problem that took me about 2 hours to track down.

I had a normal form in Continuous view that was not triggering an error of any kind, but would not write to the record. It could see all the records, you could navigate to them, and I had a delete routine that would delete the given record, but it could not be updated in any way. It acted as though the form or controls that were bound were in the “Locked” state.

Turns out that in this case, there was a strange anomally that had been saved in the Order By property of the form. It was using several fields that were actually in the form Recordset, but one of the elements of the Order By property was a reference to a combo box on the form: “Lookup_cboBox.Description”.

This reference was not causing any problems in my dev database, but the lookup table that was being used was not indexed properly in the customer database. This was silently putting the form into read only mode.

Weird huh?

So what is this “Lookup_” prefix? Apparently it is used by Access internally in many cases where you are messing with the order by clause in datasheet view or right clicking on a combo box field and choosing a sort order for the form. This setting can then get saved in your order by clause inside the form properties and automatically applied to your form when you re-open it.

It is trying to internally lookup a field that is not in your recordset using the displayed value in your form and ordering by that, like if you have a “States” table that is indexed by State_ID, and State_ID is what your customer form is storing, Access will help you filter or sort your form if you right click in the State combo that binds the State_ID to your record, but looks up the State Name to display to the user. It will then be able to sort by the state name.

If the State_ID is not indexed in the State table however, this will end up putting the form recordset itself into a read only state! And nothing tells you that this has happened.

One way to avoid this is to make sure you reset your Filter and OrderBy properties on the form in the Form_Open event. If you set them to some default your customer wants, or just blank them out, this would prevent the problem from happening at least whenever you reopened the form, however, you may still have the issue after a customer sorts on a combo box.

Another way to take care of this (and which you probably should do anyway) is to index the lookup column bound to the form in the related table. So make sure the State_ID field is indexed. Normally a field like this would already be indexed by default because Access will index table fields with the term ID in them.