Cancel Subform should do what exactly?

[Mattias Jonsson]

I have made a little form that has a few subforms on it. I would like to be
able to load a subform by setting the SourceObject property (don't know at
design time exactly which form will be the subform). If the subform
Form_Load is cancelled, I would like to be able to detect it from the parent
form. Seems like a reasonable thing to do.

Now to what Access (2002, SP 3) does. Here's how to reproduce it

Create frmParent with a subform control on it called TheSubform and a button
called cmdTest. Paste the following code:
**Code start**
Option Compare Database
Option Explicit

Private Sub cmdTest_Click()
Me.TheSubform.SourceObject = "frmChild"
MsgBox Me.TheSubform.Form.TheTestFunction
End Sub
**Code end**

Create frmChild without any controls but with the following code
**Code start**
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
Cancel = True
End Sub

Public Function TheTestFunction() As String
TheTestFunction = "Doh!"
End Function
**Code end**

Now, make sure both forms are closed. Open the parent form and click the
button.

Hope this will be as intriguing to somebody else as it has been to me.
Behaves the same way in Access 2000, by the way (not tested in earlier
versions). I can work around this problem so I'm not desperate for a
solution but it just seems really weird. Is there perhaps a better way to
load a subform at runtime?

Thanks,
Mattias Jonsson

 

[Mattias Jonsson]

Anybody?

 

[Dirk Goldgar]

I have made a little form that has a few subforms on it. I would like
to be able to load a subform by setting the SourceObject property
(don't know at design time exactly which form will be the subform).
If the subform Form_Load is cancelled, I would like to be able to
detect it from the parent form. Seems like a reasonable thing to do.

Now to what Access (2002, SP 3) does. Here's how to reproduce it

Create frmParent with a subform control on it called TheSubform and a
button called cmdTest. Paste the following code:
**Code start**
Option Compare Database
Option Explicit

Private Sub cmdTest_Click()
Me.TheSubform.SourceObject = "frmChild"
MsgBox Me.TheSubform.Form.TheTestFunction
End Sub
**Code end**

Create frmChild without any controls but with the following code
**Code start**
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
Cancel = True
End Sub

Public Function TheTestFunction() As String
TheTestFunction = "Doh!"
End Function
**Code end**

Now, make sure both forms are closed. Open the parent form and click
the button.

Hope this will be as intriguing to somebody else as it has been to me.
Behaves the same way in Access 2000, by the way (not tested in earlier
versions). I can work around this problem so I'm not desperate for a
solution but it just seems really weird. Is there perhaps a better
way to load a subform at runtime?

Thanks,
Mattias Jonsson

Hmm, you don't say what behavior you're seeing, but for me the call to
TheTestFunction works just fine, and the subform appears to be present
in memory, though not displayed on the form. If I make the subform into
a bound form, though, no records are loaded when I cancel its Open
event, and Me.TheSubform.Form.RecordCount returns 0. I don't know if
that helps you. It sure looks as though you can't cancel the Open event
of a subform.

 

[Mattias Jonsson]

Dirk,
Thanks for looking into this thing. I'll try to explain more without too
much text.

I want to be able to be able to cancel the subform by placing some logic in
Form_Open that the parent form does not need to know about. Then I would be
doing A in the parent form if the subform loaded and B if it did not.

What I'm actually working on is a generic wizard form with a bunch of
subforms that can be filled with various subforms depending on the context
in which it's used. I'd like to put the logic for if a subform is allowed to
load in the subform rather than the parent form. Like I said in the original
post I can work around this but it's not as pretty.

I would have thought that setting Me.TheSubform.SourceObject would raise an
error if the subform Form_Open was cancelled. The result seems to rather be
that the SourceObject string remains and the subform ends up somewhere
inbetween loaded and not loaded.

Can you think of any property of the subform that I can check from the
parent form that would indicate if it loaded or not? The subforms could
possibly be either bound or unbound.

Thanks,
Mattias Jonsson

 

[Dirk Goldgar]

What I'm actually working on is a generic wizard form with a bunch of
subforms that can be filled with various subforms depending on the
context in which it's used. I'd like to put the logic for if a
subform is allowed to load in the subform rather than the parent
form. Like I said in the original post I can work around this but
it's not as pretty.

I would have thought that setting Me.TheSubform.SourceObject would
raise an error if the subform Form_Open was cancelled. The result
seems to rather be that the SourceObject string remains and the
subform ends up somewhere inbetween loaded and not loaded.

Can you think of any property of the subform that I can check from the
parent form that would indicate if it loaded or not? The subforms
could possibly be either bound or unbound.

I wouldn't care to guarantee this, but in my tests with both bound and
unbound subforms, the subform.form's SelLeft and SelTop properties were
both 0 if the subform's Open event was canceled, and 1 if the subform
was allowed to load normally.

 

[Mattias Jonsson]

Dirk,
That works great! I'm amazed you figured it out. SelTop is 0 if the subform
is cancelled and 1 if it opens. SelLeft is the same (0) either way. I have
no idea why this would work (should have to do with datasheet mode?), but it
does. Both for bound and unbound forms.

One interesting quirk is that if the subform is in datasheet mode then a
reference to Me.TheSubform.Form will raise the error 2467 "...refers to an
object that is closed..." I can detect that so it's fine, just unexpected.

Thanks,
Mattias Jonsson

 

[Dirk Goldgar]

Dirk,
That works great! I'm amazed you figured it out.

Heh, I just opened the form in the various states, dumped all its
properties each time, and compared the lists.

SelTop is 0 if the
subform is cancelled and 1 if it opens. SelLeft is the same (0)
either way. I have no idea why this would work (should have to do
with datasheet mode?), but it does.

SelTop, and SelHeight can also be useful with continuous forms.

One interesting quirk is that if the subform is in datasheet mode
then a reference to Me.TheSubform.Form will raise the error 2467
"...refers to an object that is closed..." I can detect that so it's
fine, just unexpected.

Huh. I hadn't tried a datasheet form, and I'm surprised that it behaves
differently. Thanks for the warning.

 

[Mattias Jonsson]

Dirk,
Well this one had me stumped for a few days (ask my wife). Thanks for
helping out! I will try to pay back to the community.

Mattias Jonsson