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 |
#21
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
Your changes to the design sound fine. The only thing I would add is a validation rule for ContactTypeID: =1 in tblIndividuals =2 in tblOrganisations to ensure that one entity cannot accidentally "morph" into the other. As to your strange error, are you certain that you are not trying to change tblContacts.ContactTypeID for an *existing* record (the one you have previously entered)? Do you get the error if you are on a new, empty record? How have you set up the Link Master/Child Fields properties of your subform control? If you change the SourceObject of a subform control then you must also respecify the link fields, even though the field names have not changed. Also, you should be checking in Form_Current that the appropriate subform is loaded and changing it if necessary. If you're still having trouble, post the code behind your form and I'll try to reproduce the problem. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, This doesn't seem to be working properly for me. Here's what I've done so far: In tblContacts: - left the pk as it was (ie tblContactsID remains an autonumber pk field) - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblIndividuals: - deleted the original autonumber pk field (IndividualsID) - converted the existing ContactID (number) field as the new pk - inserted a new ContactTypeID (number) field - set the "default value" property to 1 (corresponding to the ContactTypeID for the "Indiv" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblOrganisations - deleted the original autonumber pk field (OrganisationsID) - converted the existing ContactID (number) field as the new pk - set the "default value" property to 2 (corresponding to the ContactTypeID for the "Orgs" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file I then created a 1:1 relationship based on the combined ContactID and ContactTypeID fields between tblContacts and tblIndividuals. I did this by selected the two fields in tblContacts and dragging them over to tblIndividuals. I created a 1:1 relationship between tblContacts and tblOrganisations in the same way. I then deleted all existing test data from tblContacts, tblIndividuals and tblOrganisations - starting with a clean slate. I then opened my client data-entry form. The main form has all fields from tblContacts. The subform is unbound, but I have inserted vba into the AfterUpdate event section of the form to ensure that the SourceObject of the subform control is changed to the required subform depending on the selected ContactType (selected from a combo box). I can enter the first record of either tblIndividuals or tblOrganisations without difficulty - once I open the required subform the value of ContactID is the same as the autonumber pk in tblContacts, and the value of the ContactTypeID defaults to the relevant default value. However, after having entered this first record, if I then close and reopen the main form, I am prevented from creating any new reords in either table by a popup box which states "The record cannot be deleted or changed because the table "Individuals" includes related records." What could be causing this error? There are no "related" records in existence! TIA Bob "Graham Mandeno" wrote in message ... 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 |
#22
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Graham,
Thanks for sticking with me on this. My setup is this: In the unbound subform control I have already the following settings: Link Child Fields - ContactID;ContactTypeID Link Master Fields - ContactID;ContactTypeID I do not create or amend this links programmatically in any way. The only thing I change via VBA is the SourceObject. As for the VBA Code itself, this is what I have: Private Sub cboContactType_AfterUpdate() If Me.cboContactType.Value = 1 Then ' Individual Me.Contactsubfrm.SourceObject = "NewIndiv" End If If Me.cboContactType.Value = 2 Then ' Organisation Me.Contactsubfrm.SourceObject = "NewOrgs" End If End Sub I've placed this code in the "After Update" event for a combobox in my main form. The control itself is bound to the ContactTypeID field in my tblContacts, but the RowSource is bound to my tblContactType. "NewIndiv" is my modified subform for Individuals, and "NewOrgs" is my modified subform for Organisations. "Contactsubfrm" is the name I have given the subform control on my main form. I've tried adding the following lines in each of the If ... Then statemens in my code: Me.Contactsubfrm.LinkChildFields = "ContactID;ContactTypeID" Me.Contactsubfrm.LinkMasterFields = "ContactID;ContactTypeID" But this just causes the error message to popup sooner - as soon as the subform opens rather than when I try to select a control or move to a new record. This time I get "Run Time 3200" as part of the error message as well. Curiously, the record selectors on the subform are not grayed out in this scenario - but still result in an error message if I press any button on the record selector itself. As for the validation rules - I forgot to mention that I had already put these in place (as per the directions in your previous post). As for the problem itself, it occurs on both subforms: - when the NewIndiv opens up, the first record it shows is the existing record that I just entered. The message I quoted earlier pops up regardless of whether I'm trying to select a textbox on the subform or simply using the record selector (on either the main form or the subform itself) to create a new record. (By the way, the record selector on the subform remains grayed out) - the message also pops up when I open the NewOrgs subform (which shows a blank record except for the ContactID and ContactTypeID fields which are already filled in when the form opens). I'm not sure what you mean when you say I should be checking in Form_Current to make sure the appropriate subform is loaded. I can definitely see the subforms changing depending on the value in my combobox (for ContactType) if that's what you mean. TIA Bob "Graham Mandeno" wrote in message ... Hi Bob Your changes to the design sound fine. The only thing I would add is a validation rule for ContactTypeID: =1 in tblIndividuals =2 in tblOrganisations to ensure that one entity cannot accidentally "morph" into the other. As to your strange error, are you certain that you are not trying to change tblContacts.ContactTypeID for an *existing* record (the one you have previously entered)? Do you get the error if you are on a new, empty record? How have you set up the Link Master/Child Fields properties of your subform control? If you change the SourceObject of a subform control then you must also respecify the link fields, even though the field names have not changed. Also, you should be checking in Form_Current that the appropriate subform is loaded and changing it if necessary. If you're still having trouble, post the code behind your form and I'll try to reproduce the problem. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, This doesn't seem to be working properly for me. Here's what I've done so far: In tblContacts: - left the pk as it was (ie tblContactsID remains an autonumber pk field) - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblIndividuals: - deleted the original autonumber pk field (IndividualsID) - converted the existing ContactID (number) field as the new pk - inserted a new ContactTypeID (number) field - set the "default value" property to 1 (corresponding to the ContactTypeID for the "Indiv" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblOrganisations - deleted the original autonumber pk field (OrganisationsID) - converted the existing ContactID (number) field as the new pk - set the "default value" property to 2 (corresponding to the ContactTypeID for the "Orgs" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file I then created a 1:1 relationship based on the combined ContactID and ContactTypeID fields between tblContacts and tblIndividuals. I did this by selected the two fields in tblContacts and dragging them over to tblIndividuals. I created a 1:1 relationship between tblContacts and tblOrganisations in the same way. I then deleted all existing test data from tblContacts, tblIndividuals and tblOrganisations - starting with a clean slate. I then opened my client data-entry form. The main form has all fields from tblContacts. The subform is unbound, but I have inserted vba into the AfterUpdate event section of the form to ensure that the SourceObject of the subform control is changed to the required subform depending on the selected ContactType (selected from a combo box). I can enter the first record of either tblIndividuals or tblOrganisations without difficulty - once I open the required subform the value of ContactID is the same as the autonumber pk in tblContacts, and the value of the ContactTypeID defaults to the relevant default value. However, after having entered this first record, if I then close and reopen the main form, I am prevented from creating any new reords in either table by a popup box which states "The record cannot be deleted or changed because the table "Individuals" includes related records." What could be causing this error? There are no "related" records in existence! TIA Bob "Graham Mandeno" wrote in message ... 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 |
#23
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
Is your database very large? Would you mind zipping it and emailing it to me? That would be quicker than me trying to set up something from scratch to try to reproduce the problem. Send it to ng1.g.mandeno at xoxy.net. -- Thanks! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Thanks for sticking with me on this. My setup is this: In the unbound subform control I have already the following settings: Link Child Fields - ContactID;ContactTypeID Link Master Fields - ContactID;ContactTypeID I do not create or amend this links programmatically in any way. The only thing I change via VBA is the SourceObject. As for the VBA Code itself, this is what I have: Private Sub cboContactType_AfterUpdate() If Me.cboContactType.Value = 1 Then ' Individual Me.Contactsubfrm.SourceObject = "NewIndiv" End If If Me.cboContactType.Value = 2 Then ' Organisation Me.Contactsubfrm.SourceObject = "NewOrgs" End If End Sub I've placed this code in the "After Update" event for a combobox in my main form. The control itself is bound to the ContactTypeID field in my tblContacts, but the RowSource is bound to my tblContactType. "NewIndiv" is my modified subform for Individuals, and "NewOrgs" is my modified subform for Organisations. "Contactsubfrm" is the name I have given the subform control on my main form. I've tried adding the following lines in each of the If ... Then statemens in my code: Me.Contactsubfrm.LinkChildFields = "ContactID;ContactTypeID" Me.Contactsubfrm.LinkMasterFields = "ContactID;ContactTypeID" But this just causes the error message to popup sooner - as soon as the subform opens rather than when I try to select a control or move to a new record. This time I get "Run Time 3200" as part of the error message as well. Curiously, the record selectors on the subform are not grayed out in this scenario - but still result in an error message if I press any button on the record selector itself. As for the validation rules - I forgot to mention that I had already put these in place (as per the directions in your previous post). As for the problem itself, it occurs on both subforms: - when the NewIndiv opens up, the first record it shows is the existing record that I just entered. The message I quoted earlier pops up regardless of whether I'm trying to select a textbox on the subform or simply using the record selector (on either the main form or the subform itself) to create a new record. (By the way, the record selector on the subform remains grayed out) - the message also pops up when I open the NewOrgs subform (which shows a blank record except for the ContactID and ContactTypeID fields which are already filled in when the form opens). I'm not sure what you mean when you say I should be checking in Form_Current to make sure the appropriate subform is loaded. I can definitely see the subforms changing depending on the value in my combobox (for ContactType) if that's what you mean. TIA Bob "Graham Mandeno" wrote in message ... Hi Bob Your changes to the design sound fine. The only thing I would add is a validation rule for ContactTypeID: =1 in tblIndividuals =2 in tblOrganisations to ensure that one entity cannot accidentally "morph" into the other. As to your strange error, are you certain that you are not trying to change tblContacts.ContactTypeID for an *existing* record (the one you have previously entered)? Do you get the error if you are on a new, empty record? How have you set up the Link Master/Child Fields properties of your subform control? If you change the SourceObject of a subform control then you must also respecify the link fields, even though the field names have not changed. Also, you should be checking in Form_Current that the appropriate subform is loaded and changing it if necessary. If you're still having trouble, post the code behind your form and I'll try to reproduce the problem. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, This doesn't seem to be working properly for me. Here's what I've done so far: In tblContacts: - left the pk as it was (ie tblContactsID remains an autonumber pk field) - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblIndividuals: - deleted the original autonumber pk field (IndividualsID) - converted the existing ContactID (number) field as the new pk - inserted a new ContactTypeID (number) field - set the "default value" property to 1 (corresponding to the ContactTypeID for the "Indiv" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblOrganisations - deleted the original autonumber pk field (OrganisationsID) - converted the existing ContactID (number) field as the new pk - set the "default value" property to 2 (corresponding to the ContactTypeID for the "Orgs" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file I then created a 1:1 relationship based on the combined ContactID and ContactTypeID fields between tblContacts and tblIndividuals. I did this by selected the two fields in tblContacts and dragging them over to tblIndividuals. I created a 1:1 relationship between tblContacts and tblOrganisations in the same way. I then deleted all existing test data from tblContacts, tblIndividuals and tblOrganisations - starting with a clean slate. I then opened my client data-entry form. The main form has all fields from tblContacts. The subform is unbound, but I have inserted vba into the AfterUpdate event section of the form to ensure that the SourceObject of the subform control is changed to the required subform depending on the selected ContactType (selected from a combo box). I can enter the first record of either tblIndividuals or tblOrganisations without difficulty - once I open the required subform the value of ContactID is the same as the autonumber pk in tblContacts, and the value of the ContactTypeID defaults to the relevant default value. However, after having entered this first record, if I then close and reopen the main form, I am prevented from creating any new reords in either table by a popup box which states "The record cannot be deleted or changed because the table "Individuals" includes related records." What could be causing this error? There are no "related" records in existence! TIA Bob "Graham Mandeno" wrote in message ... 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 |
#24
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Graham,
I just got home from work. I've emailed you a copy of the database as requested. The file is just of 600KB. TIA Bob "Graham Mandeno" wrote in message ... Hi Bob Is your database very large? Would you mind zipping it and emailing it to me? That would be quicker than me trying to set up something from scratch to try to reproduce the problem. Send it to ng1.g.mandeno at xoxy.net. -- Thanks! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Thanks for sticking with me on this. My setup is this: In the unbound subform control I have already the following settings: Link Child Fields - ContactID;ContactTypeID Link Master Fields - ContactID;ContactTypeID I do not create or amend this links programmatically in any way. The only thing I change via VBA is the SourceObject. As for the VBA Code itself, this is what I have: Private Sub cboContactType_AfterUpdate() If Me.cboContactType.Value = 1 Then ' Individual Me.Contactsubfrm.SourceObject = "NewIndiv" End If If Me.cboContactType.Value = 2 Then ' Organisation Me.Contactsubfrm.SourceObject = "NewOrgs" End If End Sub I've placed this code in the "After Update" event for a combobox in my main form. The control itself is bound to the ContactTypeID field in my tblContacts, but the RowSource is bound to my tblContactType. "NewIndiv" is my modified subform for Individuals, and "NewOrgs" is my modified subform for Organisations. "Contactsubfrm" is the name I have given the subform control on my main form. I've tried adding the following lines in each of the If ... Then statemens in my code: Me.Contactsubfrm.LinkChildFields = "ContactID;ContactTypeID" Me.Contactsubfrm.LinkMasterFields = "ContactID;ContactTypeID" But this just causes the error message to popup sooner - as soon as the subform opens rather than when I try to select a control or move to a new record. This time I get "Run Time 3200" as part of the error message as well. Curiously, the record selectors on the subform are not grayed out in this scenario - but still result in an error message if I press any button on the record selector itself. As for the validation rules - I forgot to mention that I had already put these in place (as per the directions in your previous post). As for the problem itself, it occurs on both subforms: - when the NewIndiv opens up, the first record it shows is the existing record that I just entered. The message I quoted earlier pops up regardless of whether I'm trying to select a textbox on the subform or simply using the record selector (on either the main form or the subform itself) to create a new record. (By the way, the record selector on the subform remains grayed out) - the message also pops up when I open the NewOrgs subform (which shows a blank record except for the ContactID and ContactTypeID fields which are already filled in when the form opens). I'm not sure what you mean when you say I should be checking in Form_Current to make sure the appropriate subform is loaded. I can definitely see the subforms changing depending on the value in my combobox (for ContactType) if that's what you mean. TIA Bob "Graham Mandeno" wrote in message ... Hi Bob Your changes to the design sound fine. The only thing I would add is a validation rule for ContactTypeID: =1 in tblIndividuals =2 in tblOrganisations to ensure that one entity cannot accidentally "morph" into the other. As to your strange error, are you certain that you are not trying to change tblContacts.ContactTypeID for an *existing* record (the one you have previously entered)? Do you get the error if you are on a new, empty record? How have you set up the Link Master/Child Fields properties of your subform control? If you change the SourceObject of a subform control then you must also respecify the link fields, even though the field names have not changed. Also, you should be checking in Form_Current that the appropriate subform is loaded and changing it if necessary. If you're still having trouble, post the code behind your form and I'll try to reproduce the problem. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, This doesn't seem to be working properly for me. Here's what I've done so far: In tblContacts: - left the pk as it was (ie tblContactsID remains an autonumber pk field) - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblIndividuals: - deleted the original autonumber pk field (IndividualsID) - converted the existing ContactID (number) field as the new pk - inserted a new ContactTypeID (number) field - set the "default value" property to 1 (corresponding to the ContactTypeID for the "Indiv" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblOrganisations - deleted the original autonumber pk field (OrganisationsID) - converted the existing ContactID (number) field as the new pk - set the "default value" property to 2 (corresponding to the ContactTypeID for the "Orgs" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file I then created a 1:1 relationship based on the combined ContactID and ContactTypeID fields between tblContacts and tblIndividuals. I did this by selected the two fields in tblContacts and dragging them over to tblIndividuals. I created a 1:1 relationship between tblContacts and tblOrganisations in the same way. I then deleted all existing test data from tblContacts, tblIndividuals and tblOrganisations - starting with a clean slate. I then opened my client data-entry form. The main form has all fields from tblContacts. The subform is unbound, but I have inserted vba into the AfterUpdate event section of the form to ensure that the SourceObject of the subform control is changed to the required subform depending on the selected ContactType (selected from a combo box). I can enter the first record of either tblIndividuals or tblOrganisations without difficulty - once I open the required subform the value of ContactID is the same as the autonumber pk in tblContacts, and the value of the ContactTypeID defaults to the relevant default value. However, after having entered this first record, if I then close and reopen the main form, I am prevented from creating any new reords in either table by a popup box which states "The record cannot be deleted or changed because the table "Individuals" includes related records." What could be causing this error? There are no "related" records in existence! TIA Bob "Graham Mandeno" wrote in message ... 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 |
#25
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
Strange... haven't seen it yet. You might like to try graham at mvps dot org. -- Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, I just got home from work. I've emailed you a copy of the database as requested. The file is just of 600KB. TIA Bob "Graham Mandeno" wrote in message ... Hi Bob Is your database very large? Would you mind zipping it and emailing it to me? That would be quicker than me trying to set up something from scratch to try to reproduce the problem. Send it to ng1.g.mandeno at xoxy.net. -- Thanks! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Thanks for sticking with me on this. My setup is this: In the unbound subform control I have already the following settings: Link Child Fields - ContactID;ContactTypeID Link Master Fields - ContactID;ContactTypeID I do not create or amend this links programmatically in any way. The only thing I change via VBA is the SourceObject. As for the VBA Code itself, this is what I have: Private Sub cboContactType_AfterUpdate() If Me.cboContactType.Value = 1 Then ' Individual Me.Contactsubfrm.SourceObject = "NewIndiv" End If If Me.cboContactType.Value = 2 Then ' Organisation Me.Contactsubfrm.SourceObject = "NewOrgs" End If End Sub I've placed this code in the "After Update" event for a combobox in my main form. The control itself is bound to the ContactTypeID field in my tblContacts, but the RowSource is bound to my tblContactType. "NewIndiv" is my modified subform for Individuals, and "NewOrgs" is my modified subform for Organisations. "Contactsubfrm" is the name I have given the subform control on my main form. I've tried adding the following lines in each of the If ... Then statemens in my code: Me.Contactsubfrm.LinkChildFields = "ContactID;ContactTypeID" Me.Contactsubfrm.LinkMasterFields = "ContactID;ContactTypeID" But this just causes the error message to popup sooner - as soon as the subform opens rather than when I try to select a control or move to a new record. This time I get "Run Time 3200" as part of the error message as well. Curiously, the record selectors on the subform are not grayed out in this scenario - but still result in an error message if I press any button on the record selector itself. As for the validation rules - I forgot to mention that I had already put these in place (as per the directions in your previous post). As for the problem itself, it occurs on both subforms: - when the NewIndiv opens up, the first record it shows is the existing record that I just entered. The message I quoted earlier pops up regardless of whether I'm trying to select a textbox on the subform or simply using the record selector (on either the main form or the subform itself) to create a new record. (By the way, the record selector on the subform remains grayed out) - the message also pops up when I open the NewOrgs subform (which shows a blank record except for the ContactID and ContactTypeID fields which are already filled in when the form opens). I'm not sure what you mean when you say I should be checking in Form_Current to make sure the appropriate subform is loaded. I can definitely see the subforms changing depending on the value in my combobox (for ContactType) if that's what you mean. TIA Bob "Graham Mandeno" wrote in message ... Hi Bob Your changes to the design sound fine. The only thing I would add is a validation rule for ContactTypeID: =1 in tblIndividuals =2 in tblOrganisations to ensure that one entity cannot accidentally "morph" into the other. As to your strange error, are you certain that you are not trying to change tblContacts.ContactTypeID for an *existing* record (the one you have previously entered)? Do you get the error if you are on a new, empty record? How have you set up the Link Master/Child Fields properties of your subform control? If you change the SourceObject of a subform control then you must also respecify the link fields, even though the field names have not changed. Also, you should be checking in Form_Current that the appropriate subform is loaded and changing it if necessary. If you're still having trouble, post the code behind your form and I'll try to reproduce the problem. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, This doesn't seem to be working properly for me. Here's what I've done so far: In tblContacts: - left the pk as it was (ie tblContactsID remains an autonumber pk field) - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblIndividuals: - deleted the original autonumber pk field (IndividualsID) - converted the existing ContactID (number) field as the new pk - inserted a new ContactTypeID (number) field - set the "default value" property to 1 (corresponding to the ContactTypeID for the "Indiv" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblOrganisations - deleted the original autonumber pk field (OrganisationsID) - converted the existing ContactID (number) field as the new pk - set the "default value" property to 2 (corresponding to the ContactTypeID for the "Orgs" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file I then created a 1:1 relationship based on the combined ContactID and ContactTypeID fields between tblContacts and tblIndividuals. I did this by selected the two fields in tblContacts and dragging them over to tblIndividuals. I created a 1:1 relationship between tblContacts and tblOrganisations in the same way. I then deleted all existing test data from tblContacts, tblIndividuals and tblOrganisations - starting with a clean slate. I then opened my client data-entry form. The main form has all fields from tblContacts. The subform is unbound, but I have inserted vba into the AfterUpdate event section of the form to ensure that the SourceObject of the subform control is changed to the required subform depending on the selected ContactType (selected from a combo box). I can enter the first record of either tblIndividuals or tblOrganisations without difficulty - once I open the required subform the value of ContactID is the same as the autonumber pk in tblContacts, and the value of the ContactTypeID defaults to the relevant default value. However, after having entered this first record, if I then close and reopen the main form, I am prevented from creating any new reords in either table by a popup box which states "The record cannot be deleted or changed because the table "Individuals" includes related records." What could be causing this error? There are no "related" records in existence! TIA Bob "Graham Mandeno" wrote in message ... 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 |
#26
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Graham,
I must've missed your post last night. I've emailed another copy of the file to your new email address. Cheers Bob Graham Mandeno wrote: Hi Bob Strange... haven't seen it yet. You might like to try graham at mvps dot org. -- Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, I just got home from work. I've emailed you a copy of the database as requested. The file is just of 600KB. TIA Bob "Graham Mandeno" wrote in message ... Hi Bob Is your database very large? Would you mind zipping it and emailing it to me? That would be quicker than me trying to set up something from scratch to try to reproduce the problem. Send it to ng1.g.mandeno at xoxy.net. -- Thanks! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Thanks for sticking with me on this. My setup is this: In the unbound subform control I have already the following settings: Link Child Fields - ContactID;ContactTypeID Link Master Fields - ContactID;ContactTypeID I do not create or amend this links programmatically in any way. The only thing I change via VBA is the SourceObject. As for the VBA Code itself, this is what I have: Private Sub cboContactType_AfterUpdate() If Me.cboContactType.Value = 1 Then ' Individual Me.Contactsubfrm.SourceObject = "NewIndiv" End If If Me.cboContactType.Value = 2 Then ' Organisation Me.Contactsubfrm.SourceObject = "NewOrgs" End If End Sub I've placed this code in the "After Update" event for a combobox in my main form. The control itself is bound to the ContactTypeID field in my tblContacts, but the RowSource is bound to my tblContactType. "NewIndiv" is my modified subform for Individuals, and "NewOrgs" is my modified subform for Organisations. "Contactsubfrm" is the name I have given the subform control on my main form. I've tried adding the following lines in each of the If ... Then statemens in my code: Me.Contactsubfrm.LinkChildFields = "ContactID;ContactTypeID" Me.Contactsubfrm.LinkMasterFields = "ContactID;ContactTypeID" But this just causes the error message to popup sooner - as soon as the subform opens rather than when I try to select a control or move to a new record. This time I get "Run Time 3200" as part of the error message as well. Curiously, the record selectors on the subform are not grayed out in this scenario - but still result in an error message if I press any button on the record selector itself. As for the validation rules - I forgot to mention that I had already put these in place (as per the directions in your previous post). As for the problem itself, it occurs on both subforms: - when the NewIndiv opens up, the first record it shows is the existing record that I just entered. The message I quoted earlier pops up regardless of whether I'm trying to select a textbox on the subform or simply using the record selector (on either the main form or the subform itself) to create a new record. (By the way, the record selector on the subform remains grayed out) - the message also pops up when I open the NewOrgs subform (which shows a blank record except for the ContactID and ContactTypeID fields which are already filled in when the form opens). I'm not sure what you mean when you say I should be checking in Form_Current to make sure the appropriate subform is loaded. I can definitely see the subforms changing depending on the value in my combobox (for ContactType) if that's what you mean. TIA Bob "Graham Mandeno" wrote in message ... Hi Bob Your changes to the design sound fine. The only thing I would add is a validation rule for ContactTypeID: =1 in tblIndividuals =2 in tblOrganisations to ensure that one entity cannot accidentally "morph" into the other. As to your strange error, are you certain that you are not trying to change tblContacts.ContactTypeID for an *existing* record (the one you have previously entered)? Do you get the error if you are on a new, empty record? How have you set up the Link Master/Child Fields properties of your subform control? If you change the SourceObject of a subform control then you must also respecify the link fields, even though the field names have not changed. Also, you should be checking in Form_Current that the appropriate subform is loaded and changing it if necessary. If you're still having trouble, post the code behind your form and I'll try to reproduce the problem. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, This doesn't seem to be working properly for me. Here's what I've done so far: In tblContacts: - left the pk as it was (ie tblContactsID remains an autonumber pk field) - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblIndividuals: - deleted the original autonumber pk field (IndividualsID) - converted the existing ContactID (number) field as the new pk - inserted a new ContactTypeID (number) field - set the "default value" property to 1 (corresponding to the ContactTypeID for the "Indiv" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file In tblOrganisations - deleted the original autonumber pk field (OrganisationsID) - converted the existing ContactID (number) field as the new pk - set the "default value" property to 2 (corresponding to the ContactTypeID for the "Orgs" ContactType in tblContactTypes) - set the "required" property to Yes - created a new unique multifield index based on ContactID and ContactTypeID as per the instructions in the Access help file I then created a 1:1 relationship based on the combined ContactID and ContactTypeID fields between tblContacts and tblIndividuals. I did this by selected the two fields in tblContacts and dragging them over to tblIndividuals. I created a 1:1 relationship between tblContacts and tblOrganisations in the same way. I then deleted all existing test data from tblContacts, tblIndividuals and tblOrganisations - starting with a clean slate. I then opened my client data-entry form. The main form has all fields from tblContacts. The subform is unbound, but I have inserted vba into the AfterUpdate event section of the form to ensure that the SourceObject of the subform control is changed to the required subform depending on the selected ContactType (selected from a combo box). I can enter the first record of either tblIndividuals or tblOrganisations without difficulty - once I open the required subform the value of ContactID is the same as the autonumber pk in tblContacts, and the value of the ContactTypeID defaults to the relevant default value. However, after having entered this first record, if I then close and reopen the main form, I am prevented from creating any new reords in either table by a popup box which states "The record cannot be deleted or changed because the table "Individuals" includes related records." What could be causing this error? There are no "related" records in existence! TIA Bob "Graham Mandeno" wrote in message ... 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 |
#27
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
I found the second one in the spam bucket - it didn't like you for some reason :-) Don't know what happened to the first - maybe met the same fate. Anyway, the problem was that you were not switching the subform in Form_Current, so it *looked* like you were on a new record but you were actually changing the ContactType for an existing record. Try these modifications to your code - most you can just copy and paste: ' since you need to do it in more places than one, ' add a private proc to switch the subform Private Sub SetContactType() With Me.Contactsubfrm Select Case Me.cboContactType.Value Case 1 ' Individual .SourceObject = "NewIndiv" .Visible = True Case 2 ' Organisation .SourceObject = "NewOrgs" .Visible = True Case Else .Visible = False End Select End With End Sub ' Call it in both cboContactType_AfterUpdate AND Form_Current Private Sub cboContactType_AfterUpdate() Call SetContactType End Sub Private Sub Form_Current() Call SetContactType End Sub ' now, the icing on the cake to get rid of those nasty unfriendly messages Private Sub cboContactType_BeforeUpdate(Cancel As Integer) Dim sTable As String Dim sMsg As String If Not IsNull(cboContactType.OldValue) Then Select Case cboContactType.OldValue Case 1 sTable = "Individuals" Case 2 sTable = "Organisations" End Select If DCount("*", sTable, "ContactID=" & Me.ContactID) 0 Then sMsg = "If you change the type of this contact, then you must " _ & "first delete all related information from the " & sTable _ & " table." & vbCrLf & vbCrLf & "Do you really want to do this?" If MsgBox(sMsg, vbQuestion Or vbYesNo Or vbDefaultButton2) _ = vbYes Then CurrentDb.Execute "Delete * from " & sTable _ & " where ContactID=" & Me.ContactID, dbFailOnError Else Cancel = True cboContactType.Undo End If End If End If End Sub You should change your VBA project references too (ToolsReferences). Remove the reference to ADO (Microsoft ActiveX Data Objects 2.x) and add one to DAO (Microsoft DAO 3.6). Also, I suggest you make Contacts.ContactTypeID a required field. Finally, make your subforms both Single form view (not continuous or datasheet) and remove all record selectors and navigation buttons. Let me know how you get on :-) -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ups.com... Hi Graham, I must've missed your post last night. I've emailed another copy of the file to your new email address. Cheers Bob |
#28
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Whoo Hoo!
Thanks alot Graham. It's finally working. :-) Just one more question though, why do you prefer DAO over ADO? I've now acquired a copy of the "Microsoft Office Access 2003 Bible" and there's a small section in it that asserts that ADO is to be preferred over DAO because Microsoft does not plan to provide any future enhancements for the latter. I'm still starting out at this, so I'm not entirely clear on the other pros and cons of each. Regards Bob "Graham Mandeno" wrote in message ... Hi Bob I found the second one in the spam bucket - it didn't like you for some reason :-) Don't know what happened to the first - maybe met the same fate. Anyway, the problem was that you were not switching the subform in Form_Current, so it *looked* like you were on a new record but you were actually changing the ContactType for an existing record. Try these modifications to your code - most you can just copy and paste: ' since you need to do it in more places than one, ' add a private proc to switch the subform Private Sub SetContactType() With Me.Contactsubfrm Select Case Me.cboContactType.Value Case 1 ' Individual .SourceObject = "NewIndiv" .Visible = True Case 2 ' Organisation .SourceObject = "NewOrgs" .Visible = True Case Else .Visible = False End Select End With End Sub ' Call it in both cboContactType_AfterUpdate AND Form_Current Private Sub cboContactType_AfterUpdate() Call SetContactType End Sub Private Sub Form_Current() Call SetContactType End Sub ' now, the icing on the cake to get rid of those nasty unfriendly messages Private Sub cboContactType_BeforeUpdate(Cancel As Integer) Dim sTable As String Dim sMsg As String If Not IsNull(cboContactType.OldValue) Then Select Case cboContactType.OldValue Case 1 sTable = "Individuals" Case 2 sTable = "Organisations" End Select If DCount("*", sTable, "ContactID=" & Me.ContactID) 0 Then sMsg = "If you change the type of this contact, then you must " _ & "first delete all related information from the " & sTable _ & " table." & vbCrLf & vbCrLf & "Do you really want to do this?" If MsgBox(sMsg, vbQuestion Or vbYesNo Or vbDefaultButton2) _ = vbYes Then CurrentDb.Execute "Delete * from " & sTable _ & " where ContactID=" & Me.ContactID, dbFailOnError Else Cancel = True cboContactType.Undo End If End If End If End Sub You should change your VBA project references too (ToolsReferences). Remove the reference to ADO (Microsoft ActiveX Data Objects 2.x) and add one to DAO (Microsoft DAO 3.6). Also, I suggest you make Contacts.ContactTypeID a required field. Finally, make your subforms both Single form view (not continuous or datasheet) and remove all record selectors and navigation buttons. Let me know how you get on :-) -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ups.com... Hi Graham, I must've missed your post last night. I've emailed another copy of the file to your new email address. Cheers Bob |
#29
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Graham,
Actually I did have another question too - more out of curiosity than anything else. You mentioned earlier that to switch to ANSI-92 mode you need to go to Tools Options Tables/Queries. When I following these directions I get a form with a Table and a Query design section. In the Table design section, I get options to change the default field sizes, the default field type, and a box for "AutoIndex on Import/Create". In the Query design section, I get checkboxes to "show table names", "output all fields" and "enable autojoin". I also get radio buttons to "Run permissions" as "Owner's" or "User's". But I don't see anything that refers to ANSI mode. Any ideas why? Regards Bob "Bob" wrote in message ... Whoo Hoo! Thanks alot Graham. It's finally working. :-) Just one more question though, why do you prefer DAO over ADO? I've now acquired a copy of the "Microsoft Office Access 2003 Bible" and there's a small section in it that asserts that ADO is to be preferred over DAO because Microsoft does not plan to provide any future enhancements for the latter. I'm still starting out at this, so I'm not entirely clear on the other pros and cons of each. Regards Bob |
#30
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
Glad it's all working :-) One thing I forgot to say was you should change your PK field "ContactTypeID" from AutoNumber to Long integer. When you have values hard-coded in your app, it makes no sense to have Access automatically generating those values for you! On the DAO/ADO thing, I could not disagree more with the authors of "The Bible" (was that blasphemy? g) DAO is the native object model for Jet databases and, as such, is the "natural" way of communicating with them. ADO is a sort of Esperanto, and while it can achieve most things, albeit in a sometimes inefficient way, there are some things which require DAO. There were rumours flying around about 4-5 years ago that "DAO is dead", and that there would be no further development to the Jet engine, but this has proved very wrong, as can be seen from what has happened with Access 2007. At about that time, Mary Chipman, who most would consider a High Priestess in this area, wrote the following article: http://sqlserveradvisor.com/doc/05515 She also made this statement in a private forum, which I'm sure she would not mind being quoted: quote DAO was designed, customized, and tweaked specifically for the Jet engine. In an all-Access application, it will give you the best performance and the most complete feature set. It will undoubtedly continue to be supported as long as Jet ships as a database engine for Access. If you only program in Access against Jet, you probably won't ever need ADO. ADO was designed as a general-purpose data access wrapper around OLE DB and is not specific to a particular engine. If your Access application makes use of SQL Server data, then ADO will be a better choice when writing data access code (and is in fact used in an ADP where the Jet engine isn't present). DAO uses the Jet engine, which adds unnecessary overhead when coding against SQL Server, etc. If for some reason you can't use ADO, then ODBCDirect is a better choice when coding against SQLS because it bypasses Jet. You don't need to worry about learning ADO.NET unless you're building a VS.NET windows forms or an ASP.NET app, because it doesn't work in Access or any other COM apps--it requires the .NET Framework. The other developers you spoke to are right--ADO.NET is really a new and different technology that bears only a superficial resemblance to classic DAO/ADO. However, you still may need to learn ADO if you land a project that isn't being built using .NET -- VB6 will undoubtedly be around for years to come. Yes, the alphabet soup of data access technologies is confusing -- but there's sound technical reasons why you'd choose one data access method over another in a given situation. \quote -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Whoo Hoo! Thanks alot Graham. It's finally working. :-) Just one more question though, why do you prefer DAO over ADO? I've now acquired a copy of the "Microsoft Office Access 2003 Bible" and there's a small section in it that asserts that ADO is to be preferred over DAO because Microsoft does not plan to provide any future enhancements for the latter. I'm still starting out at this, so I'm not entirely clear on the other pros and cons of each. Regards Bob "Graham Mandeno" wrote in message ... Hi Bob I found the second one in the spam bucket - it didn't like you for some reason :-) Don't know what happened to the first - maybe met the same fate. Anyway, the problem was that you were not switching the subform in Form_Current, so it *looked* like you were on a new record but you were actually changing the ContactType for an existing record. Try these modifications to your code - most you can just copy and paste: ' since you need to do it in more places than one, ' add a private proc to switch the subform Private Sub SetContactType() With Me.Contactsubfrm Select Case Me.cboContactType.Value Case 1 ' Individual .SourceObject = "NewIndiv" .Visible = True Case 2 ' Organisation .SourceObject = "NewOrgs" .Visible = True Case Else .Visible = False End Select End With End Sub ' Call it in both cboContactType_AfterUpdate AND Form_Current Private Sub cboContactType_AfterUpdate() Call SetContactType End Sub Private Sub Form_Current() Call SetContactType End Sub ' now, the icing on the cake to get rid of those nasty unfriendly messages Private Sub cboContactType_BeforeUpdate(Cancel As Integer) Dim sTable As String Dim sMsg As String If Not IsNull(cboContactType.OldValue) Then Select Case cboContactType.OldValue Case 1 sTable = "Individuals" Case 2 sTable = "Organisations" End Select If DCount("*", sTable, "ContactID=" & Me.ContactID) 0 Then sMsg = "If you change the type of this contact, then you must " _ & "first delete all related information from the " & sTable _ & " table." & vbCrLf & vbCrLf & "Do you really want to do this?" If MsgBox(sMsg, vbQuestion Or vbYesNo Or vbDefaultButton2) _ = vbYes Then CurrentDb.Execute "Delete * from " & sTable _ & " where ContactID=" & Me.ContactID, dbFailOnError Else Cancel = True cboContactType.Undo End If End If End If End Sub You should change your VBA project references too (ToolsReferences). Remove the reference to ADO (Microsoft ActiveX Data Objects 2.x) and add one to DAO (Microsoft DAO 3.6). Also, I suggest you make Contacts.ContactTypeID a required field. Finally, make your subforms both Single form view (not continuous or datasheet) and remove all record selectors and navigation buttons. Let me know how you get on :-) -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ups.com... Hi Graham, I must've missed your post last night. I've emailed another copy of the file to your new email address. Cheers Bob |
Thread Tools | |
Display Modes | |
|
|