Append query VBA?

[Tango]

LOL, I had that exact web page open this morning trying to figure it out.

 

[Tango]

I just tried to run it and got a syntax error in this line.

CurrentDb.Execute "INSERT INTO tblActiveMaster(UnitCount, Line Number, Question, AFI, PagePara, PubDate, UnitAssigned, CycleNumber, DateAssigned, SuspenceDate, CriticalYN, AssignedBy) Values (UC,'" & LineNum & "','" & Qstn & "','" & AF & "','" & PP & "','" & pubD & "','" & tempName & "', CycleNum,'" & Dassign & "','" & suspDate & "', Crit,'" & assignby & "')"

 

[G37Sam]

Pop a cold beer open brother, it's going to be a long night haha

 

[Tango]

haha, I get that feeling.

 

[G37Sam]

You missed a few & sign here:

tempName & "', CycleNum,

 

[Tango]

CurrentDb.Execute "INSERT INTO tblActiveMaster(UnitCount, Line Number, Question, AFI, PagePara, PubDate, UnitAssigned, CycleNumber, DateAssigned, SuspenceDate, CriticalYN, AssignedBy) Values (" & UC & ",'" & LineNum & "','" & Qstn & "','" & AF & "','" & PP & "',#" & pubD & "#,'" & tempName & "'," & CycleNum & ",#" & Dassign & "#,#" & suspDate & "#," & Crit & ",'" & assignby & "')"

...syntax error. ARGGhhhh

 

[Tango]

You know, even though stuff like this is a pain. This is exactly why I am going to school to be a database developer. Pain or not it's interesting and fun too.

 

[G37Sam]

Tell me about it, I studied Mechanical Engineering but always worked on DB development on the side. Got a soft spot for it. You're gonna love it.

The best thing you can do now to debug, is to debug.print your sql statement in the immediate window to see how it looks like.

Debug.print "INSERT INTO tblActiveMaster(UnitCount, Line Number, Question, AFI, PagePara, PubDate, UnitAssigned, CycleNumber, DateAssigned, SuspenceDate, CriticalYN, AssignedBy) Values (" & UC & ",'" & LineNum & "','" & Qstn & "','" & AF & "','" & PP & "',#" & pubD & "#,'" & tempName & "'," & CycleNum & ",#" & Dassign & "#,#" & suspDate & "#," & Crit & ",'" & assignby & "')"

 

[Tango]

Progress I suppose, now I am getting run-time error 3346 "Number of query values and destination fields are not the same.

Here is what I changed it to.

CurrentDb.Execute "INSERT INTO tblActiveMaster Values (" & UC & ",'" & LineNum & "','" & Qstn & "','" & AF & "','" & PP & "',#" & pubD & "#,'" & tempName & "'," & CycleNum & ",#" & Dassign & "#,#" & suspDate & "#," & Crit & ",'" & assignby & "')"

 

[Tango]

FOUND IT!

 CurrentDb.Execute "INSERT INTO tblActiveMaster (UnitCount, [Line Number],

I forgot to put [] around the Line Number since it has a space in the name... stupid spaces...

It works, almost. In my test table I have two records each needs to be split with this code. The code processed the first record but didnt touch the next record.

 

[G37Sam]

Great!

Make it a habit to not use spaces in names.

 

[Tango]

Thanks again for all the help and education. One last thing though. How do I make it loop this code through all the records in the source table and not just the first?

 

[G37Sam]

Something like this?

Dim i As Integer, iPrevious As Integer, strNames As String, tempName As String, UC As Integer, LineNum As String, Qstn As String, AF As String, PP As String, pubD As String, CycleNum As Integer, Dassign As String, suspDate As String, Crit As Integer, assignby As String, tb as DAO.recordset

set tb = currentdb.openrecordset("tblmasterchecklisttemp")
tb.movefirst
do while tb.eof = false
strNames = tb!UnitAssigned
UC = tb!UnitCount
LineNum = tb![Line Number]
Qstn = tb!Question
AF = tb!AFI
PP = tb!PagePara
pubD = tb!PubDate
CycleNum = tb!CycleNumber
Dassign = tb!DateAssigned
suspDate = tb!SuspenceDate
Crit = tb!CriticalYN
assignby = tb!AssignedBy

'add a trailing semicolon to check end of string later
If Right(strNames, 1) <> ";" Then
    strNames = strNames & ";"
End If
i = 1
Do While i < Len(strNames)
    iPrevious = i
    i = InStr(i, strNames, ";") + 1
    tempName = Mid(strNames, iPrevious, i - iPrevious - 1)
    CurrentDb.Execute 'Insert your finalized SQL statement here
Loop
tb.movenext
loop
tb.close
End Sub

 

[Tango]

Jackpot, That did it perfectly. You sir, are amazing. One free internet cookie for you!

 

[G37Sam]

Internet cookie???

If I ever come down to Johannesburg you're buying me a beer! haha

Glad I could

 

[Tango]

Be glad to, but I hear the commute from UAE can be a nightmare.