multiple tables, multiple forms, one record

[FullyBaked]

i have several tables making up one record. for each table their is a unique form. how do i link these tables and forms to create one overall record?
should i type in a persons name and bring up their personal details on form one for example, i would then like to click on the buttons i have created that open all other forms and have their relating record showing on that form.

[Toyman]

i have several tables making up one record. for each table their is a unique form. how do i link these tables and forms to create one overall record?
should i type in a persons name and bring up their personal details on form one for example, i would then like to click on the buttons i have created that open all other forms and have their relating record showing on that form.

One way of doing this is create a main based on the basic info on the person. Use a tab control in the form and insert subform in each tab for different information about that person. All of the other ralated table about that person should have a foreign key field containing the primary key from the main table

[FullyBaked]

Thanks. Working perfectly and as a result everything is a lot more simplified. one form and info across tabs as opposed to +-8 forms.
thanks for your advice. will just need to play with relationships now to get everything correct.

[FullyBaked]

okay. next problem!!!! hope you don't mind....

have created tabs but am now unsure of how to organize relationships correctly....

i have the main form, personal details, linked to corresponding table with "intake number" as the primary key an set to autonumber.

in all other tables i have created an "intake number" field but have not made this a primary key and have set as number as opposed to autonumber. is this correct?

then i have created a relationship between the primary key "Intake Number" in personal details form (My main form) and the "Intake number" (not set to primary key and set as number) in all of my other tables.

the problem i receive is as follows:

the fields with the same name as those on the "personal details" table such as surname etc are automatically completed on the other forms, however this field is for the surname of a family member and not the prson whose details we are collecting.

all the rest of my fields give me #Name? and i am unable to edit them.

[Toyman]

okay. next problem!!!! hope you don't mind....

have created tabs but am now unsure of how to organize relationships correctly....

i have the main form, personal details, linked to corresponding table with "intake number" as the primary key an set to autonumber.

in all other tables i have created an "intake number" field but have not made this a primary key and have set as number as opposed to autonumber. is this correct?

then i have created a relationship between the primary key "Intake Number" in personal details form (My main form) and the "Intake number" (not set to primary key and set as number) in all of my other tables.

the problem i receive is as follows:

the fields with the same name as those on the "personal details" table such as surname etc are automatically completed on the other forms, however this field is for the surname of a family member and not the prson whose details we are collecting.

all the rest of my fields give me #Name? and i am unable to edit them.

The primary key, "Intake Number" should be auto number. This is so that you will have a unique number for each record in the main table. The other tables should cotains records relating to the main table. In it, you should have a "foreign key". This is what you have described as having a "Intake Number" in each of your other tables. The "foreign key" field should be a number field because your primary key is a number field, even if it is an auto number. You will then create your subforms based on these "other" tables and insert them into each tab of the main form. Each subform you place into the main form must be linked to the main form "parent/child" relationship link via the primary key and the foreign key. This is the link in the forms property and not the relationship setting in the menu. Once this is done, everything you type into the subform, the foreign key of the subform will take on the primary key value of the main form. Everytime you navagate to a certain record, the subform will automatically reflect records associated with the main form

The last problem you described, the surname on the subform takes on the name in the main table indicates to me the bound table or query to the subform is not set up correctly. The bound table or query to the subform should only contain one table associated with the main record. For example, if your main record is "Personnel Information" with "EmpID" as the primary key, you would create the main form based only on the "Personnel Information" table. Lets say you want to put in a subform based on contact information of the employee, you would create a table, "Contacts". In this table, "Contacts" you would have information such as "Contact Name", "Contact Phone", "Contact Address"; you would also have a foreign field, "Cont_EmpID". The "Cont_EmpID" is the foreign key. This key will take on the value of the EmpID in the main record associated with the employee in which the supporting records are associated with.

When you create your subform for the contact information, you would create a subform based on the "Contact" table only. You would then insert the subform into the main form and link the primary key, "EmpID" with the foreign key, "Cont_EmpId" from the subform.

[FullyBaked]

hi, still having trouble i am afraid

i have my form set up as follows. entire database (Graphics) is included in one form. called Personal Details. This is where i enter the personal details of a resident staying with us. the form opens with this tab open and the table relating to personal detais tab contains my primary key - intake number. there are then several tabs next to the personal details tab and the fields in each tab are taken from the related tables. for example "person responsible for payment" - for the details of the person who will be paying for the person staying with us - not necessarily the residents themselves. I am still experiencing the problem where the secondary tabs/forms with foreign keys complete fields with the same names as those in the main tab (personal details) for example surname, address etc. you mentioned editing the parent child relationships of the forms as opposed to creating relationships between tables. i cannot find any options for editing the parent/child relationships in the form properties - perhaps i am selecting the wrong item and therefore getting the wrong properties - i click on the tab and then right click and select properties but no mention of parent/child in list of preferences - do i need relationships between tables as well as these parent child relationships or should i delete all relationships i have created between tables. is there any way i can send you a screen print so that you could get a better idea of what i have in front of me. i am also still receiving #name? in fields that do not have a field with the same name on the primary form. any suggestions - i think you might have to walk me through this one as my knowledge is very limited - haven't used access in several years until now - thanks for your help

[Toyman]

hi, still having trouble i am afraid

i have my form set up as follows. entire database (Graphics) is included in one form. called Personal Details. This is where i enter the personal details of a resident staying with us. the form opens with this tab open and the table relating to personal detais tab contains my primary key - intake number. there are then several tabs next to the personal details tab and the fields in each tab are taken from the related tables. for example "person responsible for payment" - for the details of the person who will be paying for the person staying with us - not necessarily the residents themselves. I am still experiencing the problem where the secondary tabs/forms with foreign keys complete fields with the same names as those in the main tab (personal details) for example surname, address etc. you mentioned editing the parent child relationships of the forms as opposed to creating relationships between tables. i cannot find any options for editing the parent/child relationships in the form properties - perhaps i am selecting the wrong item and therefore getting the wrong properties - i click on the tab and then right click and select properties but no mention of parent/child in list of preferences - do i need relationships between tables as well as these parent child relationships or should i delete all relationships i have created between tables. is there any way i can send you a screen print so that you could get a better idea of what i have in front of me. i am also still receiving #name? in fields that do not have a field with the same name on the primary form. any suggestions - i think you might have to walk me through this one as my knowledge is very limited - haven't used access in several years until now - thanks for your help

The setting to link Parent/Child relation can be accessed by right clicking the frame around the subform and selecting properties. If you want, post your db here and I will take a look

[FullyBaked]

hi - problem solved - i was trying to do something in a complicated way that could have been achieved simply. all info now in one table - used prefixes for field names to differentiate between info categories. created one form from table and then cut and pasted fields onto various tabs according to categories - also reduced the db size by 2MB! thanks for the help, much appreciated. one or two things to finalize and then can implement db.