By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Updating a Record in a Table from a Form

P: 12
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:
ID (This is an autonumber)

The form I'm working with is: "DMList"
The controls are:

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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub Command28_Click()
  2. Dim strSQL As String
  3. Dim db As DAO.Database
  4. Dim DMListTbl As DAO.Recordset
  5. Set db = CurrentDb
  6. Set DMListTbl = db.OpenRecordset("DMListTbl")
  8. With DMListTbl
  9. .Edit
  11. DMListTbl!Code = Forms!DMList!txtCode
  12. DMListTbl!DM = Forms!DMList!txtDM
  13. DMListTbl!Status = Forms!DMList!txtStatus
  15. strSQL = "UPDATE DMListTbl SET DMListTbl!Code = Forms!DMList![txtCode], DMListTbl![DM] = Forms!DMList![txtDM] ,DMListTbl![Status] = Forms!DMList![txtStatus]" _
  16. & "WHERE DMListTbl![ID] = Forms!DMList![txtID]"
  18. End With
  20. If Me.Dirty Then Me.Dirty = False
  22. DoCmd.RunSQL (strSQL)
  23. Me.Requery
  24. Me.SPList.Requery
  25. MsgBox ("Updated")
  26. End Sub

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


Attached Images
File Type: jpg Code.jpg (21.7 KB, 241 views)
File Type: jpg DMList.jpg (17.5 KB, 157 views)
File Type: jpg DMListTbl.jpg (10.2 KB, 103 views)
File Type: jpg Error.jpg (17.6 KB, 89 views)
Mar 13 '18 #1
Share this Question
Share on Google+
3 Replies

P: 213

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.

Expand|Select|Wrap|Line Numbers
  2. Dim strSQL As String
  4. strSQL = "UPDATE DMListTbl SET DMListTbl![codeX] = Forms!DMList![txtCode], DMListTbl![DM] = Forms!DMList![txtDM] ,DMListTbl![Status] = Forms!DMList![txtStatus]" _
  5. & "WHERE DMListTbl![ID] = Forms!DMList![txtID]"
  7. If Me.Dirty Then Me.Dirty = False
  9. DoCmd.RunSQL (strSQL)
  10. Me.Requery
  11. Me.SPList.Requery
  12. MsgBox ("Updated")
Note: I updated your "DMListTbl![code]" to "DMListTbl![codeX]" because it was messing with my own code tags.
Mar 14 '18 #2

Expert Mod 2.5K+
P: 3,204
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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub Command28_Click()
  2. On Error GoTo EH
  3.     Dim strSQL      As String
  4.     Dim db          As DAO.Database
  5.     Dim DMListTbl   As DAO.Recordset
  7.     strSQL = "SELECT * FROM DMListTbl WHERE ID = " & _
  8.         Forms!DMList![txtID] & ";"
  9.     Set db = CurrentDb
  10.     Set DMListTbl = db.OpenRecordset(strSQL)
  12.     With DMListTbl
  13.         .Edit
  14.         !Code = Forms!DMList!txtCode
  15.         !DM = Forms!DMList!txtDM
  16.         !Status = Forms!DMList!txtStatus
  17.         .Update
  18.         .Close
  19.     End With
  21.     db.Close
  22.     Set DMListTbl = Nothing
  23.     Set db = Nothing
  25.     MsgBox "Updated"
  27.     Exit Sub
  28. EH:
  29.     MsgBox "There was an error updating the record!  " & _
  30.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  31.     Exit Sub
  32. 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.
Mar 14 '18 #3

P: 12
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!
Mar 14 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.