Subform Delete Record and Filter By Form

[accessuser via AccessMonster.com]

On my main form, there is a "Delete Emp" command. It deletes all related
records including employee info, course info, and cost info. I want to keep
that and also add another delete command in the subform. The delete command
in subform will automatically delete the subform record and also the related
"frmCost" according to their "CourseID", but not the main form information.

Is it a good idea to add delete in the subform or should i just have the
users delete the information and reuse the courseID? and lastly, i have been
using the "Filter By form" and it seems pretty easy to use and don't even
need to create codes. Is there a reason why some people choose not to use
the "Filter By Form" ?


Thank you!

 

[Allen Browne]

The answer to your first question will depend on how your tables relate to
each other.

In the Relationships window (Tools | Relationships), you will have created a
relation between your Employee table and the courses the employee takes. You
can double-click that join line, ensure Referential Integrity is checked
(always), and consider whether you want to use Cascaing Delete. This would
mean when you delete an employee, all records of the courses they attended
is automatically deleted also (no code needed.) This cascade can continue on
from that table to further related tables (e.g. costs.)

You know your situation, and the cascading delete might sound too dangerous
to you. If so, then putting a command button in your subform is fine if you
want to delete the other records yourself instead of allowing cascading
deletes do it.

If Filter By Form does what you need, then by all means use it. It's fine
for power users. Developers tend to avoid it because:
- The interface is less obvious and less accessible for non-savvy users.
- It doesn't work in the mde/runtime.

 

[accessuser via AccessMonster.com]

Great! Thanks. I added a delete command on my subform and it works exactly
the way i wanted now. I think by having delete command on the subform also
makes it easier for users to differentiate between the main and subform on
what they delete.

This second database of mine will only be used by one dept, so no need to of
making MDB files. I guess i will train my users to use "filter by form" so
that i dont have to lose sleep over creating some type of "search" form.

how do i add a message to confirm with users about deleting records?

Thanks!

The answer to your first question will depend on how your tables relate to
each other.

In the Relationships window (Tools | Relationships), you will have created a
relation between your Employee table and the courses the employee takes. You
can double-click that join line, ensure Referential Integrity is checked
(always), and consider whether you want to use Cascaing Delete. This would
mean when you delete an employee, all records of the courses they attended
is automatically deleted also (no code needed.) This cascade can continue on
from that table to further related tables (e.g. costs.)

You know your situation, and the cascading delete might sound too dangerous
to you. If so, then putting a command button in your subform is fine if you
want to delete the other records yourself instead of allowing cascading
deletes do it.

If Filter By Form does what you need, then by all means use it. It's fine
for power users. Developers tend to avoid it because:
- The interface is less obvious and less accessible for non-savvy users.
- It doesn't work in the mde/runtime.

On my main form, there is a "Delete Emp" command. It deletes all related
records including employee info, course info, and cost info. I want to

[quoted text clipped - 13 lines]

use
the "Filter By Form" ?

 

[Allen Browne]

If you use the deletion built into Access, use the form's BeforeDelConfirm
event to display your custom message.

If you are deleting progrmmatically, you can just use MsgBox where it suits
in your code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Great! Thanks. I added a delete command on my subform and it works
exactly
the way i wanted now. I think by having delete command on the subform
also
makes it easier for users to differentiate between the main and subform on
what they delete.

This second database of mine will only be used by one dept, so no need to
of
making MDB files. I guess i will train my users to use "filter by form"
so
that i dont have to lose sleep over creating some type of "search" form.

how do i add a message to confirm with users about deleting records?

Thanks!

The answer to your first question will depend on how your tables relate to
each other.

In the Relationships window (Tools | Relationships), you will have created
a
relation between your Employee table and the courses the employee takes.
You
can double-click that join line, ensure Referential Integrity is checked
(always), and consider whether you want to use Cascaing Delete. This would
mean when you delete an employee, all records of the courses they attended
is automatically deleted also (no code needed.) This cascade can continue
on
from that table to further related tables (e.g. costs.)

You know your situation, and the cascading delete might sound too
dangerous
to you. If so, then putting a command button in your subform is fine if
you
want to delete the other records yourself instead of allowing cascading
deletes do it.

If Filter By Form does what you need, then by all means use it. It's fine
for power users. Developers tend to avoid it because:
- The interface is less obvious and less accessible for non-savvy users.
- It doesn't work in the mde/runtime.

On my main form, there is a "Delete Emp" command. It deletes all
related
records including employee info, course info, and cost info. I want to

[quoted text clipped - 13 lines]

use the "Filter By Form" ?

 

[accessuser via AccessMonster.com]

Allen,

My subform has the autonumber primary key "CourseID" and if my user
accidentally try to delete the not yet created row, the "End Debug" message
pops up, Is there a code that I can use to not show the "End, Debug" message.

If you use the deletion built into Access, use the form's BeforeDelConfirm
event to display your custom message.

If you are deleting progrmmatically, you can just use MsgBox where it suits
in your code.

Great! Thanks. I added a delete command on my subform and it works
exactly

[quoted text clipped - 44 lines]

[quoted text clipped - 13 lines]
use the "Filter By Form" ?

 

[accessuser via AccessMonster.com]

Never mind, I found the following code.... and is working pretty good.

Private cmdDelete_Click()

On Error GoTo Err_Handler

If MsgBox("Are you sure you want to delete this record?", _
vbQuestion+vbYesNo, "Delete Record?") _
= vbYes _
Then
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
End If

Exit_Point:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point



Thanks Allen!

Allen,

My subform has the autonumber primary key "CourseID" and if my user
accidentally try to delete the not yet created row, the "End Debug" message
pops up, Is there a code that I can use to not show the "End, Debug" message.

If you use the deletion built into Access, use the form's BeforeDelConfirm
event to display your custom message.

[quoted text clipped - 7 lines]

[quoted text clipped - 13 lines]
use the "Filter By Form" ?