If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi folks,
I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
Thread Tools | |
Display Modes | |
|
|