Cannot reference Access subform Form object until the first row of parent form has been expanded

Question

When the parent form is opened in datasheet view, I cannot reference the Form object of a subform control until the first row of the parent form has been expanded.

I would like to know a good workaround to this. I need to be able to requery this kind of subform even if the first row of the parent form datasheet has not yet been expanded.

Steps to reproduce the problem:

Create two tables:

CREATE TABLE Parent (
  PK Text PRIMARY KEY
);

CREATE TABLE Sub (
  PK Text PRIMARY KEY,
  FK Text REFERENCES Parent (PK)
);

Insert some data:

INSERT INTO Parent (PK) VALUES ('A');
INSERT INTO Parent (PK) VALUES ('B');

INSERT INTO Sub (PK,FK) VALUES ('AA','A');
INSERT INTO Sub (PK,FK) VALUES ('BB','B');

Create two forms based on these tables and set the default view to Datasheet.
Add Sub form to Parent form and link on sub.FK=parent.PK.
Give the subform container control the name child.
Open the Parent form.
Expand the first row of the datasheet and then collapse it.
Attempt to requery the subform as follows:

Forms!Parent!child.Form.Requery  'This succeeds.

Close and re-open the Parent form.
Expand the second row of the datasheet and then collapse it.
Attempt to requery the subform again using the same code.

This time it fails with the following error:

Run-time error '2455':
You entered an expression that has an invalid reference to the property Form/Report.

From this, I have to conclude that in datasheet view, a subform Form object is not reference-able until the first row of the parent form is expanded.

That's by design. A parent record must be present, or the child records would be orphaned - which is a definitive no-no. — Gustav
Thank you for the comment. A parent record is present. The problem is that the sub form cannot be re-queried unless the first record in the parent form datasheet has been expanded. — JBStovers
You should clarify that the subform is in Detail section not Footer/Header or you won't get an expandable Datasheet. — ComputerVersteher

ComputerVersteher ComputerVersteher · Accepted Answer · 2018-09-06T19:16:50

Not sure if this is correct, but it I think that this is caused by the repeated SubForm control. Each of them is a different Form Object, although they have the same name.

The difference between the first and the second record is the Focus. If you expand the first record, first control of SubForm gets focus. For the second record you have to set focus yourself. If you click on a record of the second records Subform the .Requery works. If you click on first record after this (not expand) your :Requery fails too.

Another thing to try. After expanding first records subform, expand the second one (without setting focus to a control). Now add a record to table Sub with B as FK and then .Requery. You will see that second records subform is not requeried, just the first!

Solution: Requery the control not the form with

 Forms!Parent!child.Requery

That affects all records.

Cannot reference Access subform Form object until the first row of parent form has been expanded

Steps to reproduce the problem:

1 Answers