Girl in dire need of help

[knuddelsternchen]

Alrighty, so I dared to venture into the world of VBA coding, and have been RELATIVELY successful, but I'm stumped now. I have found many useful posts on this site, so I'm hoping someone will be kind enough to humor my (probably stupid and amateurish) question.

I wrote the following module for a database. It's supposed to update all records in Table1 for which the Test fields agrees between the two tables (Table1 and Table2) with the Update1, Update2, and Update3 fields from Table2. The reason that I have to create the join is that Table1 is to be imported via a previous step, so I don't have a pre-existing join.

DoCmd.RunSQL "Update Table1 LEFT JOIN Table2 ON Table1.Test = Table2.Test SET Table1.Update1 = Table2.Update1" & _
"Table1.Update2 = Table2.Update2" & _
"Table1.Update3 = Table2.Update3" & _
"Table1.Update4 = Table2.Update4;"
End Function

It works if I don't have to use the line continuation characters, but I'm getting the 3075 run-time error when I do.

HELP!

Thanks a ton!

 

[Kiwiman]

Howzit

Welcome to the forum.

The way you have it there is no comma between the first set stmt and the next. Try...

DoCmd.RunSQL "Update Table1 LEFT JOIN Table2 ON Table1.Test = Table2.Test SET Table1.Update1 = Table2.Update1" & _
"[B],[/B]Table1.Update2 = Table2.Update2" & _
"[B],[/B]Table1.Update3 = Table2.Update3" & _
"[B],[/B]Table1.Update4 = Table2.Update4;"

 

[Rokhi]

If you copy that line of code and paste it in the immediate window with the '?' in front, you will see the problem.

?"Update Table1 LEFT JOIN Table2 ON Table1.Test = Table2.Test SET Table1.Update1 = Table2.Update1" & _
"Table1.Update2 = Table2.Update2" & _
"Table1.Update3 = Table2.Update3" & _
"Table1.Update4 = Table2.Update4;"
 
result:
Update Table1 LEFT JOIN Table2 ON Table1.Test = Table2.Test SET Table1.Update1 = Table2.Update1Table1.Update2 = Table2.Update2Table1.Update3 = Table2.Update3Table1.Update4 = Table2.Update4;

Put commas and spaces at the end or begining of each line.

?"Update Table1 LEFT JOIN Table2 ON Table1.Test = Table2.Test SET Table1.Update1 = Table2.Update1" & _
", Table1.Update2 = Table2.Update2" & _
", Table1.Update3 = Table2.Update3" & _
", Table1.Update4 = Table2.Update4;"
 
Result:
Update Table1 LEFT JOIN Table2 ON Table1.Test = Table2.Test SET Table1.Update1 = Table2.Update1, Table1.Update2 = Table2.Update2, Table1.Update3 = Table2.Update3, Table1.Update4 = Table2.Update4;

 

[knuddelsternchen]

Wow - that was FAST. Thanks so much! That does make perfect sense, and I'm more than thankful for the help!!

After changing it to have leading commas and spaces, I'm now getting a Compile - Syntax Error. Thoughts?

 

[Rokhi]

Is the compile error at the same place?
Perhaps you could post the line of code giving you the error

 

[knuddelsternchen]

It actually highlights the entire four lines of code...

 

[Rokhi]

could you copy it and paste it in your post?

 

[knuddelsternchen]

I am an idiot...as I copied it over here, I realized what I did. I'm so sorry for bothering you. Thank you TONS for helping me out! You really got me out of bind. Have a good rest of the day!