From: CW on
I'm setting up a database of corporate customers, which will include our
contact persons at each of them. In some cases there are several contacts at
a company, with different roles.
I will create one table for the Companies and another for the Contacts and
then a subform for displaying these at the bottom of the main company form.
Presumably this is a classic one-to-many relationship and I will need a
primary key in the tblCompanies table (e.g. Company1) and then a similar
entry in the tblContacts table, for the link to be made.
Question - how does the key get entered into tblContacts? If there is a
relationship between the fields, will the relevant value (such as Company1)
be automatically passed into tblContacts when a new Contact record is
created, because that is how the main form and subform are linked ? Or does
the user have to input it manually?
Thanks
CW
From: a a r o n . k e m p f on
you should have in integer, identity for the primary key




On Feb 24, 3:01 pm, CW <C...(a)discussions.microsoft.com> wrote:
> I'm setting up a database of corporate customers, which will include our
> contact persons at each of them. In some cases there are several contacts at
> a company, with different roles.
> I will create one table for the Companies and another for the Contacts and
> then a subform for displaying these at the bottom of the main company form.
> Presumably this is a classic one-to-many relationship and I will need a
> primary key in the tblCompanies table (e.g. Company1) and then a similar
> entry in the tblContacts table, for the link to be made.
> Question - how does the key get entered into tblContacts? If there is a
> relationship between the fields, will the relevant value (such as Company1)
> be automatically passed into tblContacts when a new Contact record is
> created, because that is how the main form and subform are linked ? Or does
> the user have to input it manually?
> Thanks
> CW

From: CW on
OK fine I will use an integer.
Now, how about the question I asked - how does this get entered in the
secondary table - automatically by Access via the link between the parent
form and the subform? Or does it have to be entered separately in the subform
by the user?
Thanks
CW

"a a r o n . k e m p f @ g m a i l . c o" wrote:

> you should have in integer, identity for the primary key
>
>
>
>
> On Feb 24, 3:01 pm, CW <C...(a)discussions.microsoft.com> wrote:
> > I'm setting up a database of corporate customers, which will include our
> > contact persons at each of them. In some cases there are several contacts at
> > a company, with different roles.
> > I will create one table for the Companies and another for the Contacts and
> > then a subform for displaying these at the bottom of the main company form.
> > Presumably this is a classic one-to-many relationship and I will need a
> > primary key in the tblCompanies table (e.g. Company1) and then a similar
> > entry in the tblContacts table, for the link to be made.
> > Question - how does the key get entered into tblContacts? If there is a
> > relationship between the fields, will the relevant value (such as Company1)
> > be automatically passed into tblContacts when a new Contact record is
> > created, because that is how the main form and subform are linked ? Or does
> > the user have to input it manually?
> > Thanks
> > CW
>
> .
>
From: Jeff Boyce on
You can use, as a primary key, a value entered automatically by the
database. It has different names in different databases, but there's no
need to have the user enter the [Company] primary key value ... unless you
have some way of ensuring that two companies can't have the same companyID,
in which case your user COULD enter the key.

If your [Contacts] table (I assume you mean [CompanyContact]) has, as a
foreign key, a field that points back to the [Company] record that "owns"
it, you can use a main form/subform construction. Your main form would be
the [Company] form, and the subform would show the [CompanyContact] table
records that are related. You tell Access which fields to use as the
'parent' and 'child' fields, and Access handles putting the CompanyID into
the [CompanyContact] record ... automatically!

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"CW" <CW(a)discussions.microsoft.com> wrote in message
news:0C38626F-11E8-485B-B075-ADB3C55E5718(a)microsoft.com...
> I'm setting up a database of corporate customers, which will include our
> contact persons at each of them. In some cases there are several contacts
> at
> a company, with different roles.
> I will create one table for the Companies and another for the Contacts and
> then a subform for displaying these at the bottom of the main company
> form.
> Presumably this is a classic one-to-many relationship and I will need a
> primary key in the tblCompanies table (e.g. Company1) and then a similar
> entry in the tblContacts table, for the link to be made.
> Question - how does the key get entered into tblContacts? If there is a
> relationship between the fields, will the relevant value (such as
> Company1)
> be automatically passed into tblContacts when a new Contact record is
> created, because that is how the main form and subform are linked ? Or
> does
> the user have to input it manually?
> Thanks
> CW


From: John W. Vinson on
On Wed, 24 Feb 2010 15:01:01 -0800, CW <CW(a)discussions.microsoft.com> wrote:

>I'm setting up a database of corporate customers, which will include our
>contact persons at each of them. In some cases there are several contacts at
>a company, with different roles.
>I will create one table for the Companies and another for the Contacts and
>then a subform for displaying these at the bottom of the main company form.
>Presumably this is a classic one-to-many relationship and I will need a
>primary key in the tblCompanies table (e.g. Company1) and then a similar
>entry in the tblContacts table, for the link to be made.
>Question - how does the key get entered into tblContacts? If there is a
>relationship between the fields, will the relevant value (such as Company1)
>be automatically passed into tblContacts when a new Contact record is
>created, because that is how the main form and subform are linked ? Or does
>the user have to input it manually?
>Thanks
>CW

Please be aware that Aaron has only one answer for all questions - SQL/Server.

A Subform control has a "Master Link Field" and a "Child Link Field" property.
If you set the Master link Field to the name of the primary key of
tblCompanies, and the Child Link Field to the name of the foreign key field in
the Contacts table, Access will keep them in synch and will automatically
populate the foreign key with the value from the current record on the
mainform. You don't even need to make the field visible on either form unless
you wish to do so.
--

John W. Vinson [MVP]
 |  Next  |  Last
Pages: 1 2 3
Prev: Backup button
Next: meus caminhos