SQL Coding Question

  • Thread starter Thread starter Ray Todd Jr
  • Start date Start date
R

Ray Todd Jr

I have a form that will create printing batches (via code) thanks to the
contributions of Allen Browne. One of the functions I am attempting to
modify this code to do is to update a notes table with the date/time that the
letter was generated. As a result of my code, I get the following error
message:

Run Time Error 3075
Syntax Error (missing operator) in query expression 'letter printed on
4/17/2008 4/17/2008 9:34:17 PM

The code:

Dim strDefNote As String

strDefNote = "Letter Printed on " & Now()

strSql = "Update taDefendantNotes set DefNote= " & strDefNote & " where
taDefendants.BatchID= " & lngBatchID & " and tadefendants.defendantid =
tadefendantnotes.defendantid"
db.Execute strSql, dbFailOnError

Can anyone offer suggestion as to what I'm not adding to make this work.

Thanks,

Ray.
 
Hi Ray

If you put a literal text string in a SQL statement, you must enclose it in
either single or double quotes.

Your string says:
Update ... set DefNote=Letter Printed on ...
so the SQL parser is trying to interpret /letter printed on.../ as SQL, not
as text.

So, in VBA you can say:

strSql = "Update taDefendantNotes set DefNote= '" & strDefNote & "' where...

Note that this will not add a new record to your taDefendantNotes table. It
will only update an existing record with matching BatchID and DefendantID.
It will fail if such a matching record does not already exist. Furthermore,
it will overwrite the DefNote field if that record does already exist.

I suspect that is not what you want! I suggest you try:

strSql = "Insert into taDefendantNotes (BatchID, DefendantID, DefNote) " _
& "values (" & lngBatchID & ", " & DefendantID & ", '" & strDefNote &
"')"
 
Graham:

Thanks for your help! That did the trick. However, I do have another
problem that has come up.

I am getting error 3201 (You cannot add or change a record because a related
recored is required in table 'taDEFENDANTS'.)

I understand WHAT the error means, however, what I don't know is how to fix
it using SQL.

The two tables:

taDefendants taDEFENDANTSNotes
DefendantID (PK) DefNotesID (PK)
PropertyID DefendantID (FK)
.... ...

Private Sub cmdCreateBatch_Click()
'On Error Goto Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngBatchID As Long
Dim lngKt As Long
Dim lngKtSubtotal1 As Long
Dim lngKtSubtotal2 As Long
Dim strDefNote As String
Dim lngDefendantID As Long

strDefNote = "Letter Printed on " & Now()


'Create the new batch, and get the number.
Set db = CurrentDb()
Set rs = db.OpenRecordset("taBATCH", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!BatchDateTime = Now()
lngBatchID = rs!BatchID
rs.Update
rs.Close

'Give this batch number to all members who have not been printed.
strSql = "Update taDefendants set BatchID= " & lngBatchID & " Where
BatchID is Null;"
db.Execute strSql, dbFailOnError
lngKtSubtotal1 = db.RecordsAffected

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " _
& "values (" & lngDefendantID & ", '" & strDefNote & "')"
db.Execute strSql, dbFailOnError


Any help/suggestions would be appreciated.


Thanks,

Ray.


Graham Mandeno said:
Hi Ray

If you put a literal text string in a SQL statement, you must enclose it in
either single or double quotes.

Your string says:
Update ... set DefNote=Letter Printed on ...
so the SQL parser is trying to interpret /letter printed on.../ as SQL, not
as text.

So, in VBA you can say:

strSql = "Update taDefendantNotes set DefNote= '" & strDefNote & "' where...

Note that this will not add a new record to your taDefendantNotes table. It
will only update an existing record with matching BatchID and DefendantID.
It will fail if such a matching record does not already exist. Furthermore,
it will overwrite the DefNote field if that record does already exist.

I suspect that is not what you want! I suggest you try:

strSql = "Insert into taDefendantNotes (BatchID, DefendantID, DefNote) " _
& "values (" & lngBatchID & ", " & DefendantID & ", '" & strDefNote &
"')"

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Ray Todd Jr said:
I have a form that will create printing batches (via code) thanks to the
contributions of Allen Browne. One of the functions I am attempting to
modify this code to do is to update a notes table with the date/time that
the
letter was generated. As a result of my code, I get the following error
message:

Run Time Error 3075
Syntax Error (missing operator) in query expression 'letter printed on
4/17/2008 4/17/2008 9:34:17 PM

The code:

Dim strDefNote As String

strDefNote = "Letter Printed on " & Now()

strSql = "Update taDefendantNotes set DefNote= " & strDefNote & " where
taDefendants.BatchID= " & lngBatchID & " and tadefendants.defendantid =
tadefendantnotes.defendantid"
db.Execute strSql, dbFailOnError

Can anyone offer suggestion as to what I'm not adding to make this work.

Thanks,

Ray.
Click to expand...
Click to expand...
 
Hi Ray

Sorry - I've been away for a few days visiting my sick mother.

You have not assigned a value to lngDefendantID, so it is using its initial
value (zero) and of course there is no record in taDefendants with a zero
DefendantID.

Am I right in guessing that you want to add a taDefendantsNotes record for
EVERY defendant in the new batch?

If so, then you can get all the DefendantIDs from a query on taDefendants,
filtering on BatchID:

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " _
& "select DefendantID, '" & strDefNote & "' from taDefendants "
& "where BatchID=" & lngBatchID"
db.Execute strSql, dbFailOnError

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ray Todd Jr said:
Graham:

Thanks for your help! That did the trick. However, I do have another
problem that has come up.

I am getting error 3201 (You cannot add or change a record because a
related
recored is required in table 'taDEFENDANTS'.)

I understand WHAT the error means, however, what I don't know is how to
fix
it using SQL.

The two tables:

taDefendants taDEFENDANTSNotes
DefendantID (PK) DefNotesID (PK)
PropertyID DefendantID (FK)
... ...

Private Sub cmdCreateBatch_Click()
'On Error Goto Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngBatchID As Long
Dim lngKt As Long
Dim lngKtSubtotal1 As Long
Dim lngKtSubtotal2 As Long
Dim strDefNote As String
Dim lngDefendantID As Long

strDefNote = "Letter Printed on " & Now()


'Create the new batch, and get the number.
Set db = CurrentDb()
Set rs = db.OpenRecordset("taBATCH", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!BatchDateTime = Now()
lngBatchID = rs!BatchID
rs.Update
rs.Close

'Give this batch number to all members who have not been printed.
strSql = "Update taDefendants set BatchID= " & lngBatchID & " Where
BatchID is Null;"
db.Execute strSql, dbFailOnError
lngKtSubtotal1 = db.RecordsAffected

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " _
& "values (" & lngDefendantID & ", '" & strDefNote & "')"
db.Execute strSql, dbFailOnError


Any help/suggestions would be appreciated.


Thanks,

Ray.


Graham Mandeno said:
Hi Ray

If you put a literal text string in a SQL statement, you must enclose it
in
either single or double quotes.

Your string says:
Update ... set DefNote=Letter Printed on ...
so the SQL parser is trying to interpret /letter printed on.../ as SQL,
not
as text.

So, in VBA you can say:

strSql = "Update taDefendantNotes set DefNote= '" & strDefNote & "'
where...

Note that this will not add a new record to your taDefendantNotes table.
It
will only update an existing record with matching BatchID and
DefendantID.
It will fail if such a matching record does not already exist.
Furthermore,
it will overwrite the DefNote field if that record does already exist.

I suspect that is not what you want! I suggest you try:

strSql = "Insert into taDefendantNotes (BatchID, DefendantID, DefNote) "
_
& "values (" & lngBatchID & ", " & DefendantID & ", '" & strDefNote &
"')"

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Ray Todd Jr said:
I have a form that will create printing batches (via code) thanks to the
contributions of Allen Browne. One of the functions I am attempting to
modify this code to do is to update a notes table with the date/time
that
the
letter was generated. As a result of my code, I get the following
error
message:

Run Time Error 3075
Syntax Error (missing operator) in query expression 'letter printed on
4/17/2008 4/17/2008 9:34:17 PM

The code:

Dim strDefNote As String

strDefNote = "Letter Printed on " & Now()

strSql = "Update taDefendantNotes set DefNote= " & strDefNote & " where
taDefendants.BatchID= " & lngBatchID & " and tadefendants.defendantid =
tadefendantnotes.defendantid"
db.Execute strSql, dbFailOnError

Can anyone offer suggestion as to what I'm not adding to make this
work.

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
Hello Graham:

Hope you mother is doing better. You were a big help as you pointed me in
the right direction. It is appreciated.

Thanks,

Ray.

Graham Mandeno said:
Hi Ray

Sorry - I've been away for a few days visiting my sick mother.

You have not assigned a value to lngDefendantID, so it is using its initial
value (zero) and of course there is no record in taDefendants with a zero
DefendantID.

Am I right in guessing that you want to add a taDefendantsNotes record for
EVERY defendant in the new batch?

If so, then you can get all the DefendantIDs from a query on taDefendants,
filtering on BatchID:

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " _
& "select DefendantID, '" & strDefNote & "' from taDefendants "
& "where BatchID=" & lngBatchID"
db.Execute strSql, dbFailOnError

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ray Todd Jr said:
Graham:

Thanks for your help! That did the trick. However, I do have another
problem that has come up.

I am getting error 3201 (You cannot add or change a record because a
related
recored is required in table 'taDEFENDANTS'.)

I understand WHAT the error means, however, what I don't know is how to
fix
it using SQL.

The two tables:

taDefendants taDEFENDANTSNotes
DefendantID (PK) DefNotesID (PK)
PropertyID DefendantID (FK)
... ...

Private Sub cmdCreateBatch_Click()
'On Error Goto Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngBatchID As Long
Dim lngKt As Long
Dim lngKtSubtotal1 As Long
Dim lngKtSubtotal2 As Long
Dim strDefNote As String
Dim lngDefendantID As Long

strDefNote = "Letter Printed on " & Now()


'Create the new batch, and get the number.
Set db = CurrentDb()
Set rs = db.OpenRecordset("taBATCH", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!BatchDateTime = Now()
lngBatchID = rs!BatchID
rs.Update
rs.Close

'Give this batch number to all members who have not been printed.
strSql = "Update taDefendants set BatchID= " & lngBatchID & " Where
BatchID is Null;"
db.Execute strSql, dbFailOnError
lngKtSubtotal1 = db.RecordsAffected

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " _
& "values (" & lngDefendantID & ", '" & strDefNote & "')"
db.Execute strSql, dbFailOnError


Any help/suggestions would be appreciated.


Thanks,

Ray.


Graham Mandeno said:
Hi Ray

If you put a literal text string in a SQL statement, you must enclose it
in
either single or double quotes.

Your string says:
Update ... set DefNote=Letter Printed on ...
so the SQL parser is trying to interpret /letter printed on.../ as SQL,
not
as text.

So, in VBA you can say:

strSql = "Update taDefendantNotes set DefNote= '" & strDefNote & "'
where...

Note that this will not add a new record to your taDefendantNotes table.
It
will only update an existing record with matching BatchID and
DefendantID.
It will fail if such a matching record does not already exist.
Furthermore,
it will overwrite the DefNote field if that record does already exist.

I suspect that is not what you want! I suggest you try:

strSql = "Insert into taDefendantNotes (BatchID, DefendantID, DefNote) "
_
& "values (" & lngBatchID & ", " & DefendantID & ", '" & strDefNote &
"')"

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I have a form that will create printing batches (via code) thanks to the
contributions of Allen Browne. One of the functions I am attempting to
modify this code to do is to update a notes table with the date/time
that
the
letter was generated. As a result of my code, I get the following
error
message:

Run Time Error 3075
Syntax Error (missing operator) in query expression 'letter printed on
4/17/2008 4/17/2008 9:34:17 PM

The code:

Dim strDefNote As String

strDefNote = "Letter Printed on " & Now()

strSql = "Update taDefendantNotes set DefNote= " & strDefNote & " where
taDefendants.BatchID= " & lngBatchID & " and tadefendants.defendantid =
tadefendantnotes.defendantid"
db.Execute strSql, dbFailOnError

Can anyone offer suggestion as to what I'm not adding to make this
work.

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top