Urgent: Changing RecordSource Causes A Reload of Sub Form Destroying References

[Stewart Berman]

Access 2007

This only happens on some machines. It has occurred on a PC running Windows XP SP3 and a MAC
running Windows. I have been unable to reproduce it on other PCs running Windows XP SP3, Vista or
Windows 7. On the machines that have the problem it always occurs.

I have a form with a sub form control. The control's SourceObject is changed depending on the
function required. On the forms that is used in the control is a utility form that does not
reference data through the form containing the control. One of the functions in the utility form
changes the Filter and RecordSource of the main form. Actually, it saves the values and then clears
them. Later on it restores the values. The code looks like:

Dim sFilter As String
Dim sRecordSource As String

<snip>
sFilter = Me.Parent.Filter
sRecordSource = Me.Parent.RecordSource
Me.Parent.Filter = ""
Me.Parent.RecordSource = ""
If ("\" <> Right$(sBackupDirectory, 1)) Then
sBackupDirectory = sBackupDirectory & "\"
End If

If I step through the code instead of going from the Me.Parent.RecordSource = "" line to the If
statement the code goes to the Form_Open followed by Form_Load sub routines. When the Form_Load sub
routine completes the If statement is executed. It looks like Access is reloading the form.
However, at that point the Me object is trashed. That is the code will continue to run but it has
lost its reference to itself. If I try to look at a property using the Me reference (i.e.
?Me.Parent.Name) the system throws an error saying the object reference is invalid.

But it does not throw an error when the utility function tries to restore the parent's Filter and
RecordSource:
Me.Parent.RecordSource = sRecordSource
Me.Parent.Filter = sFilter
Of course they not actually restored.

Has anyone seen this type of behavior and if so is there a work around?

 

[Daryl S]

Stewart -

One way to not lose the variables in the subform when re-loading the parent
form (due to the filter change) is to put your data in unbound hidden fields
on the parent form. Then they won't get wiped out when filter is applied on
the parent form.

 

[Stewart Berman]

I am a little confused. The parent form is not reloading -- the sub form is and it is changing the
RecordSource that triggers it not changing the filter. Why would this happen on some machines and
not others?

 

[Daryl S]

Stewart -

The Me.Parent construct refers to the parent, or main form, so your code is
changing the recordsource of the main form.

 

[Stewart Berman]

Yes, but it is the sub form that is reloaded -- not the parent.