cancel subform=cancel mainform 
Author Message
 cancel subform=cancel mainform

seams that I should be able to figure this myself....But I can't.

Form1 just holds form2. Form1 has no record source. From2 recordsource is a
parameter query. If user clicks cancel on parameter box then form1 still
opens. How can I cancel the open event of form1??

Thanks much,
Paul



Tue, 26 Jul 2005 22:58:23 GMT  
 cancel subform=cancel mainform
The subform is opened before the main, so you may be able to cancel the main
form's Open event if the sub has no records:

Private Form_Open(Cancel As Integer)
    If Me.[MySubform].Form.RecordsetClone.RecordCount = 0 Then
        Cancel = True
    End If
End Sub

--
Allen Browne - Microsoft MVP (Most Valuable Professional)
Allen Browne's Database And Training - Perth, Western Australia.
Tips for MS Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> seams that I should be able to figure this myself....But I can't.

> Form1 just holds form2. Form1 has no record source. From2 recordsource is
a
> parameter query. If user clicks cancel on parameter box then form1 still
> opens. How can I cancel the open event of form1??

> Thanks much,
> Paul



Tue, 26 Jul 2005 23:20:13 GMT  
 cancel subform=cancel mainform
Allen, I already tried that. It does not work, because canceling the
parameter box cancels the open event of the subform that called the query. I
also tried IsLoaded and it did not work.

P


Quote:
> The subform is opened before the main, so you may be able to cancel the
main
> form's Open event if the sub has no records:

> Private Form_Open(Cancel As Integer)
>     If Me.[MySubform].Form.RecordsetClone.RecordCount = 0 Then
>         Cancel = True
>     End If
> End Sub

> --
> Allen Browne - Microsoft MVP (Most Valuable Professional)
> Allen Browne's Database And Training - Perth, Western Australia.
> Tips for MS Access users - http://allenbrowne.com/tips.html
> Reply to the newsgroup. (Email address has spurious "_SpamTrap")



> > seams that I should be able to figure this myself....But I can't.

> > Form1 just holds form2. Form1 has no record source. From2 recordsource
is
> a
> > parameter query. If user clicks cancel on parameter box then form1 still
> > opens. How can I cancel the open event of form1??

> > Thanks much,
> > Paul



Tue, 26 Jul 2005 23:30:40 GMT  
 cancel subform=cancel mainform
Hmm. The timing is really messy here. Theoretically, if a user gets
distracted that parameter box could be sitting on the screen for a long
time.

Is there another way to approach? Why is there a subform with nothing in the
main form? Could the main form's purpose be replaced by the Form Header
section of the subform (in continuous view)? Could the parameter be replaced
by a text box in the Form Header section or main form? Or perhaps with a
small unbound form that could use DLookup() to see if the WHERE clause will
result in any data, and if not don't open the form?

--
Allen Browne - Microsoft MVP (Most Valuable Professional)
Allen Browne's Database And Training - Perth, Western Australia.
Tips for MS Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> Allen, I already tried that. It does not work, because canceling the
> parameter box cancels the open event of the subform that called the query.
I
> also tried IsLoaded and it did not work.

> P



> > The subform is opened before the main, so you may be able to cancel the
> main
> > form's Open event if the sub has no records:

> > Private Form_Open(Cancel As Integer)
> >     If Me.[MySubform].Form.RecordsetClone.RecordCount = 0 Then
> >         Cancel = True
> >     End If
> > End Sub

> > --
> > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > Allen Browne's Database And Training - Perth, Western Australia.
> > Tips for MS Access users - http://allenbrowne.com/tips.html
> > Reply to the newsgroup. (Email address has spurious "_SpamTrap")



> > > seams that I should be able to figure this myself....But I can't.

> > > Form1 just holds form2. Form1 has no record source. From2 recordsource
> is
> > a
> > > parameter query. If user clicks cancel on parameter box then form1
still
> > > opens. How can I cancel the open event of form1??

> > > Thanks much,
> > > Paul



Tue, 26 Jul 2005 23:47:32 GMT  
 cancel subform=cancel mainform
Yes I could replace the parameter box with a small unbound form, but I was
hopping for another fix.

The mainform holding the subform is a way to size a continuous form using
Peter's Shrinker-stretcher.

Thanks anyway,
Paul

Quote:
> Hmm. The timing is really messy here. Theoretically, if a user gets
> distracted that parameter box could be sitting on the screen for a long
> time.

> Is there another way to approach? Why is there a subform with nothing in
the
> main form? Could the main form's purpose be replaced by the Form Header
> section of the subform (in continuous view)? Could the parameter be
replaced
> by a text box in the Form Header section or main form? Or perhaps with a
> small unbound form that could use DLookup() to see if the WHERE clause
will
> result in any data, and if not don't open the form?

> --
> Allen Browne - Microsoft MVP (Most Valuable Professional)
> Allen Browne's Database And Training - Perth, Western Australia.
> Tips for MS Access users - http://allenbrowne.com/tips.html
> Reply to the newsgroup. (Email address has spurious "_SpamTrap")



> > Allen, I already tried that. It does not work, because canceling the
> > parameter box cancels the open event of the subform that called the
query.
> I
> > also tried IsLoaded and it did not work.

> > P



> > > The subform is opened before the main, so you may be able to cancel
the
> > main
> > > form's Open event if the sub has no records:

> > > Private Form_Open(Cancel As Integer)
> > >     If Me.[MySubform].Form.RecordsetClone.RecordCount = 0 Then
> > >         Cancel = True
> > >     End If
> > > End Sub

> > > --
> > > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > > Allen Browne's Database And Training - Perth, Western Australia.
> > > Tips for MS Access users - http://allenbrowne.com/tips.html
> > > Reply to the newsgroup. (Email address has spurious "_SpamTrap")



> > > > seams that I should be able to figure this myself....But I can't.

> > > > Form1 just holds form2. Form1 has no record source. From2
recordsource
> > is
> > > a
> > > > parameter query. If user clicks cancel on parameter box then form1
> still
> > > > opens. How can I cancel the open event of form1??

> > > > Thanks much,
> > > > Paul



Wed, 27 Jul 2005 00:26:29 GMT  
 cancel subform=cancel mainform
Interesting.  This seems to work:

'----- code for main form's Open event -----
Private Sub Form_Open(Cancel As Integer)

    On Error Resume Next
    If Not IsObject(Me.Subform1.Form) Then
        Cancel = True
    End If

End Sub

'----- end of code -----

(where "Subform1" is the name of the subform control on the main
form).

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Quote:
> Yes I could replace the parameter box with a small unbound form, but
I was
> hopping for another fix.

> The mainform holding the subform is a way to size a continuous form
using
> Peter's Shrinker-stretcher.

> Thanks anyway,
> Paul


> > Hmm. The timing is really messy here. Theoretically, if a user
gets
> > distracted that parameter box could be sitting on the screen for a
long
> > time.

> > Is there another way to approach? Why is there a subform with
nothing in
> the
> > main form? Could the main form's purpose be replaced by the Form
Header
> > section of the subform (in continuous view)? Could the parameter
be
> replaced
> > by a text box in the Form Header section or main form? Or perhaps
with a
> > small unbound form that could use DLookup() to see if the WHERE
clause
> will
> > result in any data, and if not don't open the form?

> > --
> > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > Allen Browne's Database And Training - Perth, Western Australia.
> > Tips for MS Access users - http://allenbrowne.com/tips.html
> > Reply to the newsgroup. (Email address has spurious "_SpamTrap")



> > > Allen, I already tried that. It does not work, because canceling
the
> > > parameter box cancels the open event of the subform that called
the
> query.
> > I
> > > also tried IsLoaded and it did not work.

> > > P


message

> > > > The subform is opened before the main, so you may be able to
cancel
> the
> > > main
> > > > form's Open event if the sub has no records:

> > > > Private Form_Open(Cancel As Integer)
> > > >     If Me.[MySubform].Form.RecordsetClone.RecordCount = 0 Then
> > > >         Cancel = True
> > > >     End If
> > > > End Sub

> > > > --
> > > > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > > > Allen Browne's Database And Training - Perth, Western
Australia.
> > > > Tips for MS Access users - http://allenbrowne.com/tips.html
> > > > Reply to the newsgroup. (Email address has spurious
"_SpamTrap")



> > > > > seams that I should be able to figure this myself....But I
can't.

> > > > > Form1 just holds form2. Form1 has no record source. From2
> recordsource
> > > is
> > > > a
> > > > > parameter query. If user clicks cancel on parameter box then
form1
> > still
> > > > > opens. How can I cancel the open event of form1??

> > > > > Thanks much,
> > > > > Paul



Wed, 27 Jul 2005 03:19:46 GMT  
 cancel subform=cancel mainform
Dirk, ur right about my positional reference, thanks. Also thanks for the
solution !

How does this work? Maybe the code that I tried would also work. Your code
errors just like mine did if I try it with out the On Error Resume Next. I
thought this just continued at the next line when ever an error occurred.
But in this case there is no more code. like huh??

Paul


Quote:
> Interesting.  This seems to work:

> '----- code for main form's Open event -----
> Private Sub Form_Open(Cancel As Integer)

>     On Error Resume Next
>     If Not IsObject(Me.Subform1.Form) Then
>         Cancel = True
>     End If

> End Sub

> '----- end of code -----

> (where "Subform1" is the name of the subform control on the main
> form).

> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com

> (please reply to the newsgroup)



> > Yes I could replace the parameter box with a small unbound form, but
> I was
> > hopping for another fix.

> > The mainform holding the subform is a way to size a continuous form
> using
> > Peter's Shrinker-stretcher.

> > Thanks anyway,
> > Paul


> > > Hmm. The timing is really messy here. Theoretically, if a user
> gets
> > > distracted that parameter box could be sitting on the screen for a
> long
> > > time.

> > > Is there another way to approach? Why is there a subform with
> nothing in
> > the
> > > main form? Could the main form's purpose be replaced by the Form
> Header
> > > section of the subform (in continuous view)? Could the parameter
> be
> > replaced
> > > by a text box in the Form Header section or main form? Or perhaps
> with a
> > > small unbound form that could use DLookup() to see if the WHERE
> clause
> > will
> > > result in any data, and if not don't open the form?

> > > --
> > > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > > Allen Browne's Database And Training - Perth, Western Australia.
> > > Tips for MS Access users - http://allenbrowne.com/tips.html
> > > Reply to the newsgroup. (Email address has spurious "_SpamTrap")



> > > > Allen, I already tried that. It does not work, because canceling
> the
> > > > parameter box cancels the open event of the subform that called
> the
> > query.
> > > I
> > > > also tried IsLoaded and it did not work.

> > > > P


> message

> > > > > The subform is opened before the main, so you may be able to
> cancel
> > the
> > > > main
> > > > > form's Open event if the sub has no records:

> > > > > Private Form_Open(Cancel As Integer)
> > > > >     If Me.[MySubform].Form.RecordsetClone.RecordCount = 0 Then
> > > > >         Cancel = True
> > > > >     End If
> > > > > End Sub

> > > > > --
> > > > > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > > > > Allen Browne's Database And Training - Perth, Western
> Australia.
> > > > > Tips for MS Access users - http://allenbrowne.com/tips.html
> > > > > Reply to the newsgroup. (Email address has spurious
> "_SpamTrap")



> > > > > > seams that I should be able to figure this myself....But I
> can't.

> > > > > > Form1 just holds form2. Form1 has no record source. From2
> > recordsource
> > > > is
> > > > > a
> > > > > > parameter query. If user clicks cancel on parameter box then
> form1
> > > still
> > > > > > opens. How can I cancel the open event of form1??

> > > > > > Thanks much,
> > > > > > Paul



Wed, 27 Jul 2005 03:45:27 GMT  
 cancel subform=cancel mainform
Because you cancelled the subform's load process by cancelling its
recordsource query, the subform control's Form property has no form
object to point to.  Therefore, any reference to that property raises
an error.  By saying "On Error Resume Next", we allow processing to
continue past that error so that the IsObject function can return
False;  that is, "ain't no object in there".  That False return value
is then tested and appropriate action is taken.

One could accomplish the same thing by making any reference to the
subform control's Form property and detecting that an error has
occurred.  For example,

    Dim strDummy As String

    On Error Resume Next
    strDummy = Me.Subform1.Form.Name
    If Err.Number <> 0 Then
        Cancel = True
    End If

However, that rather obscures the intent of the code.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Quote:
> Dirk, ur right about my positional reference, thanks. Also thanks
for the
> solution !

> How does this work? Maybe the code that I tried would also work.
Your code
> errors just like mine did if I try it with out the On Error Resume
Next. I
> thought this just continued at the next line when ever an error
occurred.
> But in this case there is no more code. like huh??

> Paul



> > Interesting.  This seems to work:

> > '----- code for main form's Open event -----
> > Private Sub Form_Open(Cancel As Integer)

> >     On Error Resume Next
> >     If Not IsObject(Me.Subform1.Form) Then
> >         Cancel = True
> >     End If

> > End Sub

> > '----- end of code -----

> > (where "Subform1" is the name of the subform control on the main
> > form).

> > --
> > Dirk Goldgar, MS Access MVP
> > www.datagnostics.com

> > (please reply to the newsgroup)



> > > Yes I could replace the parameter box with a small unbound form,
but
> > I was
> > > hopping for another fix.

> > > The mainform holding the subform is a way to size a continuous
form
> > using
> > > Peter's Shrinker-stretcher.

> > > Thanks anyway,
> > > Paul

message

> > > > Hmm. The timing is really messy here. Theoretically, if a user
> > gets
> > > > distracted that parameter box could be sitting on the screen
for a
> > long
> > > > time.

> > > > Is there another way to approach? Why is there a subform with
> > nothing in
> > > the
> > > > main form? Could the main form's purpose be replaced by the
Form
> > Header
> > > > section of the subform (in continuous view)? Could the
parameter
> > be
> > > replaced
> > > > by a text box in the Form Header section or main form? Or
perhaps
> > with a
> > > > small unbound form that could use DLookup() to see if the
WHERE
> > clause
> > > will
> > > > result in any data, and if not don't open the form?

> > > > --
> > > > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > > > Allen Browne's Database And Training - Perth, Western
Australia.
> > > > Tips for MS Access users - http://allenbrowne.com/tips.html
> > > > Reply to the newsgroup. (Email address has spurious
"_SpamTrap")



> > > > > Allen, I already tried that. It does not work, because
canceling
> > the
> > > > > parameter box cancels the open event of the subform that
called
> > the
> > > query.
> > > > I
> > > > > also tried IsLoaded and it did not work.

> > > > > P


> > message

> > > > > > The subform is opened before the main, so you may be able
to
> > cancel
> > > the
> > > > > main
> > > > > > form's Open event if the sub has no records:

> > > > > > Private Form_Open(Cancel As Integer)
> > > > > >     If Me.[MySubform].Form.RecordsetClone.RecordCount = 0
Then
> > > > > >         Cancel = True
> > > > > >     End If
> > > > > > End Sub

> > > > > > --
> > > > > > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > > > > > Allen Browne's Database And Training - Perth, Western
> > Australia.
> > > > > > Tips for MS Access users -

http://allenbrowne.com/tips.html

- Show quoted text -

Quote:
> > > > > > Reply to the newsgroup. (Email address has spurious
> > "_SpamTrap")



> > > > > > > seams that I should be able to figure this myself....But
I
> > can't.

> > > > > > > Form1 just holds form2. Form1 has no record source.
From2
> > > recordsource
> > > > > is
> > > > > > a
> > > > > > > parameter query. If user clicks cancel on parameter box
then
> > form1
> > > > still
> > > > > > > opens. How can I cancel the open event of form1??

> > > > > > > Thanks much,
> > > > > > > Paul



Wed, 27 Jul 2005 04:13:18 GMT  
 cancel subform=cancel mainform
Got it Dirk. Thanks again.
Paul

Quote:
> Because you cancelled the subform's load process by cancelling its
> recordsource query, the subform control's Form property has no form
> object to point to.  Therefore, any reference to that property raises
> an error.  By saying "On Error Resume Next", we allow processing to
> continue past that error so that the IsObject function can return
> False;  that is, "ain't no object in there".  That False return value
> is then tested and appropriate action is taken.

> One could accomplish the same thing by making any reference to the
> subform control's Form property and detecting that an error has
> occurred.  For example,

>     Dim strDummy As String

>     On Error Resume Next
>     strDummy = Me.Subform1.Form.Name
>     If Err.Number <> 0 Then
>         Cancel = True
>     End If

> However, that rather obscures the intent of the code.

> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com

> (please reply to the newsgroup)



> > Dirk, ur right about my positional reference, thanks. Also thanks
> for the
> > solution !

> > How does this work? Maybe the code that I tried would also work.
> Your code
> > errors just like mine did if I try it with out the On Error Resume
> Next. I
> > thought this just continued at the next line when ever an error
> occurred.
> > But in this case there is no more code. like huh??

> > Paul



> > > Interesting.  This seems to work:

> > > '----- code for main form's Open event -----
> > > Private Sub Form_Open(Cancel As Integer)

> > >     On Error Resume Next
> > >     If Not IsObject(Me.Subform1.Form) Then
> > >         Cancel = True
> > >     End If

> > > End Sub

> > > '----- end of code -----

> > > (where "Subform1" is the name of the subform control on the main
> > > form).

> > > --
> > > Dirk Goldgar, MS Access MVP
> > > www.datagnostics.com

> > > (please reply to the newsgroup)



> > > > Yes I could replace the parameter box with a small unbound form,
> but
> > > I was
> > > > hopping for another fix.

> > > > The mainform holding the subform is a way to size a continuous
> form
> > > using
> > > > Peter's Shrinker-stretcher.

> > > > Thanks anyway,
> > > > Paul

> message

> > > > > Hmm. The timing is really messy here. Theoretically, if a user
> > > gets
> > > > > distracted that parameter box could be sitting on the screen
> for a
> > > long
> > > > > time.

> > > > > Is there another way to approach? Why is there a subform with
> > > nothing in
> > > > the
> > > > > main form? Could the main form's purpose be replaced by the
> Form
> > > Header
> > > > > section of the subform (in continuous view)? Could the
> parameter
> > > be
> > > > replaced
> > > > > by a text box in the Form Header section or main form? Or
> perhaps
> > > with a
> > > > > small unbound form that could use DLookup() to see if the
> WHERE
> > > clause
> > > > will
> > > > > result in any data, and if not don't open the form?

> > > > > --
> > > > > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > > > > Allen Browne's Database And Training - Perth, Western
> Australia.
> > > > > Tips for MS Access users - http://allenbrowne.com/tips.html
> > > > > Reply to the newsgroup. (Email address has spurious
> "_SpamTrap")



> > > > > > Allen, I already tried that. It does not work, because
> canceling
> > > the
> > > > > > parameter box cancels the open event of the subform that
> called
> > > the
> > > > query.
> > > > > I
> > > > > > also tried IsLoaded and it did not work.

> > > > > > P


> > > message

> > > > > > > The subform is opened before the main, so you may be able
> to
> > > cancel
> > > > the
> > > > > > main
> > > > > > > form's Open event if the sub has no records:

> > > > > > > Private Form_Open(Cancel As Integer)
> > > > > > >     If Me.[MySubform].Form.RecordsetClone.RecordCount = 0
> Then
> > > > > > >         Cancel = True
> > > > > > >     End If
> > > > > > > End Sub

> > > > > > > --
> > > > > > > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > > > > > > Allen Browne's Database And Training - Perth, Western
> > > Australia.
> > > > > > > Tips for MS Access users -
> http://allenbrowne.com/tips.html
> > > > > > > Reply to the newsgroup. (Email address has spurious
> > > "_SpamTrap")



> > > > > > > > seams that I should be able to figure this myself....But
> I
> > > can't.

> > > > > > > > Form1 just holds form2. Form1 has no record source.
> From2
> > > > recordsource
> > > > > > is
> > > > > > > a
> > > > > > > > parameter query. If user clicks cancel on parameter box
> then
> > > form1
> > > > > still
> > > > > > > > opens. How can I cancel the open event of form1??

> > > > > > > > Thanks much,
> > > > > > > > Paul



Fri, 29 Jul 2005 21:45:13 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. How to Cancel Execution with Cancel Button?

2. Q: When does a Cancel button *mean* Cancel?

3. cancel changes to row in subform.

4. Cancel changes in form w/subform

5. copy from subform to mainform

6. Filter from MainForm to SubForm?

7. calling a sub on the mainform from within a event in the subform

8. Mainform subform with many to many relationship

9. VB4 DBGrid and Mainform subform - Help

10. VB MainForm/SubForm

11. Help - Cancel & Default Property for Cancel button (Repost)

12. End or cancel Close action

 

 
Powered by phpBB® Forum Software