Access 2007 - Recordsource & Subform

[Charles Tam]

Hi Everyone,

While converting our apps from Access 2003 to 2007, I've come across the
following issue. We've a main Form which contains a Subform. In Access
2007, when we update the main Form's recordsource value, the subform re-opens
(i.e. its on open event is executed) However, this doesn't happen in Access
2003. Does anyone has a workaround?

 

[Tom van Stiphout]

On Tue, 25 Nov 2008 17:30:05 -0800, Charles Tam

Why are you changing the RecordSource?

I can't reproduce this behavior with the Northwind "Order Details"
form. Can you?

-Tom.
Microsoft Access MVP

 

[Charles Tam]

Hi Tom,
We change the main Form's recordsource, so that, the returning data could be
determined at runtime. E.g. we would return specific records insteads of all
records. In regards to Northwind "Order Details", unfortunately, we're not
using the "Link Master/Child Fields", i.e. our subform is unbounded.

 

[Tom van Stiphout]

On Tue, 25 Nov 2008 19:09:01 -0800, Charles Tam

Sounds like you have a good reason for your implementation. How about
using a semaphore:
Warning: untested code follows.
In the declaration section for the form:
private m_blnReassigningRecordSource as Boolean

In the form's codebehind:
Public Property Get ReassigningRecordSource() As Boolean
ReassigningRecordSource = m_blnReassigningRecordSource
End Property
Private Property Let ReassigningRecordSource(ByVal blnReassigning As
Boolean)
m_blnReassigningRecordSource = blnReassigning
End Property

In Form_Open:
ReassigningRecordSource= False

In your procedure where you reassign the recordsource:
ReassigningRecordSource = False

In the subform's Form_Open:
If Me.Parent.ReassigningRecordSource then
Exit Sub
end if

-Tom.
Microsoft Access MVP

 

[Charles Tam]

Thanks for your suggestion, I'll try it.

I wonder why Access 2007 implements this differently than Access 2003.
Perhaps, Access 2007 has a specific approach to this implementation that we
are'nt aware of. Does anyone know about this? If so, please share your
solutions.

 

[AccessVandal via AccessMonster.com]

Same here, it does not happen i A2K even, I don't know why about this
inconsistancies with A2K7.

I think Tom suggestion might work, use the onopen event arguement to cancel
the event.

 

[Charles Tam]

Hi, I've used the OnOpen event to cancel the event. Unfortunately, the
subform no longer displayed. I believe, there's because the subform is being
closed and then reopen. Is there a way to cancel the subform being close in
the first place? It seems there is not cancel argument for OnClose event?

 

[Charles Tam]

I've tried using the OnUnload to cancel the subform being closed. However,
the OnOpen event is still occuring. It seems the Access runtime is calling
OnOpen directly. Any ideas?

 

[AccessVandal via AccessMonster.com]

Sorry about that, I thought you wanted to hide the subform.

I believe whenever you update the main form recordsource, Access 2007 reloads
the subform causing the events to execute.

Try searching the MS KB or the Hotfix. I couldn’t find anything related.

Here’s a site from Allen Browne about list of bugs.

http://www.allenbrowne.com/Access2007.html#Bugs

http://allenbrowne.com/Access2007.html

I've tried using the OnUnload to cancel the subform being closed. However,
the OnOpen event is still occuring. It seems the Access runtime is calling
OnOpen directly. Any ideas?

Hi, I've used the OnOpen event to cancel the event. Unfortunately, the
subform no longer displayed. I believe, there's because the subform is being

[quoted text clipped - 13 lines]

 

[Charles Tam]

Thanks for your reply. In the meantime, I'll try to implement the feature in
another way. Any recommendation?

Sorry about that, I thought you wanted to hide the subform.

I believe whenever you update the main form recordsource, Access 2007 reloads
the subform causing the events to execute.

Try searching the MS KB or the Hotfix. I couldn’t find anything related.

Here’s a site from Allen Browne about list of bugs.

http://www.allenbrowne.com/Access2007.html#Bugs

http://allenbrowne.com/Access2007.html

I've tried using the OnUnload to cancel the subform being closed. However,
the OnOpen event is still occuring. It seems the Access runtime is calling
OnOpen directly. Any ideas?

Hi, I've used the OnOpen event to cancel the event. Unfortunately, the
subform no longer displayed. I believe, there's because the subform is being

[quoted text clipped - 13 lines]

are'nt aware of. Does anyone know about this? If so, please share your
solutions.

 

[AccessVandal via AccessMonster.com]

Sorry, none so far.

Thanks for your reply. In the meantime, I'll try to implement the feature in
another way. Any recommendation?

 

[Charles Tam]

Should we inform Microsoft about this issue? If so, how?

 

[Marshall Barton]

I've tried using the OnUnload to cancel the subform being closed. However,
the OnOpen event is still occuring. It seems the Access runtime is calling
OnOpen directly. Any ideas?

Instead of canceling the open event, try using Exit Sub

 

[Charles Tam]

Thanks for suggestion, I've tried it. Unfortunately, it doesn't solve the
problem, because the subform is already in the openning state.

What we need is a solution such as the subform wouldn't be triggered to
reopen when its parent form's recordsource has changed. FYI: Access 2003
implemented this behaviour.

 

[AccessVandal via AccessMonster.com]

Hi Mash,

It the inconsistency between A2K/A2K2/A2K3 and A2K7. The problem the OP in
A2K7 had was that when the main form recordsource is change, the subform
reloads causing the event to fire up. This does not happen is older versions.

Could you contact someone in MS?

I've tried using the OnUnload to cancel the subform being closed. However,
the OnOpen event is still occuring. It seems the Access runtime is calling

[quoted text clipped - 4 lines]
Instead of canceling the open event, try using Exit Sub

 

[Marshall Barton]

It the inconsistency between A2K/A2K2/A2K3 and A2K7. The problem the OP in
A2K7 had was that when the main form recordsource is change, the subform
reloads causing the event to fire up. This does not happen is older versions.

Could you contact someone in MS?

I don't think I should do that at this point. My email and
newsgroup accounts were recently(?) hijacked and despite my
ISP's and my own efforts, there is no explanation as to
where/when/what/how that was done. Until I gain more
confidence that things are safe, I am very reluctant to
login to the MS private servers.

 

[AccessVandal via AccessMonster.com]

It's alright,I can understand. Even my e-mail was hi-jacked before.

 

[AccessVandal via AccessMonster.com]

You can try MSDN. Scroll below "Get Help from Microsoft".

http://msdn.microsoft.com/en-us/office/aa905515.aspx

 

[Datasmith]

I recently started converting an app to Access 2007. I have a main form with
lots of subforms. Both the main form and subforms are detached and
reattached to various recordsource queries as needed. After installing
Access 2007, even before converting the applicaion from 2003, the subforms
began exhibiting strange behaviour when the recordsource was changed. I
upgraded the app to 2007 and still had the problem. Even though the
underlying query was showing several hundred records, only one was showing.
Cut to the chase...

What I found was that when programmatically assigning the subform
recordsource it was also assigning values to the LinkMasterFields and
LinkChildFields properties, even though none existed to start. Now every
time I need to reassign the recordsource I also add two statements to clear
the relational linkage:

Forms!frmMain!fsubTemp.Form.Recordsource = "MyQuery"
Forms!frmMain!fsubTemp.LinkChildFields = ""
Forms!frmMain!fsubTemp.LinkMasterFields = ""

Seems to have solved this problem. Hope this helps you too.

Bruce

 

[Marshall Barton]

I recently started converting an app to Access 2007. I have a main form with
lots of subforms. Both the main form and subforms are detached and
reattached to various recordsource queries as needed. After installing
Access 2007, even before converting the applicaion from 2003, the subforms
began exhibiting strange behaviour when the recordsource was changed. I
upgraded the app to 2007 and still had the problem. Even though the
underlying query was showing several hundred records, only one was showing.
Cut to the chase...

What I found was that when programmatically assigning the subform
recordsource it was also assigning values to the LinkMasterFields and
LinkChildFields properties, even though none existed to start. Now every
time I need to reassign the recordsource I also add two statements to clear
the relational linkage:

Forms!frmMain!fsubTemp.Form.Recordsource = "MyQuery"
Forms!frmMain!fsubTemp.LinkChildFields = ""
Forms!frmMain!fsubTemp.LinkMasterFields = ""

Seems to have solved this problem. Hope this helps you too.

Before doing that, see if clearing the Tools - Options -
Tables/Queries - Auto Index ... entry makes the problem go
away