How to enforce subtypes/supertypes in Access 2000?

[Graham Mandeno]

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

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

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 (View>Properties 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 Tools>Options>Tables/Queries. Use with
caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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


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


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

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

 

[Bob]

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

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

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

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 (View>Properties 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 Tools>Options>Tables/Queries. Use with
caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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


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


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

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

 

[Graham Mandeno]

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

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

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

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




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 (View>Properties 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 Tools>Options>Tables/Queries. Use with
caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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


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


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

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

 

[Bob]

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

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

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

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

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




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 (View>Properties 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 Tools>Options>Tables/Queries. Use
with caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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


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


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

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

 

[Graham Mandeno]

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 (Tools>References).
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

 

[Bob]

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

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 (Tools>References).
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

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

 

[Bob]

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

 

[Graham Mandeno]

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

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

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 (Tools>References).
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

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]

It sounds like you're using Access 2000, which does not have this option.
(This would also explain why your VBA references were set to ADO by default,
and not DAO)

You don't want SQL-92 anyway, if you are working with a Jet database.

 

[Bob]

Hi Graham,

Yes, I run Access 2000 at home. We have access 2003 at work and I just
found the right settings on that one. I'll keep away from it anyway,
but its nice to know where the option is if I ever need it.

By the way, I think I found a slight bug in the Before_Update code you
supplied. What I have found is if I try to change the ContactType the
custom error message popups up as expected. If I then opt to delete
the existing record, everything is fine. The record is deleted and I
can still navigate the records using the record selector at the foot of
the main (Clients) form - again everything works as expected. But if I
opt to cancel the update instead, I end up getting the standard Access
message about not being able to change or delete the record (ie the one
I was getting before you came to the rescue ).

I came to the conclusion that the "cboContactType.Undo" line simply
wasn't working. After looking in the help files on the "dirty" event,
I noticed this statement:

"The BeforeUpdate and AfterUpdate events for a record occur after you
have entered the new or changed data in the record and moved to another
record (or clicked Save Record on the Records menu), and therefore
AFTER the Dirty event for the record." (my emphasis)

So I focussed on the dirty event. Anyways, for the benefit of those
who might have been following this thread, I resolved the problem by
simply adding "Me.Form.Undo" in the line immediately after the existing
undo command.

These variations also worked ("Clients" is the name of my main form):

(1) Forms!Clients.Undo

(2)
If Forms("Clients").Dirty = True Then
Forms("Clients").Undo
End If

Everything seems to be fine now. Thanks again for all your help on
this issue.

Getting back to the ADO/DAO, my long term aim would be ultimately to
port my little Access program to VB.Net - purely because I'm a sucker
for punishment

If I code everything using DAO now, will I need to re-code everything
into ADO later, or does VB.Net not care which one you reference?

On a different tangent, if I port everything over to VB.Net, do you
know if I will need extra licences or if I have to upgrade to a
developer edition of some sort before I can distribute my VB.Net
program with an .mdb file? Will my VB.Net or existing ms office
licences cover this sort of thing (ie distribution)? And what's the
deal with runtime files? Will they be packaged with my .mdb file if I
incorporate that into a VB.Net program?

Thanks for letting me pick your brain.


TIA
Bob

It sounds like you're using Access 2000, which does not have this option.
(This would also explain why your VBA references were set to ADO by default,
and not DAO)

You don't want SQL-92 anyway, if you are working with a Jet database.
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

 

[Graham Mandeno]

Hi Bob

Answers inline...

By the way, I think I found a slight bug in the Before_Update code you
supplied. What I have found is if I try to change the ContactType the
custom error message popups up as expected. If I then opt to delete
the existing record, everything is fine. The record is deleted and I
can still navigate the records using the record selector at the foot of
the main (Clients) form - again everything works as expected. But if I
opt to cancel the update instead, I end up getting the standard Access
message about not being able to change or delete the record (ie the one
I was getting before you came to the rescue ).

That's annoying, isn't it? I reckon that this is a bug in Access. It
recognises that you have changed one of the fields in the relation but fails
to recognise that you have undone that change.

I have discovered a workaround, and that is to set the cascading updates
property on the relations between Contacts/Individuals and
Contacts/Organisations. That way, Access doesn't whinge when you change the
master side of the relationship - it just updates the foreign key field to
the same value as it had before. Of course, you can't really change the
value of Contacts.ContactTypeID because the constraint in the related table
allows only one valid value.

So... make this change and remove the extra Undo.

By the way, the correct form to use if you want to undo the entire form is:
Me.Undo

Me is already a form object, so Me is equivalent to Me.Form.

The reason you don't want to undo the entire form is that you might have
changed a whole lot of other fields in your Contacts record before
inadvertently changing the contact type. You are then stuck between the
rock and the hard place - either delete the related record and lose all the
indiv/org data, or undo the current record and lose all your changes.

Getting back to the ADO/DAO, my long term aim would be ultimately to
port my little Access program to VB.Net - purely because I'm a sucker
for punishment

You certainly are

this will be a complete rewrite. there is no easy way to convert an Access
app to .Net (or VB6 for that matter).

If I code everything using DAO now, will I need to re-code everything
into ADO later, or does VB.Net not care which one you reference?

I believe you can use the DAO object model from .Net - no reason why not.

On a different tangent, if I port everything over to VB.Net, do you
know if I will need extra licences or if I have to upgrade to a
developer edition of some sort before I can distribute my VB.Net
program with an .mdb file? Will my VB.Net or existing ms office
licences cover this sort of thing (ie distribution)? And what's the
deal with runtime files? Will they be packaged with my .mdb file if I
incorporate that into a VB.Net program?

No, you don't need any licences to open an MDB using .Net or VB6. All you
need is DAO360.DLL, but I'm pretty sure this comes with Windows anyway.

If you buy Visual Studio Tools for Office (VSTO) you get with it the Access
Developer Extensions (ADE) which allows you unlimited licence to distribute
the runtime version of Access, so you can install and run your Access app on
a computer that doesn't have Access (or even Office) installed.

Thanks for letting me pick your brain.

My pleasure

 

[Bob]

Thanks Graham,

I was a bit worried about Form.Undo myself (for the very reason you warn
against using it) - but it seems that access auto-saves any changes
everytime you move (eg tab) to the next field anyway. So I figured I was
safe or at worst would lose changes only to the current field. Your
solution is, of course, much better. I updated the relationships and
removed the extra undo and everything works. Thanks yet again.

As for porting to VB.Net, the learning curve for Access just seems a little
easier to climb than VB.Net; especially when you have very little spare
time. While I always enjoy pulling my hair out (to the point that I'm sure
I'm experiencing premature balding :-D ), I'm also keen to see some
progress. With Access, I hope to see a working prototype a little sooner.

I was rather hoping that the required runtime files might come with VB.Net
itself. I guess I'll have to invest in a copy of VSTO.

While I still have your attention, I'd like to ask a new question related to
form/subform design. I would like to move all of my contact phone and fax
numbers into a new table (tblTeleComs). I have created a Junction table
tblContactComs to enable a many to many relationship between tblContacts and
tblTeleComs. The table structions for the two new tables are as follows:

tblTeleComs
ComID (pk - autonumber)
ComType [to store text descriptions like "Work Tel" or "Home Tel")
ComNumber

tblContactComs
ContactID (pk - number)
ComID (pk - number)

I've created a new subform (subfrmContactNumbers) which I have set up as a
continuous form. This subform is linked to tblTeleComs.

What I would like to do is have two subforms on my existing main form. I
want my existing subform (NewIndiv/NewOrg) to go on the left, and my new
form to sit immediately to the side of that. This way all phone and fax
numbers will run down the right hand side of the main form. I want to the
user to be able to add new numbers on this form by simply pressing a command
button - and have a new blank row appear immediately below the existing fax
and phone numbers ready for a new number to be inserted. (I hope this is
clear).

Anyway, my problem is that I don't have a ContactID in tblTeleComs so how do
I link the new subform to my main form?



TIA
Bob

Hi Bob

Answers inline...

By the way, I think I found a slight bug in the Before_Update code you
supplied. What I have found is if I try to change the ContactType the
custom error message popups up as expected. If I then opt to delete
the existing record, everything is fine. The record is deleted and I
can still navigate the records using the record selector at the foot of
the main (Clients) form - again everything works as expected. But if I
opt to cancel the update instead, I end up getting the standard Access
message about not being able to change or delete the record (ie the one
I was getting before you came to the rescue ).

That's annoying, isn't it? I reckon that this is a bug in Access. It
recognises that you have changed one of the fields in the relation but
fails to recognise that you have undone that change.

I have discovered a workaround, and that is to set the cascading updates
property on the relations between Contacts/Individuals and
Contacts/Organisations. That way, Access doesn't whinge when you change
the master side of the relationship - it just updates the foreign key
field to the same value as it had before. Of course, you can't really
change the value of Contacts.ContactTypeID because the constraint in the
related table allows only one valid value.

So... make this change and remove the extra Undo.

By the way, the correct form to use if you want to undo the entire form
is:
Me.Undo

Me is already a form object, so Me is equivalent to Me.Form.

The reason you don't want to undo the entire form is that you might have
changed a whole lot of other fields in your Contacts record before
inadvertently changing the contact type. You are then stuck between the
rock and the hard place - either delete the related record and lose all
the indiv/org data, or undo the current record and lose all your changes.

Getting back to the ADO/DAO, my long term aim would be ultimately to
port my little Access program to VB.Net - purely because I'm a sucker
for punishment

You certainly are

this will be a complete rewrite. there is no easy way to convert an
Access app to .Net (or VB6 for that matter).

If I code everything using DAO now, will I need to re-code everything
into ADO later, or does VB.Net not care which one you reference?

I believe you can use the DAO object model from .Net - no reason why not.

On a different tangent, if I port everything over to VB.Net, do you
know if I will need extra licences or if I have to upgrade to a
developer edition of some sort before I can distribute my VB.Net
program with an .mdb file? Will my VB.Net or existing ms office
licences cover this sort of thing (ie distribution)? And what's the
deal with runtime files? Will they be packaged with my .mdb file if I
incorporate that into a VB.Net program?

No, you don't need any licences to open an MDB using .Net or VB6. All you
need is DAO360.DLL, but I'm pretty sure this comes with Windows anyway.

If you buy Visual Studio Tools for Office (VSTO) you get with it the
Access Developer Extensions (ADE) which allows you unlimited licence to
distribute the runtime version of Access, so you can install and run your
Access app on a computer that doesn't have Access (or even Office)
installed.

Thanks for letting me pick your brain.

My pleasure
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Bob]

Hi Graham,

Don't trouble yourself with my last query. I got access to create a new
form using my junction and TeleComs table and then used the resulting form
as my source for the subform control in my main form. This achieves what I
want except that the control does not automatically resize to enable all
records in the subform to be seen. Is there a way to do this?


Regards
Bob

Thanks Graham,

I was a bit worried about Form.Undo myself (for the very reason you warn
against using it) - but it seems that access auto-saves any changes
everytime you move (eg tab) to the next field anyway. So I figured I was
safe or at worst would lose changes only to the current field. Your
solution is, of course, much better. I updated the relationships and
removed the extra undo and everything works. Thanks yet again.

As for porting to VB.Net, the learning curve for Access just seems a
little easier to climb than VB.Net; especially when you have very little
spare time. While I always enjoy pulling my hair out (to the point that
I'm sure I'm experiencing premature balding :-D ), I'm also keen to see
some progress. With Access, I hope to see a working prototype a little
sooner.

I was rather hoping that the required runtime files might come with VB.Net
itself. I guess I'll have to invest in a copy of VSTO.

While I still have your attention, I'd like to ask a new question related
to form/subform design. I would like to move all of my contact phone and
fax numbers into a new table (tblTeleComs). I have created a Junction
table tblContactComs to enable a many to many relationship between
tblContacts and tblTeleComs. The table structions for the two new tables
are as follows:

tblTeleComs
ComID (pk - autonumber)
ComType [to store text descriptions like "Work Tel" or "Home Tel")
ComNumber

tblContactComs
ContactID (pk - number)
ComID (pk - number)

I've created a new subform (subfrmContactNumbers) which I have set up as a
continuous form. This subform is linked to tblTeleComs.

What I would like to do is have two subforms on my existing main form. I
want my existing subform (NewIndiv/NewOrg) to go on the left, and my new
form to sit immediately to the side of that. This way all phone and fax
numbers will run down the right hand side of the main form. I want to the
user to be able to add new numbers on this form by simply pressing a
command button - and have a new blank row appear immediately below the
existing fax and phone numbers ready for a new number to be inserted. (I
hope this is clear).

Anyway, my problem is that I don't have a ContactID in tblTeleComs so how
do I link the new subform to my main form?



TIA
Bob

Hi Bob

Answers inline...

By the way, I think I found a slight bug in the Before_Update code you
supplied. What I have found is if I try to change the ContactType the
custom error message popups up as expected. If I then opt to delete
the existing record, everything is fine. The record is deleted and I
can still navigate the records using the record selector at the foot of
the main (Clients) form - again everything works as expected. But if I
opt to cancel the update instead, I end up getting the standard Access
message about not being able to change or delete the record (ie the one
I was getting before you came to the rescue ).

That's annoying, isn't it? I reckon that this is a bug in Access. It
recognises that you have changed one of the fields in the relation but
fails to recognise that you have undone that change.

I have discovered a workaround, and that is to set the cascading updates
property on the relations between Contacts/Individuals and
Contacts/Organisations. That way, Access doesn't whinge when you change
the master side of the relationship - it just updates the foreign key
field to the same value as it had before. Of course, you can't really
change the value of Contacts.ContactTypeID because the constraint in the
related table allows only one valid value.

So... make this change and remove the extra Undo.

By the way, the correct form to use if you want to undo the entire form
is:
Me.Undo

Me is already a form object, so Me is equivalent to Me.Form.

The reason you don't want to undo the entire form is that you might have
changed a whole lot of other fields in your Contacts record before
inadvertently changing the contact type. You are then stuck between the
rock and the hard place - either delete the related record and lose all
the indiv/org data, or undo the current record and lose all your changes.

Getting back to the ADO/DAO, my long term aim would be ultimately to
port my little Access program to VB.Net - purely because I'm a sucker
for punishment

You certainly are

this will be a complete rewrite. there is no easy way to convert an
Access app to .Net (or VB6 for that matter).

If I code everything using DAO now, will I need to re-code everything
into ADO later, or does VB.Net not care which one you reference?

I believe you can use the DAO object model from .Net - no reason why not.

On a different tangent, if I port everything over to VB.Net, do you
know if I will need extra licences or if I have to upgrade to a
developer edition of some sort before I can distribute my VB.Net
program with an .mdb file? Will my VB.Net or existing ms office
licences cover this sort of thing (ie distribution)? And what's the
deal with runtime files? Will they be packaged with my .mdb file if I
incorporate that into a VB.Net program?

No, you don't need any licences to open an MDB using .Net or VB6. All
you need is DAO360.DLL, but I'm pretty sure this comes with Windows
anyway.

If you buy Visual Studio Tools for Office (VSTO) you get with it the
Access Developer Extensions (ADE) which allows you unlimited licence to
distribute the runtime version of Access, so you can install and run your
Access app on a computer that doesn't have Access (or even Office)
installed.

Thanks for letting me pick your brain.

My pleasure
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Bob

No doubt you worked out that you should base your subform's recordsource,
not on tblTeleComs, but on a query comprising both tblTeleComs AND
tblContactComs. That way you have a ContactID in your recordsource to be
the link child field.

The total height required by a form may be calculated as follows:

(<height of detail section> * <number of records>) + <height of header and
footer (if present)>

In VBS terms:

Public Function FormHeightRequired(frm as Form) as Long
Dim lHeight as Long
With frm
.RecordsetClone.MoveLast
lHeight = .Section(acDetail).Height * .RecordsetClone.RecordCount
On Error Resume Next
' in case no header/footer
lHeight = lHeight + .Section(acHeader).Height +
..Section(acFooter).Height
FormHeightRequired = lHeight
End With
End Function

You can call this in the Current event proc of your main form to set the
height of the subform.

With Me.[SubformControl]
.Height = FormHeightRequired(.Form) + <some constant>
End With

The <some constant> is an extra bit you will have to fiddle with to
accommodate the subform control's border. Start with about 30.

You probably want to specify a limit, in case the required space is not
available. In this case, you can turn on a vertical scrollbar for the
subform:

.Form.ScrollBars = 2 ' vertical only

or, if there is enough space:

.Form.ScrollBars = 0 ' no scrollbars

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Don't trouble yourself with my last query. I got access to create a new
form using my junction and TeleComs table and then used the resulting form
as my source for the subform control in my main form. This achieves what
I want except that the control does not automatically resize to enable all
records in the subform to be seen. Is there a way to do this?


Regards
Bob

Thanks Graham,

I was a bit worried about Form.Undo myself (for the very reason you warn
against using it) - but it seems that access auto-saves any changes
everytime you move (eg tab) to the next field anyway. So I figured I was
safe or at worst would lose changes only to the current field. Your
solution is, of course, much better. I updated the relationships and
removed the extra undo and everything works. Thanks yet again.

As for porting to VB.Net, the learning curve for Access just seems a
little easier to climb than VB.Net; especially when you have very little
spare time. While I always enjoy pulling my hair out (to the point that
I'm sure I'm experiencing premature balding :-D ), I'm also keen to see
some progress. With Access, I hope to see a working prototype a little
sooner.

I was rather hoping that the required runtime files might come with
VB.Net itself. I guess I'll have to invest in a copy of VSTO.

While I still have your attention, I'd like to ask a new question related
to form/subform design. I would like to move all of my contact phone and
fax numbers into a new table (tblTeleComs). I have created a Junction
table tblContactComs to enable a many to many relationship between
tblContacts and tblTeleComs. The table structions for the two new tables
are as follows:

tblTeleComs
ComID (pk - autonumber)
ComType [to store text descriptions like "Work Tel" or "Home Tel")
ComNumber

tblContactComs
ContactID (pk - number)
ComID (pk - number)

I've created a new subform (subfrmContactNumbers) which I have set up as
a continuous form. This subform is linked to tblTeleComs.

What I would like to do is have two subforms on my existing main form. I
want my existing subform (NewIndiv/NewOrg) to go on the left, and my new
form to sit immediately to the side of that. This way all phone and fax
numbers will run down the right hand side of the main form. I want to
the user to be able to add new numbers on this form by simply pressing a
command button - and have a new blank row appear immediately below the
existing fax and phone numbers ready for a new number to be inserted.
(I hope this is clear).

Anyway, my problem is that I don't have a ContactID in tblTeleComs so how
do I link the new subform to my main form?



TIA
Bob

Hi Bob

Answers inline...

By the way, I think I found a slight bug in the Before_Update code you
supplied. What I have found is if I try to change the ContactType the
custom error message popups up as expected. If I then opt to delete
the existing record, everything is fine. The record is deleted and I
can still navigate the records using the record selector at the foot of
the main (Clients) form - again everything works as expected. But if I
opt to cancel the update instead, I end up getting the standard Access
message about not being able to change or delete the record (ie the one
I was getting before you came to the rescue ).

That's annoying, isn't it? I reckon that this is a bug in Access. It
recognises that you have changed one of the fields in the relation but
fails to recognise that you have undone that change.

I have discovered a workaround, and that is to set the cascading updates
property on the relations between Contacts/Individuals and
Contacts/Organisations. That way, Access doesn't whinge when you change
the master side of the relationship - it just updates the foreign key
field to the same value as it had before. Of course, you can't really
change the value of Contacts.ContactTypeID because the constraint in the
related table allows only one valid value.

So... make this change and remove the extra Undo.

By the way, the correct form to use if you want to undo the entire form
is:
Me.Undo

Me is already a form object, so Me is equivalent to Me.Form.

The reason you don't want to undo the entire form is that you might have
changed a whole lot of other fields in your Contacts record before
inadvertently changing the contact type. You are then stuck between the
rock and the hard place - either delete the related record and lose all
the indiv/org data, or undo the current record and lose all your
changes.

Getting back to the ADO/DAO, my long term aim would be ultimately to
port my little Access program to VB.Net - purely because I'm a sucker
for punishment

You certainly are

this will be a complete rewrite. there is no easy way to convert an
Access app to .Net (or VB6 for that matter).

If I code everything using DAO now, will I need to re-code everything
into ADO later, or does VB.Net not care which one you reference?

I believe you can use the DAO object model from .Net - no reason why
not.

On a different tangent, if I port everything over to VB.Net, do you
know if I will need extra licences or if I have to upgrade to a
developer edition of some sort before I can distribute my VB.Net
program with an .mdb file? Will my VB.Net or existing ms office
licences cover this sort of thing (ie distribution)? And what's the
deal with runtime files? Will they be packaged with my .mdb file if I
incorporate that into a VB.Net program?

No, you don't need any licences to open an MDB using .Net or VB6. All
you need is DAO360.DLL, but I'm pretty sure this comes with Windows
anyway.

If you buy Visual Studio Tools for Office (VSTO) you get with it the
Access Developer Extensions (ADE) which allows you unlimited licence to
distribute the runtime version of Access, so you can install and run
your Access app on a computer that doesn't have Access (or even Office)
installed.

Thanks for letting me pick your brain.

My pleasure
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Oh, by the way... I thought you might like to know that your first email
just arrived this morning.

God knows where it's been!

 

[Bob]

I guess snail mail has gone electronic :-D

Regards
Bob