How to update a record?

[mrsgwen90]

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

[Stewart Ross]

Is either form bound to the recordset you are updating? If so the error message could well be indicating that you need to save any changes made in the bound version of the recordset before you make changes using the ADO recordset in code.

There are also known issues with accessing SQL Server data via Access which cause the same error message (see for example this MS knowledgbase entry).

If neither of these circumstances apply, could you set a breakpoint and step through the code one line at a time to see where the error is occurring? There is no obvious problem with your recordset update.

(As an aside, I note that there is an unnecessary DoCmd.Save at line 69 which would save the form, not the current record!)

-Stewart

[NeoPa]

If both forms are interacting with the Files table, then they will clash with each other (unless you somehow ensure only one is ever active at a single time).

My guess is that you should have a single form that manages the data in the table, and not two that can interfere with each other.

Good luck.