423,321 Members | 1,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,321 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:
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:

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")
  7.  
  8. With DMListTbl
  9. .Edit
  10.  
  11. DMListTbl!Code = Forms!DMList!txtCode
  12. DMListTbl!DM = Forms!DMList!txtDM
  13. DMListTbl!Status = Forms!DMList!txtStatus
  14.  
  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]"
  17.  
  18. End With
  19.  
  20. If Me.Dirty Then Me.Dirty = False
  21.  
  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!!

Best,

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


gnawoncents
100+
P: 212
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strSQL As String
  3.  
  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]"
  6.  
  7. If Me.Dirty Then Me.Dirty = False
  8.  
  9. DoCmd.RunSQL (strSQL)
  10. Me.Requery
  11. Me.SPList.Requery
  12. MsgBox ("Updated")
  13.  
  14.  
Note: I updated your "DMListTbl![code]" to "DMListTbl![codeX]" because it was messing with my own code tags.
Mar 14 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 2,536
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
  6.  
  7.     strSQL = "SELECT * FROM DMListTbl WHERE ID = " & _
  8.         Forms!DMList![txtID] & ";"
  9.     Set db = CurrentDb
  10.     Set DMListTbl = db.OpenRecordset(strSQL)
  11.  
  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
  20.  
  21.     db.Close
  22.     Set DMListTbl = Nothing
  23.     Set db = Nothing
  24.  
  25.     MsgBox "Updated"
  26.  
  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.