Button on Form Appends Current Record to Another Table

  • Thread starter Thread starter headly
  • Start date Start date
H

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
 
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
 
Thanks for trying tina, but not even close; The command is on one line and
the forum is forcing the text to wrap.
 
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
'====================================================
 
Back
Top