Can't get sql to work. Please help.

R

RobUCSD

I can't get the sql below to work. Two problems;
1) I get a sql syntax error, Syntax error(comma) in query expression '(",
805, 'rwcass')'

2) in the select clause I get no out put form fldDescriptionOfProcedure. On
Me.txtVisitNo and Me.fldCreator I can see the out put by hovering my cursor
over them in the VB editor, but fldDesriptionOfProcedure nothing appears
while hovering.

The txt I'm trying to insert into the tblDictation comes from
tblDictationLU.fldDesriptionOfProcedure

Any help is greatly appreciated, I've been trying to get this to work for 3
days. Thanks, Rob
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim sql As String

sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT ('" & fldDescriptionOfProcedure & "', " & Me.txtVisitNO & ", '" &
Me.fldCreator & "') " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

DoCmd.RunSQL sql
 
D

Douglas J. Steele

Since fldDescriptionOfProcedure is a field in the table, it has to be inside
the quotes:

sql = "INSERT INTO tblDictation " & _
"(fldDescriptionOfProcedure, fldVisitNo, fldCreator) " & _
"SELECT fldDescriptionOfProcedure , " & Me.txtVisitNO & ", '" & _
Me.fldCreator & "' " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

The parenthesis were also incorrect.
 
Top