Form/Subform out of sync after encountering a record with no children

[mas5]

I've encountered a strange form/subform synchronization problem.

Everything works great as I'm paging forward through my records as long as my mainform records have children records. Once I get to a record that has no children in the subform then I'm out of sync and I can't get back in sync unless I close the form and reopen.

There are three tables involved so it is a little more complicated than just a one-to-many relationship. This is a database for a school. I have these tables involved with this form:
1)tblParent
2)tblTelephones
3)tblParentTelephones to resolve the many-to-many-relationships.

ParentID is common to tblParent and tblParentTelephone. Telephoneid is common to tblTelephone and tblParentTelephone.

In the On Current event of the main form I've discovered that when there are no telephone records for the parent the ParentID in the subform becomes null. Once it becomes null it stays null even if I requery the subform. I've even tried to reset the parentid on the subform to the parentid on the mainform and then requery but then I get a problem with the telephoneid.

Anybody ever run into this kind of a problem? Any help would be greatly appreciated.

 

[scriverb]

Check the relationship that you are using between tblTelephones and tblParentTelephones. Why are you in a situation where there are no records. No records on the right side of the relationship? This could cause the null values due to not finding any records for the subform.

Please detail the tblTelephone and tblParentTelephone fields and relationships. This is where the problem lies. Somehow you have a situation where there is a referential integrity problem or something. Bob Scriver

 

[mas5]

The problem occurs when I have a parent with no telephone numbers (tblParent has no related tblParentTelephone records). It's entirely possible that we would have such a case. I've checked my tables and I don't have any stray tblParentTelephone records that are not related to tblTelephone records.

I have been suspecting something wrong with tbltelephone and tblParenttelephone or the way the form is set up, but the tables are fairly straightforward. I created the subform in the form wizard, selected both tables and all of their fields and asked to view by tblParentTelephone. I added it to my mainform (Parent) later using the toolbox and gave parentid as the link field.

tblTelephone (has the one side of the relationship)
TelephoneID long int (Autonumber) Key
Telephone Text(10) with input mask
Extension Integer
Telcodeid Long Int (foreign key for bus,cell,fax,etc)

tblParentTelephone (has the many side of the relationship
to both Parent and Telephone)
TelephoneID long int (key) - related to tblTelephone
ParentID long int (key - related to tblParent
telinclude yes/no

Thanks for your help.