Master Child link being broken. (1 Viewer)

[gaidheal]

We have a database we converted to 2007 from 2003. The main form
contains two subforms. One subform is working fine, the other not so
much. When the database is filtered, the second subform appears to
lose the master child link. Existing data in the subform does not
appear, and we cannot add data to it - 'invalid use of null' error
appears.


On the subform I have the ID field that is being linked to from the
Master form. It is usually set to not visible, but I made it visible
for troubleshooting. In unfiltered usage the ID field shows the linked
ID from the Master form correctly. But when the filter is applied
that field shows '(New)'.
On other oddity. If, after applying the filter, I view the form in
Design View, then back to Form View, without changing anything, the
subform works correctly, and will continue to do so while in the
filter. But setting another filter brings the problem back.

 

[boblarson]

How are you setting the filter? Are you using code? If so, what is it?

 

[gaidheal]

No code is used to set the filter. It is done using the built-in filter tool. For example, I right-click on the 'Name' field, select 'text filters', select 'Equals', and enter the name being filtered.

 

[boblarson]

Not sure about this.

 

[gaidheal]

Okay, finally resolved this. The issue was the subform filter. How it got set I do not know, but it was set to a specific record id. When the main form was filtered the subform filter was activated and didn't match the main form filter, so wouldn't show anything.
Lesson: If you filter on the same field that the master and child forms link with, make sure there is not filter set in the child form.

 

[boblarson]

And make sure you don't use the acSaveYes with any close of the forms as that saves design changes (including filters). So, instead of using
DoCmd.Close acForm, Me.Name, acSaveYes
you would use

DoCmd.Close acForm, Me.Name, acSaveNo

Which has to do with design changes and not record changes (a common mistake).