Changing RecordSource Causes A Reload of Sub Form Destroying References

[Stewart Berman]

Apologies for posting in two newsgroups but after I originally posted it in forms I realized this
should have been posted here (formscoding) instead.

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?

 

[Paul Shapiro]

Are all the machines fully patched for Office 2007? Maybe Office service
pack level and/or later hotfixes are different/missing? Nothing you're
describing sounds like it would be OS or machine-dependent.

Are you sure it's the Me reference that is no longer valid? You gave the
example of Me.Parent, and after the parent form has been reloaded it might
make sense that Me.Parent is no longer valid. It's unusual to have a subform
modifying the parent form settings. Maybe you could raise a custom event in
the subform and let the parent form take action in response to that event?
Or make the utility subform a separate form rather than a subform. Then it
could refresh it's reference to the current parent form using the Forms
collection if the parent form reference becomes invalid.

 

[Stewart Berman]

It is definitely the Me reference that dead. Even:
?Me.name
Fails.

I changed the code to:
Me.Parent.Recordset.close
Me.Parent.Filter = ""
Me.Parent.RecordSource = ""
And it throws:
Run-time error '2567:
The expression you entered refers to an object that is closed or doesn't exist.
At the second line.

 

[Stewart Berman]

I forgot to mention that it happened on a machine that did not have Access 2007 install -- just the
runtime version that was installed as part of this product. Other machines without Access 2007
installed that had the runtime installed as part of this product do not exhibit this behavior.

So the machines with only the runtime are running the exact same version of Access 2007 runtime as
it was installed with the product.

 

[Charles Wang [MSFT]]

Hi,
Since this issue does not appear on your other machines, I think that this
should not be a coding issue. Probably some components were not installed.
Since you were using RecordSource, I would like to check with you besides
Access runtime, did you also install Data Connectivity Components? I
suggest that you also install this component and make sure that you apply
the latest update.
2007 Office System Driver: Data Connectivity Components
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554f536-8c28-4598-
9b72-ef94e038c891&DisplayLang=en
Microsoft Office Access Runtime and Data Connectivity 2007 Service Pack 2
(SP2)
http://www.microsoft.com/downloads/details.aspx?FamilyId=6F4EDEED-D83F-4C31-
AE67-458AE365D420&displaylang=en

Best regards,
Charles Wang

 

[Stewart Berman]

Access runtime, did you also install Data Connectivity Components? I

suggest that you also install this component and make sure that you apply
the latest update.

I am a little confused. The installation package includes the redistributable runtime components
needed to use the application. The same installation package was used on multiple machines. Some
of those exhibit the problem. All of the machines that did not have Access 2007 installed before
the installation package was run are going to have the same level of Access 2007 runtime components.

Microsoft Office Access Runtime and Data Connectivity 2007 Service Pack 2

I just saw the notice about SP2 for Access 2007. I tried to download the spreadsheet that listed
what was fixed but the link on http://support.microsoft.com/default.aspx/kb/957262 to download the
spreadsheet doesn't work.

Is there someplace else to get the spreadsheet?

 

[Stewart Berman]

Microsoft Office Access Runtime and Data Connectivity 2007 Service Pack 2

I am really getting confused. I got the notice from Windows Update that SP2 was available. Then I
looked at the date on the web page that describes it and it is over six months old.

Why is Windows Update first pushing this out six months after it was released?

 

[Charles Wang [MSFT]]

Hi Stewart,
You can download the Excel spreadsheet from this article:
http://support.microsoft.com/kb/953195

Regarding the windows update question, I suggest you submit a new question
at Windows community for dedicated assistance.

Best regards,
Charles Wang

 

[Stewart Berman]

You can download the Excel spreadsheet from this article:

Thanks.

Regarding the windows update question, I suggest you submit a new question
at Windows community for dedicated assistance.

No thanks. I've already been though bug reporting there -- I'll leave the question unanswered. I
have Office 2007 Ultimate installed on XP and Windows 7 and have only seen SP2 offered on the XP
machine. I have seen this before there doesn't seem to be any logically way to figure out why one
system gets an update to an MS product that is not OS dependent and another doesn't.

In the meantime can you please respond to:

I am a little confused. The installation package includes the redistributable runtime components
needed to use the application. The same installation package was used on multiple machines. Some
of those exhibit the problem. All of the machines that did not have Access 2007 installed before
the installation package was run are going to have the same level of Access 2007 runtime components.

 

[Charles Wang [MSFT]]

Hi Stewart,
According to the current information, it is hard to give a reason why this
issue happened at some of your computers but worked on other computers with
the same Access 2007 runtime components. What I suggested before was trying
to help isolate if this is a possible problem. But since this issue is
environment specific and hard to be reproduced at our side, if you want to
track the root cause of the issue, a dump analysis is required. You need to
contact Customer Support Services (CSS) via telephone so that a dedicated
Support Professional can assist you in a more efficient manner. Please be
advised that contacting phone support will be a charged call.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.

Best regards,
Charles Wang

 

[Stewart Berman]

Support Professional can assist you in a more efficient manner. Please be

advised that contacting phone support will be a charged call.

I think I'll pass on that. If I were to pay for every software bug I hit -- in third party as well
as MS -- I'd need a lot bigger revenue stream just to break even.

Anyway, we developed a workaround for the symptom -- that is the bug still affects the flow but we
are able to complete the process.

 

[Charles Wang [MSFT]]

Hi Stewart,
For contacting CSS, if this issue is proved to be a product issue, you will
not be charged at last. Anyway I am glad to hear that you have found a
workaround by yourself.

Best regards,
Charles Wang

 

[Stewart Berman]

The problem with CSS is that you are charged first and then, IF CSS
completes problem determination AND decides it is a bug you get a refund.
Meanwhile they have your money for as long as it takes for them to get to
the problem and forever if they can't do problem determination. I wonder
how much is earned on the float.

Yes, I have a workaround but the bug will probably bite a number of people
and might take a few out. Hopefully, someone will hit it in the 2010 beta.