Form not allowing updates

M

Mikel

I inherited an Access database that updates a SQL Server 2000 backend. It
worked in Access 97, but does not work when I try to change a record. I get
"ODBC -- Cannot lock all records", or "The MS Jet database stopped the
process because you and another user are attempting to change the same data
at the same time."

The form has a subform that shows the record I want to update. The top form
is for navigation.

There is a save button with this code:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

'DoCmd.RunCommand acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunCommand acCmdSaveRecord
AddOfficeRecords
cmdEdit.Enabled = False
FName.SetFocus
'cmdCancel.Enabled = False
'cmdSave.Enabled = False
'Me.AllowEdits = False

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

It calls this routine:

Private Sub AddOfficeRecords()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qDelOfficeMarket_Contact"
Set rsO_M = CurrentDb.OpenRecordset("Office_Market", dbOpenDynaset,
dbOptimistic)

'This would have been a nice place for an array.. Oh well
If CheckPet Or Check131 Then AddOfficeRecord 1, ContactID
If CheckWA Or Check131 Then AddOfficeRecord 2, ContactID
If CheckLA Or Check131 Then AddOfficeRecord 3, ContactID
If CheckSac Or Check131 Then AddOfficeRecord 4, ContactID
If CheckEB Or Check131 Then AddOfficeRecord 5, ContactID
If CheckAz Or Check131 Then AddOfficeRecord 6, ContactID
If CheckCorp Or Check131 Then AddOfficeRecord 7, ContactID
If CheckV Or Check131 Then AddOfficeRecord 8, ContactID

rsO_M.Close
DoCmd.SetWarnings True

End Sub

Adding records still works and uses this code:

Private Sub AddOfficeRecord(OfficeVal As Integer, MarketVal As Integer)
rsO_M.AddNew
rsO_M.Fields("marketid") = MarketVal
rsO_M.Fields("officeid") = OfficeVal
rsO_M.Update

End Sub

Since I didn't write the code, I am not sure what I need to do to make it
woirk in Access 2003. Any clues?
 
Top