Urgent help with linking tables via "save" command

[Guest]

Hi

I am very new to Access and have recently built my first database (using
Acess 2003), the purpose of which is essentially to enter our client firm
records and the details of related contacts within each firm. Firms can have
more than one contact.

I've built the database created the input form (capturing both firm and
contact details) and all is working well but I have run into two small
problems where I believe a quick macro or programming solution is required.
I feel like after much hard work I'm very close but my knowledge of
programming is close to zero and I've run out of time!

The 2 issues are:
1. I have created a one to many relationship between my 2 main tables,
"Firm" and "Contact". The Firm table has a primary key field entitled
"MailingListID" that is simply generated by Autonumber. I have linked this
field to a number field of the same name in the Contact table but when I save
the record (just using a standard save command button), the "MailingListID"
is successfully generated in the Firm table but does not get entered into the
related Contact table as well. Leaving me with an unrelated/connected firm
and contact.

2. In most cases firms will have 2 or more contacts. I belive this is
accomoodated by the tables ("MailingListID" field against each contact
creates a link to a firm, this number can be held against multiple contact
records via the one to many relationship), I just need a form button that
saves the entered contact details, then clears all fields except the
"MailingListID" field ready for a second entry.

Can anyone help me please with the steps or code required to perform these
actions. I've scoured usergroups, manuals and help without any success.

Many thanks for any help you can provide.

Nicola

 

[Wolfgang Kais]

Hello Nicola.

Nicolawrote:

[...]
I've built the database created the input form (capturing both firm
and contact details) and all is working well but I have run into two
small problems where I believe a quick macro or programming
solution is required. I feel like after much hard work I'm very close
but my knowledge of programming is close to zero and I've run
out of time!

I think that you don't need programming for your solution.

The 2 issues are:
1. I have created a one to many relationship between my 2 main
tables, "Firm" and "Contact". The Firm table has a primary key
field entitled "MailingListID" that is simply generated by
Autonumber. I have linked this field to a number field of the
same name in the Contact table but when I save the record
(just using a standard save command button), the "MailingListID"
is successfully generated in the Firm table but does not get entered
into the related Contact table as well. Leaving me with an
unrelated/connected firm and contact.

When creating the relationship, did you check "referential integrity"?
If not, you should do it now.

2. In most cases firms will have 2 or more contacts.
I belive this is accomoodated by the tables ("MailingListID" field
against each contact creates a link to a firm, this number can
be held against multiple contact records via the one to many
relationship), I just need a form button that saves the entered
contact details, then clears all fields except the "MailingListID" field
ready for a second entry.

You can create a form that contains a subform using the form wizard.
Select the fields from the Firm table that you want to display in the
main form and then additionaly select the fields from the contact table
to be displayed in the subform (all on the same wizard page, you do
not need to select the MailingListID column from the contact table).
When asked for it, select "form with subform(s)".
Set the other options to what you need.
The wizard creates a main form and a subform.
You can enter the firm data in the main form and then enter contact data in
the subform. The subnform will be automatically linked to the main form via
MailingListID.
When entering a new contact record, Access automatically stores the
appropriate MailingListID from the firm in the contact table.

 

[Guest]

Thank you very much Wolfgang for your detailed advice. It is much
appreciated and has resolved my problems.

Thanks again.

Nicola

Hello Nicola.

Nicolawrote:

[...]
I've built the database created the input form (capturing both firm
and contact details) and all is working well but I have run into two
small problems where I believe a quick macro or programming
solution is required. I feel like after much hard work I'm very close
but my knowledge of programming is close to zero and I've run
out of time!

I think that you don't need programming for your solution.

The 2 issues are:
1. I have created a one to many relationship between my 2 main
tables, "Firm" and "Contact". The Firm table has a primary key
field entitled "MailingListID" that is simply generated by
Autonumber. I have linked this field to a number field of the
same name in the Contact table but when I save the record
(just using a standard save command button), the "MailingListID"
is successfully generated in the Firm table but does not get entered
into the related Contact table as well. Leaving me with an
unrelated/connected firm and contact.

When creating the relationship, did you check "referential integrity"?
If not, you should do it now.

2. In most cases firms will have 2 or more contacts.
I belive this is accomoodated by the tables ("MailingListID" field
against each contact creates a link to a firm, this number can
be held against multiple contact records via the one to many
relationship), I just need a form button that saves the entered
contact details, then clears all fields except the "MailingListID" field
ready for a second entry.

You can create a form that contains a subform using the form wizard.
Select the fields from the Firm table that you want to display in the
main form and then additionaly select the fields from the contact table
to be displayed in the subform (all on the same wizard page, you do
not need to select the MailingListID column from the contact table).
When asked for it, select "form with subform(s)".
Set the other options to what you need.
The wizard creates a main form and a subform.
You can enter the firm data in the main form and then enter contact data in
the subform. The subnform will be automatically linked to the main form via
MailingListID.
When entering a new contact record, Access automatically stores the
appropriate MailingListID from the firm in the contact table.