Access 2007 - Recordsource & Subform

  • Thread starter Thread starter Charles Tam
  • Start date Start date
C

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?
 
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
 
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.
 
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
 
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.
 
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?
 
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?
 
Thanks for your reply. In the meantime, I'll try to implement the feature in
another way. Any recommendation?

AccessVandal via AccessMonster.com said:
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


Charles said:
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
Click to expand...
[quoted text clipped - 13 lines]
are'nt aware of. Does anyone know about this? If so, please share your
solutions.
Click to expand...
Click to expand...
Click to expand...
 
Charles said:
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?
Click to expand...

Instead of canceling the open event, try using Exit Sub
 
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 said:
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?
Click to expand...


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.
 
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
 
Datasmith said:
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.
Click to expand...

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