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]

Re: Mulitple Main Forms Created by Sub Form Entries

Tracy wrote:
[color=blue]
> 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[/color]
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]

Re: Mulitple Main Forms Created by Sub Form Entries

Tracy wrote:
[color=blue]
> 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[/color]

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]

Re: Mulitple Main Forms Created by Sub Form Entries

Thanks 'salad', that worked perfectly!

Problem solved!!

Thank you

Tracy

Salad <oil@vinegar.co m> wrote in message news:<4021835A. F76C9C50@vinega r.com>...[color=blue]
> Tracy wrote:
>[color=green]
> > 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[/color]
>
> 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.[/color]