Updating a Record in a Table from a Form

[Eman1234]

Hello all,

The noob is back again. I've spent all day trying to figure out how to code an edit button to update a record in a table. I have made progress, but now I am getting some kind of "locking violation". When I tell the query to run despite the locking violation, it doesn't update the record, it just adds the record onto the end. Also, neither the table or the list box I refer to in the code are requerying.

The table I'm working with is: "DMListTbl"
The fields are:
Code
DM
Status
ID (This is an autonumber)

The form I'm working with is: "DMList"
The controls are:
txtCode
txtDM
txtStatus
txtID


I'm not sure if it is relevant, but I have a list box on the form called "SPList", which is pulling data from "DMListTbl" . In the event property of the list box, when a record is double-clicked the data is moved into the controls.

This is the code:

Public Sub Command28_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim DMListTbl As DAO.Recordset
Set db = CurrentDb
Set DMListTbl = db.OpenRecordset("DMListTbl")

With DMListTbl
.Edit

DMListTbl!Code = Forms!DMList!txtCode
DMListTbl!DM = Forms!DMList!txtDM
DMListTbl!Status = Forms!DMList!txtStatus

strSQL = "UPDATE DMListTbl SET DMListTbl!Code = Forms!DMList![txtCode], DMListTbl![DM] = Forms!DMList![txtDM] ,DMListTbl![Status] = Forms!DMList![txtStatus]" _
& "WHERE DMListTbl![ID] = Forms!DMList![txtID]"

End With

If Me.Dirty Then Me.Dirty = False

DoCmd.RunSQL (strSQL)
Me.Requery
Me.SPList.Requery
MsgBox ("Updated")
End Sub

This has been driving me insane, so thank you in advance for any help on this issue!!

Best,

Eric

[gnawoncents]

Eric,

First, remember to put your code in tags.

In regards to your locking issue, it's not something your code is missing, rather, it's something extra it has. There are a number of ways to update a record. The simplest is to display the actual record in the form, then it automatically updates as changes are made. I will assume that this doesn't work for you for some reason, which is why you didn't take that approach.

Alternatively, you could update using a recordset or an SQL statement. In your case, you're trying to intermingle both. Remove the recordset and you'll be left with the code below, which should work.

Dim strSQL As String

strSQL = "UPDATE DMListTbl SET DMListTbl![codeX] = Forms!DMList![txtCode], DMListTbl![DM] = Forms!DMList![txtDM] ,DMListTbl![Status] = Forms!DMList![txtStatus]" _
& "WHERE DMListTbl![ID] = Forms!DMList![txtID]"

If Me.Dirty Then Me.Dirty = False

DoCmd.RunSQL (strSQL)
Me.Requery
Me.SPList.Requery
MsgBox ("Updated")

Note: I updated your "DMListTbl![code]" to "DMListTbl![codeX]" because it was messing with my own code tags.

[twinnyfo]

Just as a side note, although gnawoncents's reply is correct and requires fewer lines of code, the original code would be "better" if run as a true recordset, as this is a more direct interface with your table. All I want to demonstrate is that there are several ways to skin any cat, and sometimes, an update query will not always work as expected. See below for an alternate:

Public Sub Command28_Click()
On Error GoTo EH
    Dim strSQL      As String
    Dim db          As DAO.Database
    Dim DMListTbl   As DAO.Recordset

    strSQL = "SELECT * FROM DMListTbl WHERE ID = " & _
        Forms!DMList![txtID] & ";"
    Set db = CurrentDb
    Set DMListTbl = db.OpenRecordset(strSQL)
 
    With DMListTbl
        .Edit
        !Code = Forms!DMList!txtCode
        !DM = Forms!DMList!txtDM
        !Status = Forms!DMList!txtStatus
        .Update
        .Close
    End With

    db.Close
    Set DMListTbl = Nothing
    Set db = Nothing

    MsgBox "Updated"

    Exit Sub
EH:
    MsgBox "There was an error updating the record!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Keep in mind that the original code would have always updated the first record in your recordset. I've modified the strSQL variable to account for that.

Additionally, if this VBA is on the Form DMList, there is no need to refer to it explicitly. You can simply use Me (for example Me.txtCode, etc.).

I've also added Error Handling (which is a good practice for all DBAs to get in the habit of doing), and fixed your MsgBox line, as this is the proper syntax.

Hope this hepps.

[Eman1234]

Thank both of y'all. I tried both codes and they work great. As someone who had no prior experience with coding as of 2 months ago, I really admire how skilled both of you are with this. Again, I really appreciate both of you taking the time to help me with this!