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 |
#2
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
my first thought is: do you really need to to separate the individuals
records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 |
#3
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Thanks for your interest Tina,
My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 |
#4
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
hmm, okay. i don't know that i'd set up the tables quite that way, but
you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 |
#5
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
btw, suggest you use one subform in your main form. when the user chooses an
option in the main form's combo box control, then use VBA to set the subform's SourceObject, LinkChildFields, and LinkMasterFields properties for the appropriate subform object. going from memory, you may need to put a [SubformControlName].Form.Requery command at the end of the code, so that the correct records will populate the chosen subform. hth "tina" wrote in message ... hmm, okay. i don't know that i'd set up the tables quite that way, but you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 |
#6
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Thanks Tina,
I'll give that go. Regards Bob "tina" wrote in message ... btw, suggest you use one subform in your main form. when the user chooses an option in the main form's combo box control, then use VBA to set the subform's SourceObject, LinkChildFields, and LinkMasterFields properties for the appropriate subform object. going from memory, you may need to put a [SubformControlName].Form.Requery command at the end of the code, so that the correct records will populate the chosen subform. hth "tina" wrote in message ... hmm, okay. i don't know that i'd set up the tables quite that way, but you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 |
#7
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
PMFJI :-) In a Jet (Access) database, there is no way to enforce this sort of entity subclassing at the engine level. The only way to do that would be to have two FK fields in tblContacts - one for IndivID and one for OrgID, and have a table-level constraint (validation rule) to specify that they cannot both be Null. Using the structure you have, you can go most of the way there using a BeforeUpdate event procedure on your ContactType control. Something like this (pseudo-code): If ContactType.OldValue isn't null then lookup corresponding record in table corresponding to OldValue If record exists then Heavy warning message about changing contact type If user wishes to continue then delete old related record else cancel = True End If End If End If BTW, I think you are complicating matters by having separate (AutoNumber?) PKs in your Individuals and Organisations tables. I suggest you make ContactID the PK in both those tables. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 |
#8
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Graham,
Thanks for the tips. I'll re-examine the use of the autonumbers in the two sub-tables. As to the use of table-level constraints, how do I set this up? I mean, I know I can specify that a particular field can't be null. But how do I force a check on the null value of another field (field B) before allowing one field (field A) to be null? Along the same lines, how can I make sure that at least one field must be have a value? I assume that this would ordinarily be achievable at the form level - but you mention table-level constraints. I'm all ears :-) Going off topic a bit, where the form is concerned I've noticed that my database saves data automatically even if I close the form within pressing save on the toolbar. I assume there is some kind of auto-save when you enter data into a form - but this does not always happen. Sometimes it saves, sometimes it doesn't. How do I force a prompt to save every time the form closes? (I located some example code - which I don't have handy - but it does not seem to work). TIA Bob Graham Mandeno wrote: Hi Bob PMFJI :-) In a Jet (Access) database, there is no way to enforce this sort of entity subclassing at the engine level. The only way to do that would be to have two FK fields in tblContacts - one for IndivID and one for OrgID, and have a table-level constraint (validation rule) to specify that they cannot both be Null. Using the structure you have, you can go most of the way there using a BeforeUpdate event procedure on your ContactType control. Something like this (pseudo-code): If ContactType.OldValue isn't null then lookup corresponding record in table corresponding to OldValue If record exists then Heavy warning message about changing contact type If user wishes to continue then delete old related record else cancel = True End If End If End If BTW, I think you are complicating matters by having separate (AutoNumber?) PKs in your Individuals and Organisations tables. I suggest you make ContactID the PK in both those tables. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 |
#9
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Graham,
Nevermind my last post re the table-level constraints. I just re-read yours: BeforeUpdate event - got it. I'm a bit slow today. :-D I'm still interested re the save prompt question though. Regards Bob Bob wrote: Hi Graham, Thanks for the tips. I'll re-examine the use of the autonumbers in the two sub-tables. As to the use of table-level constraints, how do I set this up? I mean, I know I can specify that a particular field can't be null. But how do I force a check on the null value of another field (field B) before allowing one field (field A) to be null? Along the same lines, how can I make sure that at least one field must be have a value? I assume that this would ordinarily be achievable at the form level - but you mention table-level constraints. I'm all ears :-) Going off topic a bit, where the form is concerned I've noticed that my database saves data automatically even if I close the form within pressing save on the toolbar. I assume there is some kind of auto-save when you enter data into a form - but this does not always happen. Sometimes it saves, sometimes it doesn't. How do I force a prompt to save every time the form closes? (I located some example code - which I don't have handy - but it does not seem to work). TIA Bob Graham Mandeno wrote: Hi Bob PMFJI :-) In a Jet (Access) database, there is no way to enforce this sort of entity subclassing at the engine level. The only way to do that would be to have two FK fields in tblContacts - one for IndivID and one for OrgID, and have a table-level constraint (validation rule) to specify that they cannot both be Null. Using the structure you have, you can go most of the way there using a BeforeUpdate event procedure on your ContactType control. Something like this (pseudo-code): If ContactType.OldValue isn't null then lookup corresponding record in table corresponding to OldValue If record exists then Heavy warning message about changing contact type If user wishes to continue then delete old related record else cancel = True End If End If End If BTW, I think you are complicating matters by having separate (AutoNumber?) PKs in your Individuals and Organisations tables. I suggest you make ContactID the PK in both those tables. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 |
#10
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
The solution that Jamie gave is simple and elegant. To translate it into Access table design terms (which might be more familiar to you than ANSI-92 DDL statements!): Add a unique index to tblContacts involving ContactID AND ContactType. Next add a ContactType field to both tblIndividuals and tblOrganisations and for each, set the default value to the corresponding contact type and set the validation rule to =contact type and set required=Yes. (In other words, an individual MUST be an individual and cannot be an organisation, and vice-versa). Now, add a 1:1 relationship with referential integrity between ContactID/ContactType in tblContacts and tblIndividuals, and the same for tblOrganisations. Now the engine will look after the integrity for you. If a record in tblContacts has a matching record in one of the other tables, then the contact record can neither be deleted, nor changed to the other contact type, unless the related subclass record is first deleted. To answer your other questions: 1. A table-level validation rule can be created in the Table Properties window (ViewProperties in design view). For example: ([IndivID] Is Not Null) Xor ([OrgID] Is Not Null) However, I would NOT use this two-field approach for your particular problem. 2. You can force a prompt before saving a record using the form's BeforeUpdate event. Select case MsgBox("Save changes?", vbYesNoCancel) case vbYes ' do nothing case vbNo Cancel = True Me.Undo case vbCancel cancel = true End Select 3. You can set ANSI-92 mode via ToolsOptionsTables/Queries. Use with caution! -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Hi Graham, Thanks for the tips. I'll re-examine the use of the autonumbers in the two sub-tables. As to the use of table-level constraints, how do I set this up? I mean, I know I can specify that a particular field can't be null. But how do I force a check on the null value of another field (field B) before allowing one field (field A) to be null? Along the same lines, how can I make sure that at least one field must be have a value? I assume that this would ordinarily be achievable at the form level - but you mention table-level constraints. I'm all ears :-) Going off topic a bit, where the form is concerned I've noticed that my database saves data automatically even if I close the form within pressing save on the toolbar. I assume there is some kind of auto-save when you enter data into a form - but this does not always happen. Sometimes it saves, sometimes it doesn't. How do I force a prompt to save every time the form closes? (I located some example code - which I don't have handy - but it does not seem to work). TIA Bob Graham Mandeno wrote: Hi Bob PMFJI :-) In a Jet (Access) database, there is no way to enforce this sort of entity subclassing at the engine level. The only way to do that would be to have two FK fields in tblContacts - one for IndivID and one for OrgID, and have a table-level constraint (validation rule) to specify that they cannot both be Null. Using the structure you have, you can go most of the way there using a BeforeUpdate event procedure on your ContactType control. Something like this (pseudo-code): If ContactType.OldValue isn't null then lookup corresponding record in table corresponding to OldValue If record exists then Heavy warning message about changing contact type If user wishes to continue then delete old related record else cancel = True End If End If End If BTW, I think you are complicating matters by having separate (AutoNumber?) PKs in your Individuals and Organisations tables. I suggest you make ContactID the PK in both those tables. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... 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 | |
|
|