Button on Form Appends Current Record to Another Table

[headly]

I figure I'm close with this code, but it generates a syntax error on the
Docmd.RunSQL statement, probably related to my variable assignment above. TIA
for any assistance
Private Sub btnAppend_Click()
'Pass the current record to another table
Dim varID As Variant
varID = Me.ID
Dim mySQL As String
mySQL = "INSERT INTO tblTAs ( FirstName, LastName )" & " SELECT
tblPersonnel.FirstName, tblPersonnel.LastName" & " FROM tblPersonnel WHERE
(((tblpersonnel.id=" & varID & "));"
DoCmd.RunSQL mySQL
End Sub

 

[tina]

well, if you posted your code "as is" from the VBA module, then i'd say the
code is written on several lines, but without line breaks. try the
following, as

mySQL = "INSERT INTO tblTAs ( FirstName, LastName )" _
& " SELECT tblPersonnel.FirstName, tblPersonnel.LastName" _
& " FROM tblPersonnel WHERE (((tblpersonnel.id=" & varID & "));"

note the line break character (underscore) at the end of each line. now,
having said that, i have to wonder why you're storing the employee name in
more than one table in the first place. that violates normalization rules,
and is a bad idea unless you have a compelling business or technical reason
for doing so. well, even then it's a bad idea, which is why you shouldn't do
it unless the reason is compelling.

hth

 

[headly]

Thanks for trying tina, but not even close; The command is on one line and
the forum is forcing the text to wrap.

 

[John Spencer]

Unbalanced parentheses.

mySQL = "INSERT INTO tblTAs ( FirstName, LastName )" & " SELECT
tblPersonnel.FirstName, tblPersonnel.LastName" & " FROM tblPersonnel
WHERE tblpersonnel.id=" & varID

Your where clause had three "(" and two "))". Since none of them were
really needed, I deleted all of them in the where clause.

Hope this helps

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================