Mulitple Main Forms Created by Sub Form Entries

[Tracy]

Hi

I'm hoping this is a problem just because I'm a beginner at Access,
and so someone can help

I have created a database of companies, with a subform for contacts.
(seperate tables)

It worked perfectly until I got more than one contact for a company!

If I add a 2nd contact to the subform, when I go back into the
database, Ive got 2 records of the main form for the company, with the
subform, showing.

Its not adding a second record to the company table, that just says I
have 2 contacts attached, which is what I want.

How do I stop the record, when I view the form , duplicating itself
each time I add a second contact.

I've tried just deleting one of the records, but that deletes one of
the contacts as well (as I suppose effectively I've got one record for
each contact showing ?).

Thank you

Tracy

[Bas Cost Budde]

Tracy wrote:

Hi

I'm hoping this is a problem just because I'm a beginner at Access,
and so someone can help

I have created a database of companies, with a subform for contacts.
(seperate tables)

It worked perfectly until I got more than one contact for a company!

If I add a 2nd contact to the subform, when I go back into the
database, Ive got 2 records of the main form for the company, with the
subform, showing.

Its not adding a second record to the company table, that just says I
have 2 contacts attached, which is what I want.

How do I stop the record, when I view the form , duplicating itself
each time I add a second contact.

I've tried just deleting one of the records, but that deletes one of
the contacts as well (as I suppose effectively I've got one record for
each contact showing ?).

Thank you

Tracy

You are invited to read on the subject 'normalization'. Here, you need
another table, that contains the primary key of the company (some
number, I hope) AND let's say the type of contact, or maybe just an
identifier for the contact, alongside all other information for the contact.

--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

[Salad]

Tracy wrote:

Hi

I'm hoping this is a problem just because I'm a beginner at Access,
and so someone can help

I have created a database of companies, with a subform for contacts.
(seperate tables)

It worked perfectly until I got more than one contact for a company!

If I add a 2nd contact to the subform, when I go back into the
database, Ive got 2 records of the main form for the company, with the
subform, showing.

Its not adding a second record to the company table, that just says I
have 2 contacts attached, which is what I want.

How do I stop the record, when I view the form , duplicating itself
each time I add a second contact.

I've tried just deleting one of the records, but that deletes one of
the contacts as well (as I suppose effectively I've got one record for
each contact showing ?).

Thank you

Tracy

Your Company table should have a primary key. I recommend an autonumber.
Your contacts table should also have a primary key that is an autonumber
and it will contain the same name as the Company primary key. This is
called a foreign key and with it you can set a relationship between 2
tables; Company being the One's side (1 record per company) , Contacts
being the Child being the Many side (many records for 1 company.

Your main form would be the company form. A subform, with the link, would
be the company id. If you are using a query to join both of them together
I would suggest you change that.

IOW, create a form for just the master (Company) and create another
separate form for Child (Contacts). Open up the Company form, click the
Subform and select the Contact form. That should solve your problem.

[Tracy]

Thanks 'salad', that worked perfectly!

Problem solved!!

Thank you

Tracy

Salad <oi*@vinegar.com> wrote in message news:<40***************@vinegar.com>...

Tracy wrote:

Hi

I'm hoping this is a problem just because I'm a beginner at Access,
and so someone can help

I have created a database of companies, with a subform for contacts.
(seperate tables)

It worked perfectly until I got more than one contact for a company!

If I add a 2nd contact to the subform, when I go back into the
database, Ive got 2 records of the main form for the company, with the
subform, showing.

Its not adding a second record to the company table, that just says I
have 2 contacts attached, which is what I want.

How do I stop the record, when I view the form , duplicating itself
each time I add a second contact.

I've tried just deleting one of the records, but that deletes one of
the contacts as well (as I suppose effectively I've got one record for
each contact showing ?).

Thank you

Tracy

Your Company table should have a primary key. I recommend an autonumber.
Your contacts table should also have a primary key that is an autonumber
and it will contain the same name as the Company primary key. This is
called a foreign key and with it you can set a relationship between 2
tables; Company being the One's side (1 record per company) , Contacts
being the Child being the Many side (many records for 1 company.

Your main form would be the company form. A subform, with the link, would
be the company id. If you are using a query to join both of them together
I would suggest you change that.

IOW, create a form for just the master (Company) and create another
separate form for Child (Contacts). Open up the Company form, click the
Subform and select the Contact form. That should solve your problem.