ok, i'm no longer getting the write conflict error but it's not changing the status to closed.
here's the updated 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
rstFile.Open "dbo_tbl_Files", 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
