Confirming Delete

[Vic]

Hello,

I have a continuous form and I'm having a problem deleting like 1 out of
several records selected. If I delete everything selected it works fine.
However, if I select 2 records and then reply yes to 1 and NO to the other
neither record is deleted. My messages displayed indicated that it worked
correctly but if I cancel 1 then both are canceled. Of course this only
occurs on multiple record selected not when only 1 record is to be deleted.

I am creating an array of the record codes that are selected for delete in
the Delete event and then in the BeforeDelConfirm event it uses the array to
confirm each record for delete. Seems pretty straight forward but doesn't
work for me and I would really appreciate some insight into this.

Thank you very much!

Vic


Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

Dim resp As Long
For i = 0 To itemsDeleted - 1
resp = MsgBox("Do you want to delete fee code: " & strFeeCode(i),
vbYesNo, "Show Secretary")
If resp = vbNo Then
MsgBox strFeeCode(i) & " NOT Deleted", vbInformation, "Show
Secretary"
Cancel = True
Else
MsgBox strFeeCode(i) & " Deleted", vbInformation, "Show
Secretary"
End If
Next i

End Sub


Private Sub Form_Delete(Cancel As Integer)

strFeeCode(i) = Me.Fee_Id
i = i + 1
itemsDeleted = i

End Sub

 

[Vic]

Problem Solved - By doing everything in the Delete event and not using the
Delconfirm I'm able to selectively delete records out of a multiple
selection.

Thanks for looking!


Vic

 

[Jeff Boyce]

Vic

You're closer to your situation, so the following may not apply...

Sometimes when folks say "delete a record" they really mean "I don't want to
see it any more". This is NOT the same as totally removing the record.

For instance, imagine a table that has "active" participants. When someone
is no longer an active participant, you could chose to delete their record
.... thereby losing the pointer to all related records in other tables! A
more appropriate solution in this situation is to use either a yes/no
checkbox (?Active) or a date/time field (DateInactivated) as a way to note
who is active. Then you'd use a query to get/display ONLY those still
Active.

If you'll provide a more specific description, folks here may be able to
offer more specific suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Vic]

Thanks Jeff,

Actually, what this was intended to do is prevent deleting records in a
table that may be linked to 1 or 2 different tables. So the code will
verify if records exist in either of the 2 other tables before allowing the
delete to take place.

Appreciate your input!

Vic

 

[Jeff Boyce]

If you have records in table#1 that are related to records in tables#2 and
#3, you can prevent the deletion of records (in table#1) by enforcing
referential integrity. Access will check and will not allow the deletion
.... you don't need to!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Also, another way to approach this would be to ONLY show records that CAN be
deleted. You could use a query to find records in Table1 without
corresponding records in table2 or 3.

Regards

Jeff Boyce
Microsoft Office/Access MVP