Using Variables in an INSERT INTO query

[Lee]

Hi guys (again!!)
I'm having trouble trying to get an INSERT INTO query
working. I want to use the query to append a single row
of values to a table. This is fine but instead of using
values I'd like to use variables instead but I can't seem
to get the query to recognise them. Can you advise on
the correc syntax? Below is the code I've got at present:

Dim db As Database
Dim qdf As QueryDef
Dim fld1 As Field, fld As Field, st3 As String, st4
As String, st5 As String, st6 As String

fld1 = Forms!frm_InspectionReports!txtReportID
fld2 = Forms!frm_InspectionReports!txtActualInspID
st3 = "3"
st4 = "1"
st5 = Now()
st6 = "Standard Letter: blah, blah."

Set db = CurrentDb
Set qdf = db.QueryDefs("qry_AddLetter")
qdf.SQL = "INSERT INTO tbl_Letters (ReportID,
ActualInspID, FromID, ToID, LetterDate, LetterNotes)" _
& "VALUES (fld1, fld2, st3, st4, st5, st6)"
DoCmd.OpenQuery qdf.Name

BTW, |'ve tried setting the first two variables as
Controls and Strings but still with no success.
What am I doing wrong?!

Thanks for any help you can offer.

Lee

 

[JulieD]

Hi Lee

without really testing what you've provided i think the problem has to do
with concatenation
when you want to concatenation a string you use
"string 1 " & "string 2"
when you want to concatenation variables with a string you use
"string 1 " & variable 1 & "string 2 " & variable 2

so in your code you need to change

& "VALUES (fld1, fld2, st3, st4, st5, st6)"

to & "Values (" & fld1 & ", " & fld2 & ", " & st3 & ", " etc

but i always have to play around with this to get it exactly right
hope this gets you started though

Cheers
JulieD

 

[Lee]

Thanks Julie, I appreciate your help. I've got it
working now thanks to you.

Regards,

Lee