trying to update a record

[mrsgwen]

i have an access database with some vba code behind it. i have a table called "Files" that holds some records and i have 2 forms. one form opens a new file and the other form closes the file. When i close the file the record should be updated with a "closed" status. But the problem is i keep getting the error: "Write Conflict - this record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made." then it gives the option to copy to clipboard or drop changes. Any help will be greatly appreciated.
Here is my code:

Private Sub cmdCloseFile_Click()
On Error GoTo Err_cmdCloseFile_Click

Dim Result
Dim rst As ADODB.Recordset
Dim adocmd As ADODB.Command

Set adocmd = New ADODB.Command
Set rst = New ADODB.Recordset
With adocmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "Select * from dbo_tbl_Files where Status = 'closed' and FileNo = " & Me.FileNo
End With

rst.CursorLocation = adUseClient
rst.Open adocmd, , adOpenStatic, adLockReadOnly
If rst.RecordCount > 0 Then
MsgBox "This File has already been closed.", vbCritical, "FileNo Confirmation"


Result = vbNo
DoCmd.Close
DoCmd.OpenForm "Legal Files Main"
Else
Result = MsgBox("Are you sure you want to close File No. " + Str$(Me.FileNo) + "?", vbYesNo, "Add File")
If Result = VbMsgBoxResult.vbNo Then
Me.Room = ""
Me.BoxNo = ""
Me.DateClosed = ""


DoCmd.Close
DoCmd.OpenForm "Legal Files Main"

End If
End If


rst.Close
Set rst = Nothing
Set adocmd = Nothing

If Result = vbNo Then
Exit Sub
End If


Dim rstFile As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String
Dim sqlstmt As String

sqlstmt = "SELECT * FROM dbo_tbl_Files WHERE FileNo=" & Me.FileNo

rstFile.Open sqlstmt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

rstFile!Status = "Closed"

rstFile.Update
rstFile.Close


MsgBox "File Successfully Closed."

Msg = "Do you want to close another file?"

Response = MsgBox(Msg, vbYesNo)
If Response = vbYes Then

DoCmd.Save
DoCmd.Close
DoCmd.OpenForm "Close File"
Else
DoCmd.Close
DoCmd.OpenForm "Legal Files Main"
End If

Exit_cmdCloseFile_Click:
Exit Sub

Err_cmdCloseFile_Click:
MsgBox Err.Description
Resume Exit_cmdCloseFile_Click




End Sub

[NoellaG]

HI,

one reason could be that the record is open in the form and is dirty. This way it is been changed in the form (=user 1), and the code (user 2) tries to modify the same record.
Try to save the record in the form (docmd.RunCommand acCmdSaveRecord) before running the status update command.

success
NG