LienDate -Date
Instrument Number -Text
BookNumber -Text
PageNumber -Text
I guess I simply don’t understand the quote rules well enough in how they
should and shouldn’t be used. I have attempted to do as you stated however,
none of the information is being passed through in the sql.
The problem now is that you're enclosing the & concatenation operator INSIDE
the quoted strings, so it's being treated as a literal character, part of the
string.
What you're trying to do is assemble the string. Some of the pieces came from
your VBA variables; some are string constants delimited by " characters. The &
is the glue that sticks them together. The added complication is that in order
to have a doublequote character in the resulting string you need TWO
CONSECUTIVE doublequote characters within the string: that is, the string
"A, ""-B"
results in
A, "-B
strSQL = "INSERT INTO taDEFENDANTS(PropertyID, DefendantNameID,
DocumentTypeID, DefendantTypeID, LienDate "
strSQL = strSQL + "LienAmount, InstrumentNumber, BookNumber, PageNumber) "
The above is all ok, because you're just putting literal text into strSQL -
what's between the quotes is what you want in the string.
strSQL = strSQL + "VALUES(lngPropertyID,lngDefendantNameID,lngDocumentTypeID,"
But here's where you need to change it. What you want to do is assemble the
following pieces:
Values( << a literal text string
Whatever number is in lngPropertyID << the value of a variable
, << a literal comma as a text string
Whatever number is in lngDefendentNameID <<< the value of a variable
This should be (ignore the line wrap introduced by the newsgroup):
strSQL = strSQL & "VALUES(" & lngPropertyID & "," & lngDefendentNameID & "," &
lngDocumentTypeID & ","
strSQL = strSQL + "lngDefendantTypeID, #txtLienDate# , txtLienAmount ,"
And here you want to include the # delimiters, again, as string constants:
strSQL = strSQL & lngDefendentID & ", #" & txtLienDate & "#, " & txtLienAmount
& ","
strSQL = strSQL + "& txtInstrumentNumber & , & txtBookNumber & , &
txtPageNumber & )"
And for the text fields you need to use the "" trick to insert one " mark
within the string:
strSQL = strSQL & """ & txtInstrumentNumber & """, """ & txtBookNumber & """,
""" & txtPageNumber & """)"
The following code should work for you (using the _ line continuation
character instead of repeated strSQL =):
strSQL = "INSERT INTO taDEFENDANTS(PropertyID, DefendantNameID," _
& "DocumentTypeID, DefendantTypeID, LienDate " _
& "LienAmount, InstrumentNumber, BookNumber, PageNumber) " _
& "VALUES(" & lngPropertyID & "," & lngDefendantNameID _
& "," & lngDocumentTypeID & "," & lngDefendantTypeID _
& ", #" & txtLienDate & "# ," & txtLienAmount _
& " ,""" & txtInstrumentNumber & """,""" & txtBookNumber & """, """ _
& txtPageNumber & """)"
It's confusing as all get out, but if you work through the logic of assembling
pieces it should begin to make sense.
