Entry of primary key data

[CW]

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

 

[a a r o n . k e m p f]

you should have in integer, identity for the primary key

 

[CW]

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

 

[Jeff Boyce]

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.

 

[John W. Vinson]

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.

 

[Bob Quintal]

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

Please ignore MO oops AAron. A primary key can be a text field, any
type of number or any combination of those up to 10 fields.

As to your question regarding the foreign key being entered into the
secondary table, it will happen automatically if you use the proper
form+subform design and you properly set the link (parent and child)
field properties in the subform control.

Q

 

[GP George]

Typically ( as you already knew <grin>) , we use the Autonumber for the
Primary Key in the one-side table. Actually, the Autonumber is a Long
Integer, not an Integer, so your foreign Key field should also be defined as
a Long Integer. (That is the kind of basic detail lost on some folks,
unfortunately.) Integers are limited to 65,535 values, whereas Long Integers
can have up to 2,147,483,647 values. This may or may not be relevant in your
particular database because it seems unlikely you'll have that many
companies to track. Nonetheless, because the Autonumber IS a long integer,
you should define the corresponding foreign key the same way.

Now, to address your actual question, the answer is that Access forms are
designed so that the Master and Child Linking fields manage the relationship
for you. In other words, if you properly design the form and subform so that
the "one-side" table provides records to the main form and the "many-side"
table provides records to the subform, the foreign keys in the subform will
be handled for you. If you define the Master Linking Field to be the primary
key, and the Child Linking field to be the foreign key, Access takes care of
the rest for you.


George

 

[CW]

Thanks Bob, that helps.
And yes I have seen previous warnings about Aaron so I will steer clear!
Thanks again
CW

 

[CW]

Many thanks for the detailed advice
CW

Typically ( as you already knew <grin>) , we use the Autonumber for the
Primary Key in the one-side table. Actually, the Autonumber is a Long
Integer, not an Integer, so your foreign Key field should also be defined as
a Long Integer. (That is the kind of basic detail lost on some folks,
unfortunately.) Integers are limited to 65,535 values, whereas Long Integers
can have up to 2,147,483,647 values. This may or may not be relevant in your
particular database because it seems unlikely you'll have that many
companies to track. Nonetheless, because the Autonumber IS a long integer,
you should define the corresponding foreign key the same way.

Now, to address your actual question, the answer is that Access forms are
designed so that the Master and Child Linking fields manage the relationship
for you. In other words, if you properly design the form and subform so that
the "one-side" table provides records to the main form and the "many-side"
table provides records to the subform, the foreign keys in the subform will
be handled for you. If you define the Master Linking Field to be the primary
key, and the Child Linking field to be the foreign key, Access takes care of
the rest for you.


George






.

 

[CW]

John - that's good news and just what I hoped to hear
Many thanks
CW

 

[CW]

Jeff - That's very helpful, just what I needed to know
Many thanks
CW

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.

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

.

 

[a a r o n . k e m p f]

I'm the only person on the newsgroup that knows WTF they're doing with
the worlds most popular database