"The data has been changed" error when editing underlying record in Access VBA

Question

I have a form in Access where I have 2 unbound multi-select listboxes, with some code to move items between them.

Each of the fields in the table which are shown in the listboxes are boolean values - if the value is true then the name of that field shows up in lstSelected, and if false shows up in lstUnselected.

The listboxes have a RowSourceType of Value List, and the value list is generated programatically by looking at the underlying record and constructing a string with the field names where the boolean values are true for lstSelected and False for lstUnselected.

On the form I have two buttons, cmdMoveToSelected and cmdMoveToUnselected. When I click on cmdMoveToSelected it changes the boolean value of the underlying field for any selected items in the lstUnselected listbox from false to true by executing an SQL string, then rebuilds the value lists for both of the listboxes.

I have all of this working just fine. If I do a me.lstUnwanted.requery and a me.lstwanted.requery then everything moves and shows up correctly, and the underlying fields are edited correctly, BUT when I click on anything else on the form I get the error:

The data has been changed.

Another user edited this record and saved the changes before you attempted to save your changes.

Re-edit the record.

Now I've found a way around this (jobDetailsID is the primary key of the record being dealt with):

    Dim intCurID as Integer

    intCurID = Me.JobDetailsID

    Me.Form.Requery

    Me.Recordset.FindFirst "JobDetailsID = " & curID

This requeries the form and then moves back to the current record, and this gets rid of the error, however it causes there to be a delay and the form to flicker while it opens back at the first record, changes back to the correct record and repopulates the list boxes.

Is there a way to do away with this error, or get it to trigger programmatically so I can catch it by turning the warnings off via vba?

Thanks in advance.

Answer 1

I've had this sort of thing happen when I've left the form RowSource query hanging in place after converting the controls to unbound textboxes, etc. The general Form rowsource query (to bring in all fields I might possibly end up using) provides me with a query-list identical to the table fieldnames, making it simple to select them for control-names as needed. Works fine, but you have to remove the form rowsource query after all the names are matched-up. (After which DLookup and BeforeUpdate works for getting and storing values and changes.)

Answer 2

I seem to have fixed it, though the fix doesn't make a great deal of sense to me.

I added in a Me.Refresh to the button click code, after I had requeried the two listboxes and it appears to have stopped the message from coming up. However this only works when I have the JobDetailsID textbox visible on the form (though I expect this is arbitrary and any field-linked textbox would work).

Can anybody explain to me why this works? I'd like to understand fully when to use requery, refresh etc

Answer 3

Probably the best solution would be to not directly update the current record in the table while the Form is dirty. Instead, update the values of the fields within the form itself (Me!FieldName) as the items are moved from one List Box to the other, and let the form write those values back to the table as usual.

Answer 4

Maybe it helps not to bind the form to the table being altered by cmdMoveToSelected, but to a query that doesn't contain all the boolean fields. If cmdMoveToSelected alters one or more boolean fields, the record is changed, but the query result isn't. Not sure if it's sound though.

It also sounds a bit like a design problem rather than a form problem, storing options in boolean fields instead of into a related table.