SQL "SELECT INTO" to Copy Sponsor Contact Info to Manager Info if they are the same.

[morganaj]

Hello All,

I have a form to enter contact information, and often the sponsor info and the manager info is the same contact info. I am trying to use VBA to copy the information over to save me a bunch of time.

This is what I have:

Private Sub Command28_Click()

'VBA to copy all manager info to matching sponsor.

On Error GoTo Err_Button_Click
Dim ssql As String

ssql = "INSERT INTO [tblSponsorInfo] (Sponsor_Name, Sponsor_Salutation, Sponsor_First_Name, Sponsor_Last_Name, Sponsor_Title, Sponsor_Address, Sponsor_City, Sponsor_Province, Sponsor_Postal_Code, Sponsor_Phone, Sponsor_Fax, Sponsor_Email)" & _
"SELECT (Project_Managed_By, Manager_Salutation, Manager_First_Name, Manager_Last_Name, Manager_Title, Manager_Address, Manager_City, Manager_Province, Manager_Postal_Code, Manager_Phone, Manager_Fax, Manager_Email)" & _
"FROM [tblManagerInfo]" & _
"WHERE SHC_No = SHC_No;"


CurrentDb.Execute (ssql)
MsgBox ("The info was copied to the Sponsor Contact.")

Exit_Button_Click:
Exit Sub
Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click


End Sub

The problem is that when I click the button right now it pops up the error: "Syntax error (comma) in query expression '(Project_Manag ed_By, Manager_Salutat ion, Manager_First_N ame, Manager_Last_Na me, Manager_Title, Manager_Address , Manager_City, Manager_Provinc e, Manager_Postal_ Code, Manager_Phone, Manager_Fax, Manager_Email)' ." And as far as I can see the SQL statement is how it should be, so I need someone with more SQL experience than me to see the issue.

I appreciate any information or guidance that you can offer. Thanks! :)

[Seth Schrock]

Remove the parenthesis around the field names in your SELECT statement in line 10.

[morganaj]

Thank you Seth, that ran the SQL without error. Though I also had to remove the quotes around FROM. I forgot that it was a statement SELECT x FROM x.

[Seth Schrock]

Hmmm. You should have had to keep the quotes around the FROM statement as you are just building a string at that point. However, it might have needed a space built into either the end of line 10 or the beginning of line 11. Otherwise the last field selected would run together with the FROM keyword. At least you got it working.

A little debugging tip: If you put

Debug.Print ssql

in line 14, you will get the exact string that is causing the error in the immediate window (Ctrl + G to show it). It will show you where missing spaces are, etc. Having the string all broken up can make it tough to figure out where the error is.

[morganaj]

So this is working kind of. If I click it once it runs without an error, but will not copy the information. But, if I click it a second time it usually will copy it.

I'm not sure if this has to do with my SQL, but I assume probably. I also removed the from clause, as until a record was created it didn't have a SHC_No which is the primary key that is linked to the form. This seemed to work... like I said not 100% of the time. And I'm unsure how to make it more reliable?

Private Sub Command28_Click()

'VBA to copy all manager info to matching sponsor.

On Error GoTo Err_Button_Click
Dim ssql As String

ssql = "INSERT INTO [tblSponsorInfo] (SHC_NO, Sponsor_Name, Sponsor_Salutation, Sponsor_First_Name, Sponsor_Last_Name, Sponsor_Title, Sponsor_Address, Sponsor_City, Sponsor_Province, Sponsor_Postal_Code, Sponsor_Phone, Sponsor_Fax, Sponsor_Email)" & _
"SELECT SHC_No, Project_Managed_By, Manager_Salutation, Manager_First_Name, Manager_Last_Name, Manager_Title, Manager_Address, Manager_City, Manager_Province, Manager_Postal_Code, Manager_Phone, Manager_Fax, Manager_Email FROM [tblManagerInfo];"

Debug.Print ssql

CurrentDb.Execute (ssql)
MsgBox ("The info was copied to the Sponsor Contact.")

Exit_Button_Click:
Exit Sub
Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click


End Sub

[Seth Schrock]

Try making line 11 be

CurrentDb.Execute ssql, dbFailOnError

This will make it produce a useful error message if an error does occur. Also, I just noticed, you don't have a WHERE clause in your SELECT statement. Thus you will be copying the entire table contents. Not sure if this is what you are wanting.

Do you always get your copy successful message even when the copy doesn't seem to have occurred?

[morganaj]

I tried that. The first time still produced a successful message without actually copying. The second time it went over. I didn't get an error message either time.