2 subforms in 1 form, and only the first one survives!?

[xinyin]

I build a database to hold information of some companies. I have created a form with two subforms in it. The main form [frm_name] is created from table [tbl_name], contained companyID and company name. Since one company may have more than one address, there is a second table [tbl_address] and subform [frm_address]. And in one address there may be more than 1 phone line, so [tbl_phone] and second subform [frm_phone]. These tables are linked in this relationship: one company has many addresses, one address has many phone numbers.

The design of the form:
Main form [frm_name] displays the companyID, company name.
First subform [frm_address] is place inside the main form.
Second subform [frm_phone] is place right under (NOT inside) the [frm_address].
If this thing works properly, then when I am checking company "A", [frm_address] should display all address(es) that company A has; and [frm_phone] should display all phone numbers depending on which address in [frm_address] is "focused" (usually the first one by default). For example, if I click the second address in [frm_address], then [frm_phone] should automatically displays only the phone numbers belong to that address. And if I check another company, then of course BOTH subforms should change at the same time.

Problem:
The address subform works fine, but [frm_phone] is dead - it always stays at the first phone number no matter which company name I am currently at or which address I am clicking.
My Link Master/Child Fields property: for the phone subform the link child field is [addressID] (autonumber field to link up tbl_address and tbl_phone), and the link master field is [frm_address].Form![addressID]. In Northwind there is a form [Customer Orders] works exactly the way I want. But its second subform comes from a query not table, I tried the same thing on my phone subform but it still doesn't work. And for some reasons I cannot just copy and edit it.

 

[Isadore]

xin: I'm no expert, but I've never layed a form over a table, always a query (I suppose the answer to this would be that its better to be working with a recordset of the table rather than the table itself).

Sounds like you might have to trigger a "requery" event, perhaps in the OnCurrent, e.g.,

Me![Form1].Form![subform2].Requery

...just an idea. It may be that subform1 is queried and then for some reason, subform2 doesn't.

 

[hmdoc30]

It sounds like you haven't defined your relationships properly. i.e., Primary key/Secondary key.

For your first table tbl.name should be your Primary key that is linked to your tbl.address. This will be a one-many relationship due to multiple addresses. Then you need to create another table for phone numbers with its unique Primary key that is linked to your tbl.address. This too is a one-many relationship.

Once you defined your relationships then your subforms will work correctly. Don't forget to use Referential Integrity & Cascading in your relationships.