Runtime Error '3075'

[DiDi]

Hi, All : I borrowed Allen Browne's code to track Deletes, Edits, and
Inserts on a Form, I did modify to suit the needs,
However, I got a Runtime error "3075' syntax error in date in query
expression', the query expression lists in the following

Any idea to fix it?


sSQL = "INSERT INTO " & sAudTmpTable & " (audType, audDate, audUser,
[ID#], [Dod#], title, [Rev#], sectitle, " & _
"critreq, d1, d2, d3, d4, Internal_comments)" & _
"SELECT 'edit from 'AS audType, Now() AS audDate, strUserName AS
audUser, sTable.[ID#], sTable.[Dod#], " & _
" sTable.title, sTable.[Rev#], sTable.sectitle, sTable.critreq,
sTable.D1, sTable.D2, & _
" sTable.D3, sTable.Discipline4, sTable.Internal_comments" & _
" FROM " & sTable & " WHERE ((" & sTable & "." & sKeyField & "
= " & lngKeyValue & ")" & ");"


Above expression is working properly as an append query in Access query
design, but would get a Runtime error if the coding was placed within
the Form.

Thanks in advance for the help.

 

[George Nicholson]

Is sTable a variable or the actual name of a table? In the code you posted
it is being treated both ways, which strikes me as highly unlikely.
(Likewise, strUserName strikes me as a good variable name but a poor
FieldName, yet it's being treated as a Field name...)

Above expression is working properly as an append query in Access query
design

Doubtful.

Put a breakpoint in your code immediately *following* the statement shown,
then do the following:
1) in VB editor: View>Immediate, 2) type '?sSQL' (no quotes) in the
Immediate window.

Review the result. I suspect the problems will be obvious.
Remember that every place you see a variable name in the result (rather than
the value of that variable), you have a problem.
Also be aware of missing/extra spaces & quotes.

ONLY when you can copy the result into query designer's SQL view & have it
execute can you say it "works properly" there, and you need to get it to
that point for your code to run.

HTH,

Hi, All : I borrowed Allen Browne's code to track Deletes, Edits, and
Inserts on a Form, I did modify to suit the needs,
However, I got a Runtime error "3075' syntax error in date in query
expression', the query expression lists in the following

Any idea to fix it?


sSQL = "INSERT INTO " & sAudTmpTable & " (audType, audDate, audUser,
[ID#], [Dod#], title, [Rev#], sectitle, " & _
"critreq, d1, d2, d3, d4, Internal_comments)" & _
"SELECT 'edit from 'AS audType, Now() AS audDate, strUserName AS
audUser, sTable.[ID#], sTable.[Dod#], " & _
" sTable.title, sTable.[Rev#], sTable.sectitle, sTable.critreq,
sTable.D1, sTable.D2, & _
" sTable.D3, sTable.Discipline4, sTable.Internal_comments" & _
" FROM " & sTable & " WHERE ((" & sTable & "." & sKeyField & "
= " & lngKeyValue & ")" & ");"


Above expression is working properly as an append query in Access query
design, but would get a Runtime error if the coding was placed within
the Form.

Thanks in advance for the help.

 

[DiDi]

Thanks, your comments were useful, I made a correction of it. A
runtime error was caused by missing quotes at the WHERE clause which it
should be [ID#].

Is sTable a variable or the actual name of a table? In the code you posted
it is being treated both ways, which strikes me as highly unlikely.
(Likewise, strUserName strikes me as a good variable name but a poor
FieldName, yet it's being treated as a Field name...)

Above expression is working properly as an append query in Access query
design

Doubtful.

Put a breakpoint in your code immediately *following* the statement shown,
then do the following:
1) in VB editor: View>Immediate, 2) type '?sSQL' (no quotes) in the
Immediate window.

Review the result. I suspect the problems will be obvious.
Remember that every place you see a variable name in the result (rather than
the value of that variable), you have a problem.
Also be aware of missing/extra spaces & quotes.

ONLY when you can copy the result into query designer's SQL view & have it
execute can you say it "works properly" there, and you need to get it to
that point for your code to run.

HTH,

Hi, All : I borrowed Allen Browne's code to track Deletes, Edits, and
Inserts on a Form, I did modify to suit the needs,
However, I got a Runtime error "3075' syntax error in date in query
expression', the query expression lists in the following

Any idea to fix it?


sSQL = "INSERT INTO " & sAudTmpTable & " (audType, audDate, audUser,
[ID#], [Dod#], title, [Rev#], sectitle, " & _
"critreq, d1, d2, d3, d4, Internal_comments)" & _
"SELECT 'edit from 'AS audType, Now() AS audDate, strUserName AS
audUser, sTable.[ID#], sTable.[Dod#], " & _
" sTable.title, sTable.[Rev#], sTable.sectitle, sTable.critreq,
sTable.D1, sTable.D2, & _
" sTable.D3, sTable.Discipline4, sTable.Internal_comments" & _
" FROM " & sTable & " WHERE ((" & sTable & "." & sKeyField & "
= " & lngKeyValue & ")" & ");"


Above expression is working properly as an append query in Access query
design, but would get a Runtime error if the coding was placed within
the Form.

Thanks in advance for the help.