Blank Subform

[Rod]

Because of the rather strange design specs I have been given, the same
subform appears four times on a single form. Each instance of the subform
appears on a different tab of a tab control and is linked to a different
master field on the primary form. Put another way, there are four subforms
each with the same Source Object.

Three subforms work fine, each linking the child field (QuoteID) with a
different master field. The fourth does not - a white space appears where the
subform should be.

Given that the underlying subform (the Source Object) is the identical form
in all four instances, the problem cannot relate to the subform itself. It
must relate to how the subform object on the primary form relates to its
Source Object and the primary form.

The child and master fields for the fourth form contain valid data. All
other setting for the fourt, non-working subform are identical to those for
the three, working subforms.

Any ideas on what I should be looking for - i.e. what would be causing the
fourth subform to be displayed as a blank, white space?

 

[Allen Browne]

So your main form has 4 subform controls (on different tab pages), each
using the same SourceObject.

Take a look at the Name property of the subform control that does not work.
Make sure it is not the same as any of the fields in the main form's source
query.

While the form is open (in form view, not design view), open the Immediate
Window (Ctrl+G) and ask Access to tell you what's going on. For example if
the main form is named Form1, and the subform *control* name is Sub4, try:
? Forms![Form1]![Sub4].SourceObject
That should show the name of the form loaded into it.
If that works, ask about the linkage:
? Forms![Form1]![Sub4].LinkMasterFields
? Forms![Form1]![Sub4].LinkChildFields

Then ask about the values of those fields. Say the master field is ID1 and
the child field is ID2, try:
? Forms![Form1]![ID1]
? Forms![Form1]![Sub4].Form![ID2]

Note that if a subform is set up so records cannot be added (e.g.
AllowAdditions is No, or source is a read-only query) and it is filtered
such that it returns no records, its entire Detail section goes completely
blank. More info:
http://allenbrowne.com/casu-20.html

Hopefully that gives you some guidance as to how to track down what's going
on.

 

[Rod]

Thanks for the help.

What this shows is that while the SourceObject, LinkChildFields and
LinkMasterFields properties all return sensible values, however:

? Forms!Form1!Sub4.Form!ID2

Returns the following error:

"2467, The expression you entered refers to an oject that is closed or
doesn't exist"

Using the same code for the same subform which is the Source Object for a
different subform:

? Forms!Form1!Sub3.Form!ID2

Returns a sensible value.

I understand what you say about disallowing additions and having no record:
the form will return a blank detail section. The underlying form has
AllowAdditions set to Yes.

I cannot think of how to interpret the fact that I getting an error when
requesting the value of a field in one instance of te subform but not
another. Do you have any further suggestions?

So your main form has 4 subform controls (on different tab pages), each
using the same SourceObject.

Take a look at the Name property of the subform control that does not work.
Make sure it is not the same as any of the fields in the main form's source
query.

While the form is open (in form view, not design view), open the Immediate
Window (Ctrl+G) and ask Access to tell you what's going on. For example if
the main form is named Form1, and the subform *control* name is Sub4, try:
? Forms![Form1]![Sub4].SourceObject
That should show the name of the form loaded into it.
If that works, ask about the linkage:
? Forms![Form1]![Sub4].LinkMasterFields
? Forms![Form1]![Sub4].LinkChildFields

Then ask about the values of those fields. Say the master field is ID1 and
the child field is ID2, try:
? Forms![Form1]![ID1]
? Forms![Form1]![Sub4].Form![ID2]

Note that if a subform is set up so records cannot be added (e.g.
AllowAdditions is No, or source is a read-only query) and it is filtered
such that it returns no records, its entire Detail section goes completely
blank. More info:
http://allenbrowne.com/casu-20.html

Hopefully that gives you some guidance as to how to track down what's going
on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Because of the rather strange design specs I have been given, the same
subform appears four times on a single form. Each instance of the subform
appears on a different tab of a tab control and is linked to a different
master field on the primary form. Put another way, there are four subforms
each with the same Source Object.

Three subforms work fine, each linking the child field (QuoteID) with a
different master field. The fourth does not - a white space appears where
the
subform should be.

Given that the underlying subform (the Source Object) is the identical
form
in all four instances, the problem cannot relate to the subform itself. It
must relate to how the subform object on the primary form relates to its
Source Object and the primary form.

The child and master fields for the fourth form contain valid data. All
other setting for the fourt, non-working subform are identical to those
for
the three, working subforms.

Any ideas on what I should be looking for - i.e. what would be causing the
fourth subform to be displayed as a blank, white space?

 

[Allen Browne]

Ah ha! So the LinkChildFields refers to a field in the subform's recordset,
but there is no *control* with that name on the subform. That's a very
common way to build a subform, but IME it has problems.

When you build a form this way, the LinkChildFields refers to an object of
type AccessField, and it's buggy. It can even crash some versions of Access.
The solution is to add a (hidden) text box, and use the name of the text box
in LinkChildFields. But before you do that, you need to disconnect the
subform from the main form, so that Access doesn't just continue to use the
AccessField for the link.

Another possibility is that the failure is due to a Name AutoCorrect
corruption. These steps address both possibilities:

1. Open the main form in design view.

2. Delete the values beside the LinkMasterFields and LinkChildFields
properties. Repeat for each subform.

3. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

4. Compact the database:
Tools | Database Utilities | Compact/Repair

5. Open the subform in design view, and add a text box for the foreign key
field(s), i.e. the one(s) previously listed in LinkChildFields. Set the text
box's Visible property to No if you wish. Save. Close.

6. Open the main form in design view. Type the values back into the
LinkMasterFields and LinkChildFields properties. Repeat for each subform.
Save.

You should find that all subforms are now working.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the help.

What this shows is that while the SourceObject, LinkChildFields and
LinkMasterFields properties all return sensible values, however:

? Forms!Form1!Sub4.Form!ID2

Returns the following error:

"2467, The expression you entered refers to an oject that is closed or
doesn't exist"

Using the same code for the same subform which is the Source Object for a
different subform:

? Forms!Form1!Sub3.Form!ID2

Returns a sensible value.

I understand what you say about disallowing additions and having no
record:
the form will return a blank detail section. The underlying form has
AllowAdditions set to Yes.

I cannot think of how to interpret the fact that I getting an error when
requesting the value of a field in one instance of te subform but not
another. Do you have any further suggestions?

So your main form has 4 subform controls (on different tab pages), each
using the same SourceObject.

Take a look at the Name property of the subform control that does not
work.
Make sure it is not the same as any of the fields in the main form's
source
query.

While the form is open (in form view, not design view), open the
Immediate
Window (Ctrl+G) and ask Access to tell you what's going on. For example
if
the main form is named Form1, and the subform *control* name is Sub4,
try:
? Forms![Form1]![Sub4].SourceObject
That should show the name of the form loaded into it.
If that works, ask about the linkage:
? Forms![Form1]![Sub4].LinkMasterFields
? Forms![Form1]![Sub4].LinkChildFields

Then ask about the values of those fields. Say the master field is ID1
and
the child field is ID2, try:
? Forms![Form1]![ID1]
? Forms![Form1]![Sub4].Form![ID2]

Note that if a subform is set up so records cannot be added (e.g.
AllowAdditions is No, or source is a read-only query) and it is filtered
such that it returns no records, its entire Detail section goes
completely
blank. More info:
http://allenbrowne.com/casu-20.html

Hopefully that gives you some guidance as to how to track down what's
going
on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Because of the rather strange design specs I have been given, the same
subform appears four times on a single form. Each instance of the
subform
appears on a different tab of a tab control and is linked to a
different
master field on the primary form. Put another way, there are four
subforms
each with the same Source Object.

Three subforms work fine, each linking the child field (QuoteID) with a
different master field. The fourth does not - a white space appears
where
the
subform should be.

Given that the underlying subform (the Source Object) is the identical
form
in all four instances, the problem cannot relate to the subform itself.
It
must relate to how the subform object on the primary form relates to
its
Source Object and the primary form.

The child and master fields for the fourth form contain valid data. All
other setting for the fourt, non-working subform are identical to those
for
the three, working subforms.

Any ideas on what I should be looking for - i.e. what would be causing
the
fourth subform to be displayed as a blank, white space?

 

[Rod Behr]

Allen, you are a gentleman and a scholar!

Thanks! All fine.

Ah ha! So the LinkChildFields refers to a field in the subform's recordset,
but there is no *control* with that name on the subform. That's a very
common way to build a subform, but IME it has problems.

When you build a form this way, the LinkChildFields refers to an object of
type AccessField, and it's buggy. It can even crash some versions of Access.
The solution is to add a (hidden) text box, and use the name of the text box
in LinkChildFields. But before you do that, you need to disconnect the
subform from the main form, so that Access doesn't just continue to use the
AccessField for the link.

Another possibility is that the failure is due to a Name AutoCorrect
corruption. These steps address both possibilities:

1. Open the main form in design view.

2. Delete the values beside the LinkMasterFields and LinkChildFields
properties. Repeat for each subform.

3. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

4. Compact the database:
Tools | Database Utilities | Compact/Repair

5. Open the subform in design view, and add a text box for the foreign key
field(s), i.e. the one(s) previously listed in LinkChildFields. Set the text
box's Visible property to No if you wish. Save. Close.

6. Open the main form in design view. Type the values back into the
LinkMasterFields and LinkChildFields properties. Repeat for each subform.
Save.

You should find that all subforms are now working.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the help.

What this shows is that while the SourceObject, LinkChildFields and
LinkMasterFields properties all return sensible values, however:

? Forms!Form1!Sub4.Form!ID2

Returns the following error:

"2467, The expression you entered refers to an oject that is closed or
doesn't exist"

Using the same code for the same subform which is the Source Object for a
different subform:

? Forms!Form1!Sub3.Form!ID2

Returns a sensible value.

I understand what you say about disallowing additions and having no
record:
the form will return a blank detail section. The underlying form has
AllowAdditions set to Yes.

I cannot think of how to interpret the fact that I getting an error when
requesting the value of a field in one instance of te subform but not
another. Do you have any further suggestions?

So your main form has 4 subform controls (on different tab pages), each
using the same SourceObject.

Take a look at the Name property of the subform control that does not
work.
Make sure it is not the same as any of the fields in the main form's
source
query.

While the form is open (in form view, not design view), open the
Immediate
Window (Ctrl+G) and ask Access to tell you what's going on. For example
if
the main form is named Form1, and the subform *control* name is Sub4,
try:
? Forms![Form1]![Sub4].SourceObject
That should show the name of the form loaded into it.
If that works, ask about the linkage:
? Forms![Form1]![Sub4].LinkMasterFields
? Forms![Form1]![Sub4].LinkChildFields

Then ask about the values of those fields. Say the master field is ID1
and
the child field is ID2, try:
? Forms![Form1]![ID1]
? Forms![Form1]![Sub4].Form![ID2]

Note that if a subform is set up so records cannot be added (e.g.
AllowAdditions is No, or source is a read-only query) and it is filtered
such that it returns no records, its entire Detail section goes
completely
blank. More info:
http://allenbrowne.com/casu-20.html

Hopefully that gives you some guidance as to how to track down what's
going
on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Because of the rather strange design specs I have been given, the same
subform appears four times on a single form. Each instance of the
subform
appears on a different tab of a tab control and is linked to a
different
master field on the primary form. Put another way, there are four
subforms
each with the same Source Object.

Three subforms work fine, each linking the child field (QuoteID) with a
different master field. The fourth does not - a white space appears
where
the
subform should be.

Given that the underlying subform (the Source Object) is the identical
form
in all four instances, the problem cannot relate to the subform itself.
It
must relate to how the subform object on the primary form relates to
its
Source Object and the primary form.

The child and master fields for the fourth form contain valid data. All
other setting for the fourt, non-working subform are identical to those
for
the three, working subforms.

Any ideas on what I should be looking for - i.e. what would be causing
the
fourth subform to be displayed as a blank, white space?