Single-User Record Locking?

[CharlesHerrick]

I'm getting random warning messages in a single-user Access 2000 application involving a multiselect list box (lstRegion), where the user can select more than one item from the list (thanks to MikeAngelastro for the suggesion). I need to save the user selections in a table, to be used in another part of the application that will be run several days later. The table gets updated okay in spite of the warnings.

The message reads: "The data has changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record."

I can click the various selections several times with no problem, then the error pops up seemingly at random. I've tried setting the form's Record Locks property to both No Locks and Edited Record.

Here's the code:

Dim rs As Recordset, ctl As Control, I As Integer, db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblRegion", dbOpenDynaset)
Set ctl = Me!lstRegion

rs.MoveFirst
For I = 0 To ctl.ListCount - 1
rs.Edit
If ctl.Selected(I) Then
rs!blnStatePick = True
Else
rs!blnStatePick = False
End If
rs.Update
rs.MoveNext
Next I

I've also tried wrapping an Edit/Update statement pair around each field change, like this:

rs.Edit
rs!blnStatePick = True
rs.Update).

Does anyone have any ideas on this? Thanks.

 

[Fornatian]

I don't know if this will help, I doubt it but you can replace all this:

If ctl.Selected(I) Then
rs!blnStatePick = True
Else
rs!blnStatePick = False
End If


with

rs!blnStatePick = ctl.Selected(i)

because ctl.Selected(i) will return TRUE or FALSE anyway.

Does your code fail on any specified selection, for instance if you pick more than n rows? Or the first row?

Also, do you close the recordset and set it nothing at the end of the procedure as this can cause conflicts.

rs.Close
Set rs = Nothing

 

[CharlesHerrick]

Thanks, Ian, I'll try your rs!blnStatePick = ctl.Selected(i) shortcut.

I am using rs.Close, but not the Set rs = Nothing (I'll add that).

There are currently four items in the list. I can select all four and maybe deselect one or two before the error. But sometimes it happens after just a couple of clicks; just seems very random.

--Charles

 

[CharlesHerrick]

Eureka!

I solved my problem!

Nothing I tried had worked, so I decided to zoom out from the "trees" in the code and look at the "forest" of form events. That's when I realized I was calling the code from the On Click event of the list box.

I moved the code to the On Click event of a button that starts the next phase of the application and ... it worked like a charm!

I'm sure MS would have an explanation for what happened with the original code placement but I'm ready to move on, older and wiser.

Thanks to Ian for his suggestion on replacing my If loop.

--Charles