Hi,
I have a form with a subform containing the child records. This sub form
also contains a sub form with another level of child records
(grand-children). Now, I want my personalized Delete button (placed in the
form header) to delete oinly the record that is selected, the grand-parent,
the parent or the child according to the user's selection.
Can that be done?
How do I recognize or check which record is selected.
The code I wrote for first try deletes the grand parent record when the
grand child is selected.
Private Sub cmdDelete_Click()
strMsg = "Delete this record?"
intStyle = vbQuestion + vbYesNo + vbDefaultButton2
intResponse = MsgBox(strMsg, intStyle, strcTitle)
If intResponse = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
Else
MsgBox "Operation cancelled"
End If
End Sub
I set cascade delete on all related tables.
I use Access 2000 (actually my users).
Thanks for your help.
--
Jac Tremblay

If you place this command button on the main form, Access moves focus to the
main form *before* the button's Click event fires. Any attempt to determine
which level subform had focus, and how many records were selected there is
doomed before you begin.

You could use a button that is not on the form, e.g. a toolbar button. In
fact, there is already a Delete button on the toolbar that does this without
needing any code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jac Tremblay" <(E-Mail Removed)> wrote in message
news:F21E6156-DC89-4BC9-BBD8-(E-Mail Removed)...
> Hi,
> I have a form with a subform containing the child records. This sub form
> also contains a sub form with another level of child records
> (grand-children). Now, I want my personalized Delete button (placed in the
> form header) to delete oinly the record that is selected, the
> grand-parent,
> the parent or the child according to the user's selection.
> Can that be done?
> How do I recognize or check which record is selected.
> The code I wrote for first try deletes the grand parent record when the
> grand child is selected.
> Private Sub cmdDelete_Click()
> strMsg = "Delete this record?"
> intStyle = vbQuestion + vbYesNo + vbDefaultButton2
> intResponse = MsgBox(strMsg, intStyle, strcTitle)
> If intResponse = vbYes Then
> DoCmd.RunCommand acCmdDeleteRecord
> Else
> MsgBox "Operation cancelled"
> End If
> End Sub
> I set cascade delete on all related tables.
> I use Access 2000 (actually my users).
> Thanks for your help.
> --
> Jac Tremblay

Hi Allen,
What I need is a button on the header's main form and I want to know if,
with such a button, I would be able to determine which record is selected,
the grand-parent, the parent or the child.
The reason why I want to place that button on the main form's header is that
all the buttons that the application needs are placed there. Why would I want
to place a delete button alone on any of the sub forms?
I eventually will hide the Access toolbars and menus so the user will not be
able to use them when the application runs.
And I do want to code something for the Delete button. I want to determine
which record is selected before I delete anything. The Record selector is
activated on all the forms. So, if I can detect which record is selected, I
can code something like this:
If GrandParent.selected then
Delete from GrandParent where lngIdGrandParent = ...
Elseif Parent.Selected then
Delete from Parent where lngIdParent = ...
Else
Delete from Child where lngIdChild = ...
End if
Isn't that a good idea? Is that possible? Thank you for your comment. I
appreciate it.
--
Jac Tremblay


"Allen Browne" wrote:

> If you place this command button on the main form, Access moves focus to the
> main form *before* the button's Click event fires. Any attempt to determine
> which level subform had focus, and how many records were selected there is
> doomed before you begin.
>
> You could use a button that is not on the form, e.g. a toolbar button. In
> fact, there is already a Delete button on the toolbar that does this without
> needing any code.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jac Tremblay" <(E-Mail Removed)> wrote in message
> news:F21E6156-DC89-4BC9-BBD8-(E-Mail Removed)...
> > Hi,
> > I have a form with a subform containing the child records. This sub form
> > also contains a sub form with another level of child records
> > (grand-children). Now, I want my personalized Delete button (placed in the
> > form header) to delete oinly the record that is selected, the
> > grand-parent,
> > the parent or the child according to the user's selection.
> > Can that be done?
> > How do I recognize or check which record is selected.
> > The code I wrote for first try deletes the grand parent record when the
> > grand child is selected.
> > Private Sub cmdDelete_Click()
> > strMsg = "Delete this record?"
> > intStyle = vbQuestion + vbYesNo + vbDefaultButton2
> > intResponse = MsgBox(strMsg, intStyle, strcTitle)
> > If intResponse = vbYes Then
> > DoCmd.RunCommand acCmdDeleteRecord
> > Else
> > MsgBox "Operation cancelled"
> > End If
> > End Sub
> > I set cascade delete on all related tables.
> > I use Access 2000 (actually my users).
> > Thanks for your help.
> > --
> > Jac Tremblay
>
>
>

Not possible.

You can mess with Screen.Previous control to see if, before the button was
clicked, focus was in the subform. However, you cannot know how many records
were previously selected in the (sub)subform, because the selection is
already lost.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jac Tremblay" <(E-Mail Removed)> wrote in message
news:BB151342-1F56-427B-B064-(E-Mail Removed)...
> Hi Allen,
> What I need is a button on the header's main form and I want to know if,
> with such a button, I would be able to determine which record is selected,
> the grand-parent, the parent or the child.
> The reason why I want to place that button on the main form's header is
> that
> all the buttons that the application needs are placed there. Why would I
> want
> to place a delete button alone on any of the sub forms?
> I eventually will hide the Access toolbars and menus so the user will not
> be
> able to use them when the application runs.
> And I do want to code something for the Delete button. I want to determine
> which record is selected before I delete anything. The Record selector is
> activated on all the forms. So, if I can detect which record is selected,
> I
> can code something like this:
> If GrandParent.selected then
> Delete from GrandParent where lngIdGrandParent = ...
> Elseif Parent.Selected then
> Delete from Parent where lngIdParent = ...
> Else
> Delete from Child where lngIdChild = ...
> End if
> Isn't that a good idea? Is that possible? Thank you for your comment. I
> appreciate it.
> --
> Jac Tremblay
>
>
> "Allen Browne" wrote:
>
>> If you place this command button on the main form, Access moves focus to
>> the
>> main form *before* the button's Click event fires. Any attempt to
>> determine
>> which level subform had focus, and how many records were selected there
>> is
>> doomed before you begin.
>>
>> You could use a button that is not on the form, e.g. a toolbar button. In
>> fact, there is already a Delete button on the toolbar that does this
>> without
>> needing any code.
>>
>> "Jac Tremblay" <(E-Mail Removed)> wrote in message
>> news:F21E6156-DC89-4BC9-BBD8-(E-Mail Removed)...
>> > Hi,
>> > I have a form with a subform containing the child records. This sub
>> > form
>> > also contains a sub form with another level of child records
>> > (grand-children). Now, I want my personalized Delete button (placed in
>> > the
>> > form header) to delete oinly the record that is selected, the
>> > grand-parent,
>> > the parent or the child according to the user's selection.
>> > Can that be done?
>> > How do I recognize or check which record is selected.
>> > The code I wrote for first try deletes the grand parent record when the
>> > grand child is selected.
>> > Private Sub cmdDelete_Click()
>> > strMsg = "Delete this record?"
>> > intStyle = vbQuestion + vbYesNo + vbDefaultButton2
>> > intResponse = MsgBox(strMsg, intStyle, strcTitle)
>> > If intResponse = vbYes Then
>> > DoCmd.RunCommand acCmdDeleteRecord
>> > Else
>> > MsgBox "Operation cancelled"
>> > End If
>> > End Sub
>> > I set cascade delete on all related tables.
>> > I use Access 2000 (actually my users).
>> > Thanks for your help.
>> > --
>> > Jac Tremblay

Hi again Allen,
Ok, but what is the Form's RecordSelectors property used for? According to
my tests, all I can get out of it is True or False, whether the form has the
property set to Yes or no.
Is it possible to determine,when a button is clkicked, which field has the
focus on a form or suborm? If it is, then I can determine which form or
subform to work with (and therefore which table occurrence to delete).

Thanks again for your comments.
--
Jac Tremblay


"Allen Browne" wrote:

> Not possible.
>
> You can mess with Screen.Previous control to see if, before the button was
> clicked, focus was in the subform. However, you cannot know how many records
> were previously selected in the (sub)subform, because the selection is
> already lost.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jac Tremblay" <(E-Mail Removed)> wrote in message
> news:BB151342-1F56-427B-B064-(E-Mail Removed)...
> > Hi Allen,
> > What I need is a button on the header's main form and I want to know if,
> > with such a button, I would be able to determine which record is selected,
> > the grand-parent, the parent or the child.
> > The reason why I want to place that button on the main form's header is
> > that
> > all the buttons that the application needs are placed there. Why would I
> > want
> > to place a delete button alone on any of the sub forms?
> > I eventually will hide the Access toolbars and menus so the user will not
> > be
> > able to use them when the application runs.
> > And I do want to code something for the Delete button. I want to determine
> > which record is selected before I delete anything. The Record selector is
> > activated on all the forms. So, if I can detect which record is selected,
> > I
> > can code something like this:
> > If GrandParent.selected then
> > Delete from GrandParent where lngIdGrandParent = ...
> > Elseif Parent.Selected then
> > Delete from Parent where lngIdParent = ...
> > Else
> > Delete from Child where lngIdChild = ...
> > End if
> > Isn't that a good idea? Is that possible? Thank you for your comment. I
> > appreciate it.
> > --
> > Jac Tremblay
> >
> >
> > "Allen Browne" wrote:
> >
> >> If you place this command button on the main form, Access moves focus to
> >> the
> >> main form *before* the button's Click event fires. Any attempt to
> >> determine
> >> which level subform had focus, and how many records were selected there
> >> is
> >> doomed before you begin.
> >>
> >> You could use a button that is not on the form, e.g. a toolbar button. In
> >> fact, there is already a Delete button on the toolbar that does this
> >> without
> >> needing any code.
> >>
> >> "Jac Tremblay" <(E-Mail Removed)> wrote in message
> >> news:F21E6156-DC89-4BC9-BBD8-(E-Mail Removed)...
> >> > Hi,
> >> > I have a form with a subform containing the child records. This sub
> >> > form
> >> > also contains a sub form with another level of child records
> >> > (grand-children). Now, I want my personalized Delete button (placed in
> >> > the
> >> > form header) to delete oinly the record that is selected, the
> >> > grand-parent,
> >> > the parent or the child according to the user's selection.
> >> > Can that be done?
> >> > How do I recognize or check which record is selected.
> >> > The code I wrote for first try deletes the grand parent record when the
> >> > grand child is selected.
> >> > Private Sub cmdDelete_Click()
> >> > strMsg = "Delete this record?"
> >> > intStyle = vbQuestion + vbYesNo + vbDefaultButton2
> >> > intResponse = MsgBox(strMsg, intStyle, strcTitle)
> >> > If intResponse = vbYes Then
> >> > DoCmd.RunCommand acCmdDeleteRecord
> >> > Else
> >> > MsgBox "Operation cancelled"
> >> > End If
> >> > End Sub
> >> > I set cascade delete on all related tables.
> >> > I use Access 2000 (actually my users).
> >> > Thanks for your help.
> >> > --
> >> > Jac Tremblay
>
>
>

Regardless of whether a form has a record selector or not, you can read the
value of the current record from the form.

What you cannot do is determine how many records were selected in the
subform (in Continuous or Datasheet view) before the button on the main form
was clicked.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jac Tremblay" <(E-Mail Removed)> wrote in message
news:07587CF9-6679-4D71-9226-(E-Mail Removed)...
> Hi again Allen,
> Ok, but what is the Form's RecordSelectors property used for? According to
> my tests, all I can get out of it is True or False, whether the form has
> the
> property set to Yes or no.
> Is it possible to determine,when a button is clkicked, which field has the
> focus on a form or suborm? If it is, then I can determine which form or
> subform to work with (and therefore which table occurrence to delete).
>
> Thanks again for your comments.
> --
> Jac Tremblay
>
>
> "Allen Browne" wrote:
>
>> Not possible.
>>
>> You can mess with Screen.Previous control to see if, before the button
>> was
>> clicked, focus was in the subform. However, you cannot know how many
>> records
>> were previously selected in the (sub)subform, because the selection is
>> already lost.
>>
>> "Jac Tremblay" <(E-Mail Removed)> wrote in message
>> news:BB151342-1F56-427B-B064-(E-Mail Removed)...
>> > Hi Allen,
>> > What I need is a button on the header's main form and I want to know
>> > if,
>> > with such a button, I would be able to determine which record is
>> > selected,
>> > the grand-parent, the parent or the child.
>> > The reason why I want to place that button on the main form's header is
>> > that
>> > all the buttons that the application needs are placed there. Why would
>> > I
>> > want
>> > to place a delete button alone on any of the sub forms?
>> > I eventually will hide the Access toolbars and menus so the user will
>> > not
>> > be
>> > able to use them when the application runs.
>> > And I do want to code something for the Delete button. I want to
>> > determine
>> > which record is selected before I delete anything. The Record selector
>> > is
>> > activated on all the forms. So, if I can detect which record is
>> > selected,
>> > I
>> > can code something like this:
>> > If GrandParent.selected then
>> > Delete from GrandParent where lngIdGrandParent = ...
>> > Elseif Parent.Selected then
>> > Delete from Parent where lngIdParent = ...
>> > Else
>> > Delete from Child where lngIdChild = ...
>> > End if
>> > Isn't that a good idea? Is that possible? Thank you for your comment. I
>> > appreciate it.
>> > --
>> > Jac Tremblay
>> >
>> >
>> > "Allen Browne" wrote:
>> >
>> >> If you place this command button on the main form, Access moves focus
>> >> to
>> >> the
>> >> main form *before* the button's Click event fires. Any attempt to
>> >> determine
>> >> which level subform had focus, and how many records were selected
>> >> there
>> >> is
>> >> doomed before you begin.
>> >>
>> >> You could use a button that is not on the form, e.g. a toolbar button.
>> >> In
>> >> fact, there is already a Delete button on the toolbar that does this
>> >> without
>> >> needing any code.
>> >>
>> >> "Jac Tremblay" <(E-Mail Removed)> wrote in message
>> >> news:F21E6156-DC89-4BC9-BBD8-(E-Mail Removed)...
>> >> > Hi,
>> >> > I have a form with a subform containing the child records. This sub
>> >> > form
>> >> > also contains a sub form with another level of child records
>> >> > (grand-children). Now, I want my personalized Delete button (placed
>> >> > in
>> >> > the
>> >> > form header) to delete oinly the record that is selected, the
>> >> > grand-parent,
>> >> > the parent or the child according to the user's selection.
>> >> > Can that be done?
>> >> > How do I recognize or check which record is selected.
>> >> > The code I wrote for first try deletes the grand parent record when
>> >> > the
>> >> > grand child is selected.
>> >> > Private Sub cmdDelete_Click()
>> >> > strMsg = "Delete this record?"
>> >> > intStyle = vbQuestion + vbYesNo + vbDefaultButton2
>> >> > intResponse = MsgBox(strMsg, intStyle, strcTitle)
>> >> > If intResponse = vbYes Then
>> >> > DoCmd.RunCommand acCmdDeleteRecord
>> >> > Else
>> >> > MsgBox "Operation cancelled"
>> >> > End If
>> >> > End Sub
>> >> > I set cascade delete on all related tables.
>> >> > I use Access 2000 (actually my users).
>> >> > Thanks for your help.

Hi allen,
I understand your point, but I am sure there is a solution to my problem.
If I cannot determine which record has the focus, can I determine if the
Form.RecordSelectors is activated or selected or something else?
Thanks
--
Jac Tremblay


"Allen Browne" wrote:

> If you place this command button on the main form, Access moves focus to the
> main form *before* the button's Click event fires. Any attempt to determine
> which level subform had focus, and how many records were selected there is
> doomed before you begin.
>
> You could use a button that is not on the form, e.g. a toolbar button. In
> fact, there is already a Delete button on the toolbar that does this without
> needing any code.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jac Tremblay" <(E-Mail Removed)> wrote in message
> news:F21E6156-DC89-4BC9-BBD8-(E-Mail Removed)...
> > Hi,
> > I have a form with a subform containing the child records. This sub form
> > also contains a sub form with another level of child records
> > (grand-children). Now, I want my personalized Delete button (placed in the
> > form header) to delete oinly the record that is selected, the
> > grand-parent,
> > the parent or the child according to the user's selection.
> > Can that be done?
> > How do I recognize or check which record is selected.
> > The code I wrote for first try deletes the grand parent record when the
> > grand child is selected.
> > Private Sub cmdDelete_Click()
> > strMsg = "Delete this record?"
> > intStyle = vbQuestion + vbYesNo + vbDefaultButton2
> > intResponse = MsgBox(strMsg, intStyle, strcTitle)
> > If intResponse = vbYes Then
> > DoCmd.RunCommand acCmdDeleteRecord
> > Else
> > MsgBox "Operation cancelled"
> > End If
> > End Sub
> > I set cascade delete on all related tables.
> > I use Access 2000 (actually my users).
> > Thanks for your help.
> > --
> > Jac Tremblay
>
>
>

There is no problem determining the current record in any bound form.

I don't think there is anything else I can add.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jac Tremblay" <(E-Mail Removed)> wrote in message
news:CF6EB324-8D5E-40F3-8ED6-(E-Mail Removed)...
> Hi allen,
> I understand your point, but I am sure there is a solution to my problem.
> If I cannot determine which record has the focus, can I determine if the
> Form.RecordSelectors is activated or selected or something else?
> Thanks
> --
> Jac Tremblay
>
>
> "Allen Browne" wrote:
>
>> If you place this command button on the main form, Access moves focus to
>> the
>> main form *before* the button's Click event fires. Any attempt to
>> determine
>> which level subform had focus, and how many records were selected there
>> is
>> doomed before you begin.
>>
>> You could use a button that is not on the form, e.g. a toolbar button. In
>> fact, there is already a Delete button on the toolbar that does this
>> without
>> needing any code.
>>
>> "Jac Tremblay" <(E-Mail Removed)> wrote in message
>> news:F21E6156-DC89-4BC9-BBD8-(E-Mail Removed)...
>> > Hi,
>> > I have a form with a subform containing the child records. This sub
>> > form
>> > also contains a sub form with another level of child records
>> > (grand-children). Now, I want my personalized Delete button (placed in
>> > the
>> > form header) to delete oinly the record that is selected, the
>> > grand-parent,
>> > the parent or the child according to the user's selection.
>> > Can that be done?
>> > How do I recognize or check which record is selected.
>> > The code I wrote for first try deletes the grand parent record when the
>> > grand child is selected.
>> > Private Sub cmdDelete_Click()
>> > strMsg = "Delete this record?"
>> > intStyle = vbQuestion + vbYesNo + vbDefaultButton2
>> > intResponse = MsgBox(strMsg, intStyle, strcTitle)
>> > If intResponse = vbYes Then
>> > DoCmd.RunCommand acCmdDeleteRecord
>> > Else
>> > MsgBox "Operation cancelled"
>> > End If
>> > End Sub
>> > I set cascade delete on all related tables.
>> > I use Access 2000 (actually my users).
>> > Thanks for your help.