Linking Forms

[ChipVerdi55]

I am attempting to set up a database which uses an “IntakeForm” to register clients. The form has over 230 fields that may have dataentered. By using combo Boxes to store data when one and only one of multiplechoices can be selected; the number of “unique” fields is reduced to 129.Applying the “normalization Rules”, the fields break out into three groups with34 fields, 11 fields, and 84 fields. The 34 fields comprise the main data inputform and the 11 & 84 are separate. The 84 fields are 14 fields of the sameinformation for up to 6 household members. All are checkboxes and 1 householdmember may have from 0 to 14 items checked.


I defined three tables, with a query * on each, and designedthree forms which contain the respective above number fields.
I would like to know how to link and display the fields suchthat a data entry person can enter the data as he/she sees it on the “IntakeForm”
Currently, I have three forms with command buttons on themain form which calls the “Child” forms, but I cannot advance the child forms insync with the main form. On the main form I might be on record 3, but the childform is still on Record 1.

[June7]

Are you using form/subform arrangement? The Master/Child links properties of the subform container control will synchronize the records. Review http://office.microsoft.com/en-us/ac...010098674.aspx

[ChipVerdi55]

Thanks for the response. The first time that I attempted toset up the Form/Subform, I had already developed the subform, so I dragged anddropped it on the main form. I never saw the subform control. On a secondattempt I used the Access 2010 Wizard and the subform control was present so Iset up the Master child relationship. I then closed both forms and have notbeen able to find the subform control again.
I have Windows 7 (don't know if it is 32 or 64 bit, and Ihave Access 2010, again don't know if it is 32 or 64 bit).

[June7]

You can't find the subform control on the ribbon or on the form?

On the main form in design view, click on the 'subform'. This should select the container (see an orange border on edge?) and can view its properties. Click the 'subform' again and the object in the container should now be selected and can view its properties.

Master/Child link properties are set and records are still not synchronizing? Want to provide project for analysis? Follow instructions at bottom of my post.

[ChipVerdi55]

Thanks again. Clicking on the orange ring on the selected subform and then selecting properties did provide an opportunity to select the subform Control properties. Master child are now working fine. Super good advice.

[ChipVerdi55]

I am trying to set up data entry information from a rather large (# of Fields) Intake form. I have separated the tables to store this information into 3 Tables. I want to present the data entry screens to the data entry person just as they see it on the Intake Form. To do this I made 3 forms and set up a Master Form (33 fields) and two subforms (11 and 84 data entry fields respectively).

I followed the advice that I received in this forum and the Master Form and first subform are in sync and appear to be working fine. I can enter data on both the Master and Child, the data is retained, and as I step through the Master, the appropriate record and information appear in both the Master and the child.
However when I added the second subform (which I linked to the Master on the same field as I had linked the first subform), the second subform permits data entry but does not visibly retain the data when stepping through the records of the Master form. I did look in the Limitations Table and there were multiple entries, so some data was being stored, but it did not correlate to the Master.

Also the second subform appeared to shudder upon entry, and once I got a message that said recordset was editable. This is a long winded way of asking "How should I link the Master Child for Master and subform 1 and then Master and Subform2?"

[June7]

Multiple subforms should be able to link to main form.

We need to know more about your data structure.

Possible issue is that not actually saving the pk value into fk field.

If you want to provide file for analysis, follow instructions at bottom of my post.

[ChipVerdi55]

Sending dB Forms in zipped file

[June7]

You have an expression in the ControlSource for the AgencyIntakeID textbox of Limitations subform. This means the textbox is not bound and fk is not saved to table. Set the ControlSource to AgencyIntakeID field of the subform's RecordSource.

I don't understand data relationships.

Main form data source is AgencyIntakeTbl.

HouseholdMembersLimitations subform data source is HouseholdMembersLimitationstbl with fields:
HouseholdMemberLimitationsID (PK)
NeedsIdentifiedID (FK)
AgencyIntakeID (FK)
multiple similar name fields (HHM1BedMobility, HHM2BedMobility ... HHM6BedMobility, etc) which indicates non-normalized data structure - limit of 6 members.
Will there always be one and only one Limitations record per AgencyIntake record (1 to 1)?

NeedsIdentified subform data source is NeedsIdentifiedtbl with fields:
NeedsIdentifiedID (PK)
AgencyIntakeID (FK)
Will there always be one and only one Limitations record per AgencyIntake record (1 to 1)?

If the 1-to-1 relationships hold true, could be just one big table.

Consider: Intake table has household info, Needs table identifies needs for the household, Limitations table identifies limitations for each member of the household. I don't see reason for the NeedsIdentifiedID in Limitations table.

You have subforms sitting on top of each other and control visibility with code. Might consider using a tab control and place subforms on pages of the tab control.

[ChipVerdi55]

You have an expression in the ControlSource for theAgencyIntakeID textbox of Limitations subform. This means the textbox is notbound and fk is not saved to table. Set the ControlSource to AgencyIntakeIDfield of the subform's RecordSource.
Done Record Saving now happening
I don't understand data relationships.
Main form data source is AgencyIntakeTbl.
HouseholdMembersLimitations subform data source isHouseholdMembersLimitationstbl with fields:
HouseholdMemberLimitationsID (PK)
NeedsIdentifiedID (FK) This field wastemporary while I was trying to figure out the Master Child linking. I did notknow if I needed to link to the form or the first subform. I will remove it
AgencyIntakeID (FK)
multiple similar namefields (HHM1BedMobility, HHM2BedMobility ... HHM6BedMobility, etc) whichindicates non-normalized data structure - limit of 6 members.
An intake person can check 0-14 boxes for each of 6 Household Members(HHM). I don’t know how to normalize these 84 fields. HHM 1 is an Individual orHead of Household. The remaining 5 HHMs are not referenced anywhere else on theintake form. Total household members is restricted by the agency to a total of6.
Will there always beone and only one Limitations record per AgencyIntake record (1 to 1)?
YES

NeedsIdentified subform data source is NeedsIdentifiedtblwith fields:
NeedsIdentifiedID(PK)
AgencyIntakeID (FK)
Will there always beone and only one Limitations record per AgencyIntake record (1 to 1)?
Yes

If the 1-to-1 relationships hold true, could be just one bigtable.
I thought that normalization required me toseparate Basic Info, Needs, and Limitations. If not one large table cancertainly be done.
Consider: Intake table has household info, Needs tableidentifies needs for the household, Limitations table identifies limitationsfor each member of the household. I don't see reason for the NeedsIdentifiedIDin Limitations table.
I agree See statement above.
You have subforms sitting on top of each other and controlvisibility with code. Might consider using a tab control and place subforms onpages of the tab control.
Will look at tab control. I am concerned that Iwill have spatial issues

[June7]

Only need to separate the Info, Needs, Limitations if you will have 1 to many or many to many relationships or if not every Intake will have Needs and Limitations. Your data structure will result in the same amount of empty cells ('flat' files) even if split into separate tables.