Entry of primary key data
in [General]
|
Prev: Backup button
Next: meus caminhos
From: CW on 24 Feb 2010 18:01 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 24 Feb 2010 18:16 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 24 Feb 2010 19:02 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 24 Feb 2010 19:18 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 24 Feb 2010 19:36
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] |