How to enforce subtypes/supertypes in Access 2000?

Hi Graham,

Thanks for the tips. I'll re-examine the use of the autonumbers in the
two sub-tables.

As to the use of table-level constraints, how do I set this up? I
mean, I know I can specify that a particular field can't be null. But
how do I force a check on the null value of another field (field B)
before allowing one field (field A) to be null? Along the same lines,
how can I make sure that at least one field must be have a value?

I assume that this would ordinarily be achievable at the form level -
but you mention table-level constraints. I'm all ears :-)

Going off topic a bit, where the form is concerned I've noticed that my
database saves data automatically even if I close the form within
pressing save on the toolbar. I assume there is some kind of auto-save
when you enter data into a form - but this does not always happen.
Sometimes it saves, sometimes it doesn't. How do I force a prompt to
save every time the form closes? (I located some example code - which
I don't have handy - but it does not seem to work).


TIA
Bob

Graham Mandeno wrote:

Hi Bob

PMFJI :-)

In a Jet (Access) database, there is no way to enforce this sort of entity
subclassing at the engine level. The only way to do that would be to have
two FK fields in tblContacts - one for IndivID and one for OrgID, and have a
table-level constraint (validation rule) to specify that they cannot both be
Null.

Using the structure you have, you can go most of the way there using a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate (AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Bob" wrote in message
...
Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

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