Unbound subform is reset

[Guest]

I have an unbound subform that for some reason gets reloaded when the main
form is requeried.

Has anyone else experienced this problem?

The DB was converted from Access 2003 where this problem did not occurr. The
same error occurrs on two different forms in my application. If I use the
subform as a separate form in it's own window then I will not have this
problem.

This is quite annoying since my reason for using a unbound subform is so
that it would retain it's state regardless of what happends on the main form.
Now all user changes to the subform are lost since the subform is reloaded
(yes - I have stepped through the code, it is really reloaded).

 

[Allen Browne]

Mats, what's in the LinkMasterFields/LinkChildFields properties of the
subform *control*?

If there is anything there, Access will reload the subform whenever the main
form record changes.

If that's not the issue, tell us a little more. Presumably this is A2007.
What view is the main form in? Single Form? Layout? Split?

How did you "step through the code" to verify it is really reloaded?
What event is this?

 

[Guest]

Here are answers to your questions (and some extra info):

1. LinkMasterFields/LinkChildFields properties are empty. I also tried using
dummy values, but that did of course not help.

2. Yes - this is Access 2007.

3. The main form is in Single Form layout.

4. Debug.print for subform events (not the subform container, but the actual
form that is within the subform container). When the main form is first
opened I see the "Open" and "Load" event being fired off. When my problem
occurrs I see the "Close" event followed by the "Open" event. Strange this is
that I do not see "Load" or "Unload" events.

Extra:

I keep a reference to the subform like this

Private WithEvents m_frmEventTree As Form_frmEventTree

in order to be able to capture a custom event that the child form raises. I
set this reference in the main forms "Open" event like this

Set m_frmEventTree = Me.ctlEvent.Form

In a sub within the main form I do this:

....

Me.lstParticipants.Requery
Me.RecordSource = Me.lstParticipants.RowSource 'here I still have a
reference
Me.Requery '... and now it's gone.

.....

Before the recordsource is set the object m_frmEventTree has a reference to
the subform, as soon as the recordsource is set the reference is lost.

Thanks for looking into this!

Br,
Mats

 

[Allen Browne]

Mats, I've just created a simple sample that does not have the problem you
describe:
http://allenbrowne.com/temp/SubformReset.zip

The main form and subform log the Open, Load, and Current events.
The command button reassigns the subform's RecordSource.
The logging does not show the subform being re-opened.

The main form's Load event also sets a module-level variable to the form in
the subform control. The logging does not show this variable becoming
Nothing again (i.e. it is Nothing only in the main form's Open event, prior
to being set.)

It's a rather basic example, so I guess there is another factor in the code
you are working with.

 

[Guest]

Sorry, I might have been a bit unclear. Set the main forms recordsource.
Using your example, I made this change:


Private Sub cmdAssign_Click()
Dim strSql As String

Randomize
Call LogEvent("cmdAssign_Click")
strSql = "SELECT Products.* FROM Products WHERE CategoryID = " & Int(7 *
Rnd() + 1) & ";"
Me.frmProduct.Form.RecordSource = strSql

Dim str As String
str = Me.RecordSource
Me.RecordSource = ""
Me.RecordSource = str
Me.Requery

End Sub

Now it blows up just nicely


Br,
Mats

 

[michi Kremm]

my english is not so good:-(

i have the same problem, i think Allen cant see the problem because the application is an mdb not a accdb, but you can see a example here
http://members.vol.at/kremmel/2007Subformreset.zip
try formular1
or create your application in accdb than you can see the problem...



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[michi Kremm]

my english is not so good:-(

i have the same problem, i think Allen cant see the problem because the application is an mdb not a accdb, but you can see a example here
http://members.vol.at/kremmel/2007Subformreset.zip
try formular1
or create your application in accdb than you can see the problem...



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Allen Browne]

Okay, got it. The issue is that when you reset the RecordSource of the main
form, Access 2007 closes and reopens the subform, whereas previous versions
do not. As a consequence, if you were relying on a reference to the object,
the reference is invalid once A2007 closes and reopens the subform. (This
occurs whether there is anything in LinkMasterFields/LinkChildFields or
not.)

This is the kind of difference that can occur in new versions. MS certainly
altered the way subforms are handled. In previous versions, there were
several bugs associated with applying a Filter/OrderBy to both the main form
and subform:
http://allenbrowne.com/bug-02.html
These bugs are fixed in A2007. In doing so, it would be surprising if they
changed the way subforms are handled.

Your immediate problem is that your existing code is now broken, and so you
will need a different way to handle this. One option might be to use the
main form's Filter instead of setting its RecordSource. A quick test
suggests that this does not cause the subform to close and reload. Another
option might be to avoid the module-level variable, and just refer to the
form in the subform control directly. I can imagine scenarios where neither
of those ideas would be suitable: it will depend on why you have taken the
approach you have.

Not sure if that's much help, but the issue you describe is a real
difference over previous versions.

 

[Guest]

OK - always nice to have a problem confirmed by a 3rd party. Then you know
it's not just caused by yoour own shitty code

Thanks for your efforts. I must go with the filter-approach as I do not want
to have the subform reloaded as the user then looses all their selections on
the subform. The reference is not the biggest proble. I could always reset
that after I set the recordsource (I need the reference to capture an event
from the subform).

My reason for not using filters is just because I like to image a massive
database with so many records that filtering in the computers memory is not
feasible. This has, however, nothing to do with the real-life use of my app...


Thanks again,
Mats