Updating mutliple tables through a form

[Steve67]

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 txtNewPartNumber and the other text box is named txtOldPartNumber. The idea is for the user to type in the old part number in the txtOldPartNumber box and type what the new part number is changing to into the txtNewPartNumber 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 tblPreventiveToolDamageinfo, tblPMTrial"
mySQL = mySQL + " SET tblPMTrial.PartNumber = '" & Me.txtNewPartNumber & "' tblPreventiveToolDamageinfo.PartNumber = '" & Me.txtNewPartNumber & "'"
mySQL = mySQL + " WHERE tblPMTrial.PartNumber = '" & Me.txtOldPartNumber & "' AND tblPreventiveToolDamageinfo.PartNumber = '" & Me.txtOldPartNumber & "'"

DoCmd.RunSQL mySQL
cmbPartNumber.Requery
End Sub

Thanks for your help

[Annalyzer]

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 tblPreventiveToolDamageinfo SET tblPreventiveToolDamageinfo.PartNumber = '" & Me.txtNewPartNumber & " WHERE tblPreventiveToolDamageinfo.PartNumber = '" & Me.txtOldPartNumber & "'"
DoCmd.RunSQL mySQL

mySQL = "UPDATE tblPMTrial SET tblPMTrial.PartNumber = '" & Me.txtNewPartNumber & " WHERE tblPMTrial.PartNumber = '" & Me.txtOldPartNumber & "'"
DoCmd.RunSQL mySQL

mySQL = "UPDATE tblTable3 SET tblTable3.PartNumber = '" & Me.txtNewPartNumber & " WHERE tblTable3.PartNumber = '" & Me.txtOldPartNumber & "'"
DoCmd.RunSQL mySQL

Not a very elegant solution, granted, but I'm not yet a very elegant programmer. =)

[Steve67]

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.

[Annalyzer]

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 tblPreventiveToolDamageinfo, tblPMTrial"
mySQL = mySQL + " SET tblPMTrial.PartNumber = '" & Me.txtNewPartNumber & "', tblPreventiveToolDamageinfo.PartNumber = '" & Me.txtNewPartNumber & "'"
mySQL = mySQL + " WHERE tblPMTrial.PartNumber = '" & Me.txtOldPartNumber & "' AND tblPreventiveToolDamageinfo.PartNumber = '" & Me.txtOldPartNumber & "'"

Hope this works!

[Steve67]

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

[Steve67]

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

[Annalyzer]

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.

[Stewart Ross]

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

[Steve67]

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]

@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 ;)