Delete with SQL code

[Kurt]

I am trying to handle a record delete through a SQL
statement.

The delete works except:

1. "#Deleted" appears in all the affected fields until I
close and reopen the database. I obviously need some sort
of refresh statement.

2. Even though the delete ultimately worked, my custom
msgbox, "There were no records to update.", which is part
of an If Then statement near the end of the code,
executes. This is probably related to #1 above.

Any ideas? Thanks. - Kurt (Code below)

###

Private Sub cmdDeleteMI_Click()
On Error GoTo Err_Cancel

Dim iresponse As Integer
iresponse = MsgBox("This will delete this patient's
medical intake record, as well as any exam(s) associated
with this record." & _
Chr(13) & Chr(13) & "Continue?", 4 + 32 + 256)
If iresponse = 7 Then
Exit Sub
Else
End If

Dim db As DAO.Database
Set db = CurrentDb

Dim StrSql As String

StrSql = "DELETE tblMedicalIntake.intCaseNumber " & _
"FROM tblMedicalIntake " & _
"WHERE (((tblMedicalIntake.intCaseNumber)=[Forms]!
[frmMedicalIntake]![txtCaseNumber]));"

DoCmd.RunSQL StrSql

If db.RecordsAffected > 0 Then
'Tell user query was completed
MsgBox "Operation completed. Updated " &
db.RecordsAffected & " Records"
Else
MsgBox "There were no records to update."
End If

Set db = Nothing

Exit_Cancel: ' Label to resume after error.
Exit Sub ' Exit before error handler.
Err_Cancel: ' Label to jump to on error.
MsgBox Err.Number & Err.Description ' Place error
handling here.
Resume Exit_Cancel

Exit_cmdDeleteMI_Click:
Exit Sub
End Sub

 

[Brian]

I am trying to handle a record delete through a SQL
statement.

The delete works except:

1. "#Deleted" appears in all the affected fields until I
close and reopen the database. I obviously need some sort
of refresh statement.

2. Even though the delete ultimately worked, my custom
msgbox, "There were no records to update.", which is part
of an If Then statement near the end of the code,
executes. This is probably related to #1 above.

Any ideas? Thanks. - Kurt (Code below)

###

Private Sub cmdDeleteMI_Click()
On Error GoTo Err_Cancel

Dim iresponse As Integer
iresponse = MsgBox("This will delete this patient's
medical intake record, as well as any exam(s) associated
with this record." & _
Chr(13) & Chr(13) & "Continue?", 4 + 32 + 256)
If iresponse = 7 Then
Exit Sub
Else
End If

Dim db As DAO.Database
Set db = CurrentDb

Dim StrSql As String

StrSql = "DELETE tblMedicalIntake.intCaseNumber " & _
"FROM tblMedicalIntake " & _
"WHERE (((tblMedicalIntake.intCaseNumber)=[Forms]!
[frmMedicalIntake]![txtCaseNumber]));"

DoCmd.RunSQL StrSql

If db.RecordsAffected > 0 Then
'Tell user query was completed
MsgBox "Operation completed. Updated " &
db.RecordsAffected & " Records"
Else
MsgBox "There were no records to update."
End If

Set db = Nothing

Exit_Cancel: ' Label to resume after error.
Exit Sub ' Exit before error handler.
Err_Cancel: ' Label to jump to on error.
MsgBox Err.Number & Err.Description ' Place error
handling here.
Resume Exit_Cancel

Exit_cmdDeleteMI_Click:
Exit Sub
End Sub

Several points:

1. Why are you using code to delete a record that is in the form's
recordset? This is almost always a bad idea: it's usually preferable to
use the form itself to delete a record, and then you wouldn't have this
problem with the "deleted" text in the fields.
2. You can requery the form's recordset using the statement Me.Requery
3. db.RecordsAffected is always zero because you haven't actually used
the db database object to execute your SQL! Your DoCmd.RunSQL statement
would work just the same if you hadn't bothered to declare db! This is a
better way to do it:

db.Execute strSQL, dbFailOnError

 

[Kurt]

1. Why are you using code to delete a record that

is in the form's recordset? This is almost always
a bad idea: it's usually preferable to use the form
itself to delete a record, and then you wouldn't
have this problem with the "deleted" text in the
fields.

When you say "use the form itself to delete a record," I
assume you mean this:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

The reason I'm using code is because I need to delete
related records from multiple tables, all of which are
related one-to-one. (There are too many fields to combine
them all into one table; hence, the one-to-ones.) All but
one of the relationships allow Cascade Delete. The one
which does not is the table in which I do *not* want the
related record deleted. The SQL allows me to handle this,
and it seemed to be the best way.

2. You can requery the form's recordset using the
statement Me.Requery

That did it. Thanks.

3. db.RecordsAffected is always zero because you
haven't actually used the db database object to
execute your SQL! Your DoCmd.RunSQL statement
would work just the same if you hadn't bothered to
declare db! This is a better way to do it:

db.Execute strSQL, dbFailOnError

Slight adjustments to the code and everything's working:

strSql = "DELETE tblMedicalIntake.intCaseNumber " & _
"FROM tblMedicalIntake " & _
"WHERE tblMedicalIntake.intCaseNumber=" & [Forms]!
[frmMedicalIntake]![txtCaseNumber]

db.Execute strSql, dbFailOnError

###

Thanks. - Kurt

-----Original Message-----

I am trying to handle a record delete through a SQL
statement.

The delete works except:

1. "#Deleted" appears in all the affected fields until I
close and reopen the database. I obviously need some sort
of refresh statement.

2. Even though the delete ultimately worked, my custom
msgbox, "There were no records to update.", which is part
of an If Then statement near the end of the code,
executes. This is probably related to #1 above.

Any ideas? Thanks. - Kurt (Code below)

###

Private Sub cmdDeleteMI_Click()
On Error GoTo Err_Cancel

Dim iresponse As Integer
iresponse = MsgBox("This will delete this patient's
medical intake record, as well as any exam(s) associated
with this record." & _
Chr(13) & Chr(13) & "Continue?", 4 + 32 + 256)
If iresponse = 7 Then
Exit Sub
Else
End If

Dim db As DAO.Database
Set db = CurrentDb

Dim StrSql As String

StrSql = "DELETE tblMedicalIntake.intCaseNumber " & _
"FROM tblMedicalIntake " & _
"WHERE (((tblMedicalIntake.intCaseNumber)=[Forms]!
[frmMedicalIntake]![txtCaseNumber]));"

DoCmd.RunSQL StrSql

If db.RecordsAffected > 0 Then
'Tell user query was completed
MsgBox "Operation completed. Updated " &
db.RecordsAffected & " Records"
Else
MsgBox "There were no records to update."
End If

Set db = Nothing

Exit_Cancel: ' Label to resume after error.
Exit Sub ' Exit before error handler.
Err_Cancel: ' Label to jump to on error.
MsgBox Err.Number & Err.Description ' Place error
handling here.
Resume Exit_Cancel

Exit_cmdDeleteMI_Click:
Exit Sub
End Sub

Several points:

1. Why are you using code to delete a record that is in the form's
recordset? This is almost always a bad idea: it's usually preferable to
use the form itself to delete a record, and then you wouldn't have this
problem with the "deleted" text in the fields.
2. You can requery the form's recordset using the statement Me.Requery
3. db.RecordsAffected is always zero because you haven't actually used
the db database object to execute your SQL! Your DoCmd.RunSQL statement
would work just the same if you hadn't bothered to declare db! This is a
better way to do it:

db.Execute strSQL, dbFailOnError


.

 

[Brian]

When you say "use the form itself to delete a record," I
assume you mean this:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

The reason I'm using code is because I need to delete
related records from multiple tables, all of which are
related one-to-one. (There are too many fields to combine
them all into one table; hence, the one-to-ones.) All but
one of the relationships allow Cascade Delete. The one
which does not is the table in which I do *not* want the
related record deleted. The SQL allows me to handle this,
and it seemed to be the best way.

Yes, that's what I meant. I still don't understand, though: if you've got
cascading deletes on the tables where you want the related records deleted,
then deleting the record via the form will delete them. Conversely, if you
do NOT have cascading deletes where you do NOT want the record deleted, then
it will NOT get deleted! So, where's the problem?

 

[Kurt]

I still don't understand, though: if you've got

cascading deletes on the tables where you want the
related records deleted, then deleting the record
via the form will delete them. Conversely, if you
do NOT have cascading deletes where you do NOT want
the record deleted, then it will NOT get deleted!
So, where's the problem?

I should explain that the record I don't want deleted is
in a table (tblCases) on the "left side" of the one-to-
one relationship. (tblCases is a linked table - i.e.,
linked from another database - although I'm not sure if
that matters. Also, tblCases is on the many side of a one-
to-many with tblPatients.)

tblPatients (linked table)
----------
intPatientCode (PK)

tblCases (linked table)
--------
intCaseNumber (PK)
intPatientCode

tblMedicalIntake (related to tblCases)
---------
intCaseNumber (PK)

tblExamPhysical (related to tblIntake)
---------------
intCaseNumber (PK)

tblExamSexual (related to tblIntake)
-------------
intCaseNumber (PK)

(The Integrity and Cascade options are greyed out for the
one-to-one between tblCases tblMedicalIntake.)

So, the SQL allows me to delete the record in
tblMedicalIntake, and the related records in
tblExamPhysical and tblExamSexual are deleted thanks to
Cascade Delete. Whereas, using the form to delete the
record deletes all records, including the one in tblCases.

Kurt