Form Type? PLEASE HELP!!!!

F

frommonc

Have contact database with 2 tables, contacts and contact types. Have contact
form for entering contact data. Have control for contact types, but can only
select one type. How do I enable to select more than one type, as users may
belong to more than one type. Thought about creating tables for each type,
but would have to enter data for same contact into each type table that they
belong to. I know the most efficient way is to have one contact where
contacts can have multiple types, just don't know how to get there. PLEASE
HELP!!!!
 
D

Dirk Goldgar

frommonc said:
Have contact database with 2 tables, contacts and contact types. Have
contact form for entering contact data. Have control for contact
types, but can only select one type. How do I enable to select more
than one type, as users may belong to more than one type. Thought
about creating tables for each type, but would have to enter data for
same contact into each type table that they belong to. I know the
most efficient way is to have one contact where contacts can have
multiple types, just don't know how to get there. PLEASE HELP!!!!
Click to expand...

You need three tables:

Contacts (one record per contact)

ContactTypes (one record per available contact type)

ContactsContactTypes (one record per contact+contact type,
meaning that this contact is of this type)

The ContactsContactTypes table would have a two-field primary key.
Suppose that the primary key of the Contacts table is a field called
ContactID, and the primary key of the ContactTypes table is a field
called ContactType. Then the ContactsContactTypes table would have both
those fields, ContactID and ContactType, and the two fields together
would constitute the primary key of ContactsContactTypes.

You'd use a form and subform arrangement to enter and view the contacts
and their types. The main form would be based on the Contacts table,
and the subform would be based on ContactsContactTypes. The subform
would be set for continuous forms view, and would be linked to the main
form by the ContactID (or whatever field(s) you designated as the
primary key of the Contacts table. Adding a record for the current
ContactID to the subform would involve choosing a ContactType, probably
from a combo box that has the ContactTypes table for its RowSource.
 
F

frommonc

Dirk,
In contact and contacttype tables already created field types for primary
keys are autonumber. Should they be the same type in ContactsContactTypes
table. Created table with ID and ContactTypeID as fields. These are the
default keys for contact and contacttypes. Currently set as data text type.
 
D

Douglas J. Steele

The ContactsContactTypes table should have Long Integers corresponding to
the fields in the Contacts and ContactTypes tables. In other words, if
you've got ContactId as an Autonumber in Contacts, and ContactTypeId as an
Autonumber in ContactTypes, then ContactsContactTypes should have fields
ContactId and ContactTypeId, both as Long Integers (and the two fields
together making up the primary key)
 
D

Dirk Goldgar

frommonc said:
Dirk,
In contact and contacttype tables already created field types for
primary keys are autonumber. Should they be the same type in
ContactsContactTypes table. Created table with ID and ContactTypeID
as fields. These are the default keys for contact and contacttypes.
Currently set as data text type.
Click to expand...

See Doug Steele's answer.
 
F

frommonc

Subform now shows multiple contact types, but when i go to create next
record, subform still shows contact type from previous record.
 
D

Douglas J. Steele

You'll need to post more details of how you've set up the form/subform
relationship. Also, make sure you haven't set default values for the fields
in the form or the table itself.
 
F

frommonc

When attempting to set both field in ContactsContactTypes as primary keys,
get error message: Index or primary key cannot contain a null value.
 
F

frommonc

Thanks for the help, but this is really getting frustrating. Guess I should
leave this stuff to you pros.
 
F

frommonc

Can u recommend any web sites that explain Access in as elementary a way as
possible?
 
D

Douglas J. Steele

That implies that you've got data in the table, and that you didn't set the
values for one or more of the fields in the primary key.
 
Top