By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

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

P: 12
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command28_Click()
  3.  
  4. 'VBA to copy all manager info to matching sponsor.
  5.  
  6. On Error GoTo Err_Button_Click
  7. Dim ssql As String
  8.  
  9. 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)" & _
  10. "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)" & _
  11. "FROM [tblManagerInfo]" & _
  12. "WHERE SHC_No = SHC_No;"
  13.  
  14.  
  15. CurrentDb.Execute (ssql)
  16. MsgBox ("The info was copied to the Sponsor Contact.")
  17.  
  18. Exit_Button_Click:
  19. Exit Sub
  20. Err_Button_Click:
  21. MsgBox Err.Description
  22. Resume Exit_Button_Click
  23.  
  24.  
  25. End Sub
  26.  
The problem is that when I click the button right now it pops up the error: "Syntax error (comma) in query expression '(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)'." 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! :)
Feb 3 '14 #1
Share this Question
Share on Google+
6 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
Remove the parenthesis around the field names in your SELECT statement in line 10.
Feb 3 '14 #2

P: 12
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.
Feb 3 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,941
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 3 '14 #4

P: 12
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command28_Click()
  2.  
  3. 'VBA to copy all manager info to matching sponsor.
  4.  
  5. On Error GoTo Err_Button_Click
  6. Dim ssql As String
  7.  
  8. 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)" & _
  9. "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];"
  10.  
  11. Debug.Print ssql
  12.  
  13. CurrentDb.Execute (ssql)
  14. MsgBox ("The info was copied to the Sponsor Contact.")
  15.  
  16. Exit_Button_Click:
  17. Exit Sub
  18. Err_Button_Click:
  19. MsgBox Err.Description
  20. Resume Exit_Button_Click
  21.  
  22.  
  23. End Sub
Feb 3 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,941
Try making line 11 be
Expand|Select|Wrap|Line Numbers
  1. 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?
Feb 3 '14 #6

P: 12
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.
Feb 3 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.