Need a little assistance with a VBA code for updating tables through a form. I have a form with two text boxes and a command button. One text box is named txtNewPartNumbe r and the other text box is named txtOldPartNumbe r. The idea is for the user to type in the old part number in the txtOldPartNumbe r box and type what the new part number is changing to into the txtNewPartNumbe r box then click on the command button to change the part numbers in 3 different tables. When I set up the VBA code to change the part number in one table, it works just fine, but when I try to and another table, I get the error message shown below. What am I missing in the SQL statement?
Here is the code I am using to update the tables
Private Sub cmdUpdate_Click ()
Dim mySQL As String
mySQL = "UPDATE tblPreventiveTo olDamageinfo, tblPMTrial"
mySQL = mySQL + " SET tblPMTrial.Part Number = '" & Me.txtNewPartNu mber & "' tblPreventiveTo olDamageinfo.Pa rtNumber = '" & Me.txtNewPartNu mber & "'"
mySQL = mySQL + " WHERE tblPMTrial.Part Number = '" & Me.txtOldPartNu mber & "' AND tblPreventiveTo olDamageinfo.Pa rtNumber = '" & Me.txtOldPartNu mber & "'"
DoCmd.RunSQL mySQL
cmbPartNumber.R equery
End Sub
Thanks for your help
9 1656
I'm not a vastly experienced Access programmer, so when I read this I was really curious. Can you really update 3 tables at one time? I would have just run 3 separate update queries: mySQL = "UPDATE tblPreventiveTo olDamageinfo SET tblPreventiveTo olDamageinfo.Pa rtNumber = '" & Me.txtNewPartNu mber & " WHERE tblPreventiveTo olDamageinfo.Pa rtNumber = '" & Me.txtOldPartNu mber & "'"
DoCmd.RunSQL mySQL
mySQL = "UPDATE tblPMTrial SET tblPMTrial.Part Number = '" & Me.txtNewPartNu mber & " WHERE tblPMTrial.Part Number = '" & Me.txtOldPartNu mber & "'"
DoCmd.RunSQL mySQL
mySQL = "UPDATE tblTable3 SET tblTable3.PartN umber = '" & Me.txtNewPartNu mber & " WHERE tblTable3.PartN umber = '" & Me.txtOldPartNu mber & "'"
DoCmd.RunSQL mySQL Not a very elegant solution, granted, but I'm not yet a very elegant programmer. =)
I am pretty sure that you can update more then one table at once, but it takes knowing what the right code would be, which is where I am stuck at..lol. If I can update 3 tables at once using an update query, I would think I could do the same in VBE with the right code using an Click event on a commond button.
This got me curious and I just couldn't leave it alone. I read everything I could about updating multiple tables and I think I see a problem. Don't you need to separate the SET commands? Try this:
mySQL = "UPDATE tblPreventiveTo olDamageinfo, tblPMTrial"
mySQL = mySQL + " SET tblPMTrial.Part Number = '" & Me.txtNewPartNu mber & "', tblPreventiveTo olDamageinfo.Pa rtNumber = '" & Me.txtNewPartNu mber & "'"
mySQL = mySQL + " WHERE tblPMTrial.Part Number = '" & Me.txtOldPartNu mber & "' AND tblPreventiveTo olDamageinfo.Pa rtNumber = '" & Me.txtOldPartNu mber & "'"
Hope this works!
Thought that might have been the problem also not seperating them, but I tried seperating them, but still didn't solve the problem. Then again codes like this are so finiky I could have placed the comma wrong or had and extra space.. Will give it another try
I think I figured it out and was able to make a very slight change to the code and get all 3 tables updated with the use of one statement. Here is what I did. I went back and seperated the "SET" statements with commas. I tried this before and it didn't work, but for giggles I decide to try it again. Well the seperating the "SET" statements with commas didn't fix the problem. Next I decided to change the "AND" in the "WHERE statement from being all capitalized to just the first letter being capitalized "And." I ran the code and it worked. All three tables updated with the new part number. Here is the code that I used: - Dim mySQL As String
-
-
mySQL = "UPDATE tblPreventiveToolDamageinfo, tblPMTrial, tblPreventiveToolDamageLog"
-
mySQL = mySQL + " SET tblPreventiveToolDamageinfo.PartNumber = '" & Me.txtNewPartNumber & "', tblPMTrial.PartNumber = '" & Me.txtNewPartNumber & "', tblPreventiveToolDamageLog.PartNumber = '" & Me.txtNewPartNumber & "'"
-
mySQL = mySQL + " WHERE tblPreventiveToolDamageinfo.PartNumber = '" & Me.txtOldPartNumber & "' And tblPMTrial.PartNumber = '" & Me.txtOldPartNumber & "' And tblPreventiveToolDamageLog.PartNumber = '" & Me.txtOldPartNumber & "'"
-
-
DoCmd.RunSQL mySQL
-
cmbPartNumber.Requery
Bizarre. I've never heard of Access SQL being case sensitive. I'm glad you got it working. Now, I can't wait to modify a couple of my databases to make use of this.
Hi. Access is most definitely not case sensitive when it comes to the use of keywords - I wonder if there is a simpler explanation, such as an accidental additional space typed into one of the textboxes preventing the WHERE clause from matching? It has nothing at all to do with the case of the keywords - you can check this easily in the query builder using SQL View and try any form of case substitution you like - it won't make any difference.
There is no need to go through any existing queries changing the case of ANDs and so on...
-Stewart
I went back and changed the code back to using AND and it still worked, so I guess you are right on the case sensitive thing, but not sure what happened that made it work now and not work before...lol. It it working, so I am not going to lose anymore sleep over it..lol.
NeoPa 32,564
Recognized Expert Moderator MVP
@Annalyser UPDATE queries can include tables individually, or they can refer to any "updatable" recordset. Check the Help system for all the details, but generally a recordset is updatable if the tables are linked exclusively with INNER JOINs.
PS. Case is not sensitive in SQL as Stewart explains, but it is nevertheless good policy to capitalise all SQL reserved words. This is not for the benefit of the SQL processor, but rather for your own and anyone having to read it. It is likewise good policy to start each SQL clause on a separate line. You will definitely benefit from this over time ;)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jason |
last post by:
Let's say I have an html form with 20 or 30 fields in it. The form
submits the fields via POST to a php page which updates a table in a
database with the $_POST vars. Which makes more sense?
1) simply UPDATING the values for all fields in the table, whether or
not any particular field has actually changed
2) running a second SELECT...
|
by: LouD |
last post by:
I have 5 tables in my db and when i bring up the linked form I get my
product lists
Tbls are tbStock, which contain unitcost, sellprice n qty
tblProduct, contains ID, medicalname n genericname
tblCat contains ID, catname and catcode
tblPacket which ID, contains packetcode and description
tblSize which ID, contains the size code and...
|
by: gaosul |
last post by:
I am non-programming scientist and I am using a Program called
Easyarticles from Synaptosoft Inc., which is based the database
program Access. Unfortunately, the owner of this company has
disappeared even though his website still functions. As the program
requires updating, I was wondering whether anyone has any suggestions,
as to what I...
|
by: Doug |
last post by:
Scenario:
I select data from a SQL Server View which links 3 tables, into a
single dataset table. I update some of those fields on a web form.
When I want to update the db, clearly I can't update via a View -
makes perfect sense.
But because I used a View, I can't use the CommandBuilder. I have to
specify my own Commands which will in some...
|
by: Robert |
last post by:
I sent this last week, but am unable to go that far back
in the newsgroup to read the answer, so I'm posting again:
I'm using the Jet.Oledb to connect to an Access 2002
database. I have a form that is supposed to load
information from the database, and then return
information to it. It loads the info okay, but will not
update the...
| |
by: jaYPee |
last post by:
does anyone experienced slowness when updating a dataset using
AcceptChanges?
when calling this code it takes many seconds to update the database
SqlDataAdapter1.Update(DsStudentCourse1)
DsStudentCourse1.AcceptChanges()
i'm also wondering because w/ out AcceptChanges the data is still save
into the database and it is now faster.
|
by: Geoff |
last post by:
Hi
I'm hoping somebody can help me with the following problem that has occurred
to me.
Suppose I have two tables in an SQL Server database. Let's call these tables
A and B. Assume that A has two fields: a primary key and another holding a
string. In table B there are three fields: a primary key, a foreign key
(which links to the primary...
|
by: rdemyan via AccessMonster.com |
last post by:
My App has 10 or so tables that we provide that contains proprietary data.
This data will need to be updated once or twice a year.
I would like some comments, suggestions on my proposed strategy for updating
these tables via an ftp site:
1) Post a .mdb file to our ftp web site that contains the updated tables. My
App code connects to the...
|
by: =?Utf-8?B?VmFuZXNzYQ==?= |
last post by:
Hi All!
I am with a situation where I am not getting the right updating to the
form's fields. The situation is the following one:
I have one combobox and one textbox. I am using the CurrentChanged event of
the BindingSource of the combobox to update the textbox. When selecting an
item in the combobox or when selecting a row in the grid,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |