weird access duplicate tables

[ladydazy]

For some reason, duplicate tables were created in the Relationships window on 2 tables that have a relationship between them.

tblCampus tblSchool
cam_id (PK) sch_id (PK)
cam_name sch_name
cam_id (FK)

I used the Lookup Wizard to create a combo box to pull info. from cam_name in the tblCampus to the field cam_id in tblSchool. The info. is pulled nicely, but then when I look in the Relationships window, duplicate tables appeared ( School_1, School_2 and Campus_1) and won't go away even after I delete them! They just come on back when I reopen the Relationships window.Argh!

All this because I was trying to get around another problem I was getting: I wrote an SQL statement to pull the info from tblCampus.cam_name (to cam_id in School) but could not select any of its values in the School form. Error message: You cannot add or change a record because a related record is required in table 'Campus'

 

[Pat Hartman]

The "duplicates" in the relationship window are not duplicate tables. In some cases, a copy of a table suffixed with a number will be added to the window to make the relationships easier to understand. For example, if you had two zipcodes in the same table, one for billing address and the other for shipping address, you would need two instances of the zipcode table to represent the relationships. BillZip would be connected to one instance and ShipZip would be connected to the second instance. This is simply a way of cleanly showing the relationships and doesn't duplicate anything.

If you want to delete a table from the relationships window, you must first delete any relationships that it participates in. Once the relationship is deleted, when you delete the table (you are not really deleting a table, you are simply removing it from the "picture") it will stay deleted. The delete of a relationship is real however. If you delete a relationship, Jet will no longer attempt to enforce it so make sure that that is what you really want.

You cannot add or change a record because a related record is required in table

- it is quite possible that this error is caused because you have bound the wrong field in your combobox. For example, if you have a combo that shows CustomerName, it is almost certainly bound to a field named CustomerID. CustomerID is numeric and the unique identifier for a customer so that is the field that is stored in the order table, NOT CustomerName. If you are unfamiliar with the details of combos, let the wizard build them but make sure you understand the impact of the choices you make.

 

[ladydazy]

Hey, thanks for the tip.