Urgent help with linking tables via "save" command

[Guest]

*copy of msg posted in "Macros" discussion group aswell

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

 

[John Nurick]

Hi Nicola,

The standard (and VBA-free) way of handling this sort of relationship is
to use a main form bound to the Firm table, with a subform on it bound
to the Contact table, the two linked on the key field. If you do it that
way, Access automatically adds the key value from the Firm when you
create a new Contact record.

 

[Guest]

Thanks for such a quick response John, my day just got a whole lot better
knowing you're there. I did suspect there had to be non-code option! Your
suggestion is actually the way I built my form (Main form based on 'Firm'
Table, subform embedded based on 'Contact' table) so I was surprised when it
didn't handle it automatically. Obviously I have done something wrong.

At the risk of appearing like a complete dill, may I ask for clarification
on a couple of points please?

- I have set the sub-form as continuous by going to the subform
Properties-Format tab- Default view field- and selected "continuous
forms"...is this correct?

- Can you please explain how you would "link the two on the key field"? I
have linked the two table fields via a one-to-many relationship - is there
something else that requires linking?

- Any ideas about the second part of my original question, adding multiple
contacts to same firm via my form?

Many thanks John.

Nicola

 

[John Nurick]

Nicola,

In the subform control on the main form, check the Link Child Fields and
Link Master Fields property. If I understand you right, the "master"
field should of course be MailingListID, the primary key of the FIrm
table, and the child field is the related field in the Contact table
(which I guess you've also called MailingListID).

Once the subform is properly linked, the rest is pretty much automatic.
The subform will only display records related to the current record in
the main form, and when you add a record via the subform it is
automatically linked to the current record in the main form (i.e. the
"link child" field is given the current value of the "link master"
field). This happens no matter how many records you add via the subform,
and no matter whether the subform is in "form" view or continuous form
view.

 

[Guest]

Thanks a million for your advice John, all sorted.

 

[John Nurick]

A pleasure.

Thanks a million for your advice John, all sorted.