Two Strings together?

[galvinjaf]

Good Morning,

I'm an amateur when it comes to code. I have two strings that work, but they won't work together. I have Dim strSQL As String and then two strSQL statements. Only one will work, the other does nothing. How can I combine them? They both work, but not together. I can't come up with the right sequence to make then work simultaneously.

Dim strSQL As String
strSQL = "UPDATE tblCutSheet SET Customer_Name = " & Chr(34) & Me.Customer_Name & Chr(34) & " WHERE Account_Number = " & Me.Account_Number
strSQL = "UPDATE tblCutSheet SET Customer_Phone = " & Chr(34) & Me.Customer_Phone & Chr(34) & " WHERE Account_Number = " & Me.Account_Number
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "Account Number" & " " & Me.Account_Number.Value & " " & "has been Added to the Cut Sheet"
End Sub

(I cannot take credit for this code, another user/admin from this group helped me to create this) Works flawlessly for me.

 

[plog]

Since everything but the SET clause is the same, you can just construct the SET clause with every change you want. Check out W3schools:

http://www.w3schools.com/sql/sql_update.asp

Extra Hint: Don't try and get your SQL to work via VBA at first. Instead open up a query, and construct a test SQL string that actually works as you want. Then you move that SQL string to your VBA and reconstruct it to use your variables.

 

[galvinjaf]

Thanks for the feedback. I took a look at the website you posted, but because of my unfamiliarity, I don't quite know how to arrange the code to do as you suggest. I see the SET and WHERE, but don't understand what to actually do with them...

 

[plog]

You seperate everything in the SET clause by commas:

UPDATE YourTable SET Field1='ABC', Field2=7, .... WHERE Field3=1;

 

[galvinjaf]

Okay, so I understand the fundamental. The code is below, but I feel I've got way too many or not enough commas?

Private Sub Command82_Click()
Dim strSQL As String
Set strSQL = "UPDATE tblCutSheet SET Customer_Name = " & Chr(34) & Me.Customer_Name, & Chr(34) & " WHERE Account_Number = " & Me.Account_Number, Customer_Phone = " & Chr(34) & Me.Customer_Phone, & Chr(34), & " WHERE Account_Number = " & Me.Account_Number
  CurrentDb.Execute strSQL, dbFailOnError
 MsgBox "Account Number" & " " & Me.Account_Number.Value & " " & "has been Added to the Cut Sheet"
End Sub

 

[arnelgp]

strSQL = "UPDATE tblCutSheet SET Customer_Name = " & Chr(34) & Me.Customer_Name & Chr(34) & ", "
strSQL = strsql & "Customer_Phone = " & Chr(34) & Me.Customer_Phone & Chr(34) & " WHERE Account_Number = " & Me.Account_Number
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "Account Number" & " " & Me.Account_Number.Value & " " & "has been Added to the Cut Sheet"
End Sub

 

[plog]

And on cue there's arnelgp to help prevent a lesson from being learned.

 

[galvinjaf]

Thanks arnelgp, I appreciate it. plog, you as well. I'm certainly not afraid to learn, and I DO study the difference between my code, and the ones offered to me for the next time. There is a balance.

 

[galvinjaf]

So in practicing with this code building, I've recreated the same code, but have basically reversed the action which is to make the fields blank on a button click. (deleting the info from the tblcutsheet)

I believe my code is right, BUT get an error on the CurrentDB.Execute string...

Private Sub Command84_Click()
Dim strSQL As String
strSQL = "UPDATE tblCutSheet SET Customer_Name = " & Null & ", "
strSQL = strSQL & "Customer_Phone = " & Null & " WHERE Account_Number = " & Me.Account_Number
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "Account Number" & " " & Me.Account_Number.Value & " " & "has been Deleted from the Cut Sheet"
End Sub

 

[arnelgp]

strSQL = "UPDATE tblCutSheet SET Customer_Name = Null ,"
strSQL = strSQL & "Customer_Phone = Null WHERE Account_Number = " & Me.Account_Number

i hope you are getting something without explanation. i have difficulty on explaining.
your assigment is to find out why.