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


"headly" <(E-Mail Removed)> wrote in message
news:9A63F572-6D49-4CD0-AFCB-(E-Mail Removed)...
> 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

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

"tina" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:9A63F572-6D49-4CD0-AFCB-(E-Mail Removed)...
> > 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
>
>
>

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
'====================================================


headly wrote:
> 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