473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Updating a Record in a Table from a Form

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, 640 views)
File Type: jpg DMList.jpg (17.5 KB, 801 views)
File Type: jpg DMListTbl.jpg (10.2 KB, 285 views)
File Type: jpg Error.jpg (17.6 KB, 260 views)
Mar 13 '18 #1
3 22481
gnawoncents
214 100+
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
3,653 Expert Mod 2GB
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
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

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

Similar topics

2
by: Antonio Delgado | last post by:
I was wondering if anybody could help me out a little. My goal is to update some information about a system every 2-5 seconds. The information needs to be displayed in some sort of table, but the...
9
by: David Allison | last post by:
cmdButton to Print 3 copies of record in Form view ? cmdButton will print 1 but I need 3 copies of the one Form record. -- Dave Allison
0
by: loloxi | last post by:
please i nedd help about updating a table from a form, i hvae a textbox which when it gets an input and saved, i want it to replace the corrseponding field in the table. thanks - hope this makes...
8
by: Ragbrai | last post by:
Howdy all, I have a form for entering data (Form A), on which is a cmd button that opens Form B in acdialog mode to allow me to enter/edit records that are being displayed on Form A. After...
2
by: Tom Clavel | last post by:
Scratching my head. I am using the filter property on a combo box .AfterUpdate event to get to a single client record. This is causing a some strange behavior: As I enter a subform, I get a no...
1
by: rickair7777 | last post by:
Hi, I'm an ex-Notes guy, doing an Access 2007 project. I'm a little weak on Access syntax and functionality. It's not a customer database, but it will be easier to describe it in those terms......
4
by: prosad | last post by:
hello, Just solved a problem using Javascript onclick, can click on any cell in a dynamic table and it will pass the innerText object value to my form text field. parts of code given below: ...
10
by: help4me | last post by:
I am having trouble updating a table. The logic seems so simple but I just can’t get it to work. The table is named test. The column names are passcode, name, address, city, state, zip and email....
2
tdw
by: tdw | last post by:
Hi all, I have several ideas on how to do this, but am having difficulty putting the ideas together and figuring out the most efficient way to do this. I have a database of survey coordinate...
15
by: vishal prada | last post by:
<html> <body> <table width="50%" border="1" rules=ALL> <tr><th> Username</th><th>Access Level</th><th>Delete</th></tr> <%@ page language="java" import="java.sql.*" %> ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.