Can the same subform be used on a form multiple times without issues?

Question

I have a main form that has a repeated subform on it 5 times.

The subform itself has a subform on it, which is a grid of records

For a variety of reasons, that subform was modified from a datasheet to a continuous form

Each subform is having its recordsource being set by the top level form.

Having the following issue

Although the recordsource is correct in the lowest level subform, the recordset is empty

There are no records in it.

When I see that issue, I can requery everything, and the problem is solved - sort of

There is conditional formatting on the lowest level subform implementing a highlight the current record function. That doesnt work properly after the requery

Im more interested in solving the why a recordset would be empty for the exact same query

Im thinking that there is a difference in the event model between access xp and access 2010 in how events on subforms are being fired. We have discovered that the open event fires off the load and the activate, and it appears to be asyncronous across the subforms.

So the question is this. Any idea on why the subform recordsets would be empty?

Its not data driven - its some sort of timing issue.

Im willing to recode and figure out a way not to reuse the subforms if its somehow bad practice to have the same subform on a form multiple times. But in much simpler test programs, it seems to work fine

---

Scott Berger McCormick Systems

Answer 1

There shouldn't be any issue with having the same sub-form occur multiple times on a form. They are just objects of the same class.

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 2

Hi Scott,

Welcome to the Access forum.

Is there any error message displaying? Have you tried to set just on sub form on the main form? Does it work?

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

The error that occurs is a 3021 error - no current record

I expect it to occur on the first call to the form_current event - that would be the subform instantiating with an empty record source.

However when the record source for the form is set to a query that returns data, the 3021 error occurs again. The record source is valid, but no records are returned. And it stays that way through all the form events.

Its some sort of timing issue. It does not occur every time. We had to modify the code to have the form_activate event of the grandparent form check to see if this has occurred, and set all the record sources again. Not optimal.

---

Scott Berger McCormick Systems

Answer 4

The error that occurs is a 3021 error - no current record

I expect it to occur on the first call to the form_current event - that would be the subform instantiating with an empty record source.

However when the record source for the form is set to a query that returns data, the 3021 error occurs again. The record source is valid, but no records are returned. And it stays that way through all the form events.

Its some sort of timing issue. It does not occur every time. We had to modify the code to have the form_activate event of the grandparent form check to see if this has occurred, and set all the record sources again. Not optimal.

---

Scott Berger McCormick Systems

Hi Scott,

I can imagine that the subform is loaded before continuing the loading of the main form, and - in the same line - the sub-subform before the subform.
With that model in mind it could be that not yet enough reference data is available to build the recordset of subform or sub-subform, thus a timing problem.

What happens when you set the RecordSource of the subform and the sub-subform, in that order, in the Load event of the main form? This setting is quite simple. In the Load event of the main form you could include something like:

  Me.Subform(<subformcontrolname>).Form.RecordSource = Me.Subform(<subformcontrolname).Form.RecordSource,

and the same for the sub-subform.

Excuse me if the syntax to adrress the subforms RecordSource may have to be changed a little bit. Long time ago I automated that part of my programming, so I have no direct practical experience with it anymore.

Imb.

Answer 5

Yes the Access documentation states that the sub forms are loaded before the main form.

Curiously not in the order the documentation says

They are being fired asynchronously

But what you propose is what the code is currently doing

The sub form and sub-sub form dont set their own datasources. They are blank on startup

They are set by the parent (or grandparent, if you will)

Initially we had it in the open event, then moved it to the load event with no change

And I already tried that trick - setting the recordsource to itself doesnt force the load of the recordset. Its still empty. Its like some event hasnt happened yet, and access needs to come to some stable state before the code will work correctly. However we have no clue what to look for, so the patch is a kludge. Its working, but no clue why

Thats what makes this issue rather baffling

And I cant recreate it in a simpler test designed to isolate the issue

---

Scott Berger McCormick Systems

Answer 6

They are being fired asynchronously

But what you propose is what the code is currently doing

The sub form and sub-sub form dont set their own datasources. They are blank on startup

They are set by the parent (or grandparent, if you will)

Hi Scott,

Another shot in the dark. Did you add a couple of DoEvents on crucial places?

Imb.

Answer 7

Yes we thought about that trick too

It didnt fix it either

---

Scott Berger McCormick Systems

Answer 8

It's more work to coordinate things, but what I do when timing gets tricky is explicitly set the subform control's sourceobject and recordsource properties from the main form.  Initially, everything is set to nothing.  So in the main form design mode, the subform controls are there but the Source Object property is blank for each control and the subforms will show as empty white boxes.  In the subform design mode, the Record Source property is blank; you'll set this later to your queries/tables/SQL statements.

Thus there will be no conflicts when the main form loads.  After that however, you need to set the properties manually and continue to coordinate subform data with user activity (e.g. main form changes to a different record and subforms need to follow).  I am assuming you are not using the built-in Master/Child field link functionality.  I've always found the built-in Master/Child functionality to be problematic, especially in complex cases where there are sub-subforms.

For example, initially you'll have:

Me.subformcontrolname.SourceObject = "" (if you check it, no need to set this)

When you want to load the subform control:

Me.subformcontrolname.SourceObject = "Subform Object Name"

When you want to set the subform data:

Me.subformcontrolname.Form.RecordSource = "Query Object Name"

Answer 9

What does setting the subform control at run time do for you?

I was already doing the second part

---

Scott Berger McCormick Systems

Answer 10

It allows you to control "exactly" when the subform control loads. It also updates the data and I think clears out any filters but you'll have to check that.