Access many-to-many data disappears before my eyes

[NPeirce]

Hi.

I have a database with one main table, let's say tblCompanies, which has two many-to-many relationships with other tables, tblPlaces and tblEmployees.

To support the many-to-many relationships, I have two associations tables, let's say tblAssociationsCP which contains Company ID and Place ID, and tblAssociationsCE which contains CompanyID and EmployeeID.

When I input data into tblAssociationsCE, it's fine, but when I input data into tblAssociationsCP, then close that same table and reopen it, the data I just inputted is gone!

But it's not all the way gone...

The disappearing data does show up in the "+" dropdown in tblPlaces on the correct rows, so the association has been made in some fashion (and by the way, I can't enter it again in the blank tblAssociationsCP, just like I can't double-enter two rows the same in tblAssociationsCE) but why might everything I input disappear out of one associations table and not the other? I thought I made them the same.

Any ideas?

Thanks very much.

-Nathan

 

[MajP]

It does not sound like it is "gone" just the display has some kind of filter on it.

Can you provide the structure for tblAssociationsCP? My assumption is something like

companyID_fk (foreign key to the company table)
placesID_fk (foreingn key to the places table

Now make sure this table does not have lookup fields. Lookups should be only used at the form level. If the pK in the places table and company tables are autonumbers then I would expect to see data that looks like this

1 1
1 2
1 5
1 7
2 2
2 4
2 7
......
not something like
CompanyA North Carolina
CompanyA California
...

 

[NPeirce]

That sounds about right.

The associations tables don't have any primary keys defined, but the tblPlaces and tblEmployees do have primary keys defined as "Place Record ID" and "Employee Record ID". Those are the same field names used in the associations tables.

There are no autonumber fields, but the associations tables are numeric and the one that's working looks like your numeric example.

I didn't create any lookup fields.

Any other ideas?

Thanks,

Nathan