Subform cannot be referenced when placed in a hidden footer - why? (1 Viewer)

[Notiophilus]

I use fields in the (hidden) footer of a single form to handle validation checks. Recently I needed to include a bound subform, and discovered that

• if the subform is placed in the hidden footer, VBA cannot reference it: error 2455, "You entered an expression that has an invalid reference to the property Form/Report."
• if the subform is placed in the footer, but the footer is visible, there are no errors
• if the subform is hidden, but in the main body of the form, there are no errors

Strange that VBA would refuse to recognise the subform only because the footer is hidden; I have other bound fields in that footer and have no problems from them. Is there something I should know about how Access views form footers / subforms?

*note: I am very much an amateur so mayyyybe the explanation is beyond my ken. Still, I'm curious.

The table structure has a 1-1 relationship. Each story may have up to one optional comment.
tblStory: StoryID (PK), [other fields]
tblStoryComments: StoryID (FK), Comment
tblStory is the main form controlsource; the bound subform shows tblStoryComments. I use an unbound field to view and edit comments, and only commit the edits (with the hidden bound subform) once the entire form saves. Before saving, the form runs validation checks: if key fields are not filled in, the user is alerted and the save is cancelled.

 

[theDBguy]

Hi. Can you show us exactly how you were referencing the hidden subform? Thanks.

 

[Notiophilus]

In Form_Current(),
If IsNull(Me.fsubCommentsContainer.Form!txtComments) Then 'do stuff (where txtComments is a textbox)
or
Me.fsubCommentsContainer.Form!Comment (where Comment is a field in the subform)

To reiterate, this works absolutely fine when I place it (hidden) in the main body of the form or when the footer is visible.

 

[jdraw]

Perhaps you code post a copy of the database for readers to examine.
Suggest you zip the database and include specific instructions to highlight the issue.

 

[pbaldy]

One possibility is that the subform has no records. This may help:

Forms: #Error when the Subform has no records

theaccessweb.com

 

[Notiophilus]

Sorry for the delay, busy weekend. I made a small, shitty database to highlight the issue. If there is a comment linked to the current record (Form_Current checks the subform) then the comment icon goes dark.

• frmStories_SubInBody: subform in body is hidden, no error
• frmStories_SubInFooter: subform in footer. Click the toggle to show/hide the footer: when it is hidden, Form_Current throws an error

(I know there are many ways around the problem, but I'm curious as to why it occurs)

 

[xavier.batlle]

Hi, Notiophilus

My approach is that for testing if the comment is null or not would be easier if you change the Recordsource of the form joining the two tables and then inserting the comment Textbox invisible.
I uploaded my approach.

 

[Notiophilus]

Thank you, but as I said, I'm not trying to find a way around the problem - I've already got several ways around it, and there are reasons not to make the recordsource a query - but to understand why Access behaves this way.