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
3 22481
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.
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.
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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
|
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...
|
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...
|
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...
|
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......
|
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:
...
|
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....
|
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...
|
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.*" %>
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |