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
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