Vba code to append table based on dynamic table name

[sw1085]

Hi All,

I am trying to combine these two bits of code together so I can basically select the data from one table and transfer this to another table however the table name of the table I am copying to would be based on tblDatabase & the value from the record in the table we're copy from.

It just continues to fail to run and gives a message of Compile Error Syntax.

I am not a coder at all as you can probably guess.

Dim tName As String
Dim strSQL As String

tName = tblDatato & tblDatafrom.COCODE
strSQL = "INSERT INTO " & tName & _
SELECT Acc, Amount, [Desc], JRNAL_LINE FROM
(SELECT Acc, Amount, [Desc], 1 AS JRNAL_LINE
FROM tblDatafrom
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount IS NOT NULL
UNION ALL
SELECT Acc1, Amount1, [Desc], 2 AS JRNAL_LINE
FROM tblDatafrom
WHERE [Ready] = True AND [Acc1] IS NOT NULL AND Amount1 IS NOT NULL
UNION ALL
SELECT 271 AS Acc, Amount, [Desc], 3 AS JRNAL_LINE
FROM tblDatafrom
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount2 IS NOT NULL)

DoCmd.RunSQL strSQL

Any help would be much appreciated.

Thanks alot

 

[pbaldy]

You never restarted the string after you concatenated the variable.

 

[sw1085]

You never restarted the string after you concatenated the variable.

Paul

I'm sorry I don't understand. I simply copied that code and tried to amend but failed.

Do you think you could help me by correcting the code?

Thanks very much for your help. I've been :banghead: all day!

 

[sw1085]

Hi Paul

I have made some minor changes - can you please take a look to see what could be wrong? I keep getting run-time error 3129. Thanks very much in advance for your help.

Sub TRFDATATO()
Dim tName As String
Dim strSQL As String
tName = "tblDatato & tblDatafrom.CO_CODE " & _
strSQL = "INSERT INTO " & tName & _
"SELECT SUN_DB, ACCNT_CODE, [NET AMOUNT], DESCRIPTN, JRNAL_LINE FROM " & _
"(SELECT SUN_DB, ACCNT_CODE, [NET AMOUNT], DESCRIPTN, 1 AS JRNAL_LINE " & _
"FROM tblDatafrom" & _
"WHERE [Ready] = True And Null<>[ACCNT_CODE] And Null<>[NET AMOUNT]" & _
"UNION ALL" & _
"SELECT SUN_DB, SUPP_CODE, [GROSS AMOUNT], DESCRIPTN, 2 AS JRNAL_LINE" & _
"FROM tblDatafrom" & _
"WHERE [Ready] = True And Null<>[SUPP_CODE] And Null<> [GROSS AMOUNT]" & _
"UNION ALL" & _
"SELECT SUN_DB, 271 AS ACCNT_CODE, VAT, DESCRIPTN, 3 AS JRNAL_LINE" & _
"FROM tblDatafrom" & _
"WHERE [Ready] = True AND Null<>ACCNT_CODE)"
DoCmd.RunSQL strSQL
End Sub

Thanks again

SW

Paul

I'm sorry I don't understand. I simply copied that code and tried to amend but failed.

Do you think you could help me by correcting the code?

Thanks very much for your help. I've been :banghead: all day!

 

[pbaldy]

Here's a handy debugging tool:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, you've made a common error of failing to include spaces between lines. For instance, you'll end up with:

"INSERT INTO TableNameSELECT SUN_DB...