Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Unanswered: trying to update a record

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is normal as you try to modify a row that's already being open for modification by the form. There are several solutions to this:
    1. Move the form to a different row before performing the modifications.
    2. Use an unbound form.
    3. Disconnect the form from its data source, perform the modifications then reconnect it.
    4. Perform the modifications through the form itself (or its recordset).
    5. Store the SQL statement(s) that will be necessary to perform the modifications somewhere (array, linked list, file, table, etc.) and have them executed when the form is being closed.

    It's difficult to be more specific without knowing why you chose to work that way.
    Have a nice day!

  3. #3
    Join Date
    Dec 2008
    Posts
    5

    trying to update a record

    I am not an Advanced Access user so I get a little confused when you say I'm trying to modify a row that's already being open for modification by the form. can you tell how to modify my code so that i can just simply update the record.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Shortly explained with other words, the write conflict error you receive means that you create an other process (an ADO recordset) and try to use it to modify the row of a table or query that is currently displyed on the form (i.e. the current record).

    Why don't you simply use at the form module level:
    Code:
    Me!Status = "Closed" ' Change the value
    Me.Dirty = False ' Commit the change
    Have a nice day!

  5. #5
    Join Date
    Dec 2008
    Posts
    5

    trying to update a record

    do you mean on the form's Load event?

  6. #6
    Join Date
    Dec 2008
    Posts
    5

    trying to update record

    thank you so very much, that did the trick. Got it working! I really appreciate you guys that are so knowlegable on the subject matter.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •