Entry of primary key data
in [General]
|
Prev: Backup button
Next: meus caminhos
From: Bob Quintal on 24 Feb 2010 20:03 =?Utf-8?B?Q1c=?= <CW(a)discussions.microsoft.com> wrote in news:2B41D16E-47BA-4225-A43F-CF7E9599474B(a)microsoft.com: > 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 > > "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 >> >> . >> > -- Bob Quintal PA is y I've altered my email address.
From: GP George on 24 Feb 2010 23:53 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" <CW(a)discussions.microsoft.com> wrote in message news:2B41D16E-47BA-4225-A43F-CF7E9599474B(a)microsoft.com... > 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: CW on 25 Feb 2010 06:41 Thanks Bob, that helps. And yes I have seen previous warnings about Aaron so I will steer clear! Thanks again CW "Bob Quintal" wrote: > =?Utf-8?B?Q1c=?= <CW(a)discussions.microsoft.com> wrote in > news:2B41D16E-47BA-4225-A43F-CF7E9599474B(a)microsoft.com: > > > 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 > > > > > "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 > >> > >> . > >> > > > > > > -- > Bob Quintal > > PA is y I've altered my email address. > . >
From: CW on 25 Feb 2010 06:43 Many thanks for the detailed advice CW "GP George" wrote: > 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" <CW(a)discussions.microsoft.com> wrote in message > news:2B41D16E-47BA-4225-A43F-CF7E9599474B(a)microsoft.com... > > 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: CW on 25 Feb 2010 06:44
John - that's good news and just what I hoped to hear Many thanks CW "John W. Vinson" wrote: > 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] > . > |