sql - MS Access, create a relationship with a form and subform - Stack Overflow
The general idea is that you create a subform that's bound to your link table, and make one of the fields in the subform a combo box bound to. 1) On load picklist subform field based on a main form field value . can be achieved by creating a bi-directional lookup field based on the sub-form relation. Complex forms with data and data entry from many to many relationship tables · forms . Problems with LO Base Form and subforms synch [closed] · base.
For situations where the link master field is editable, a subform MUST be linked to the control rather than underlying field. Otherwise, the wrong data could be displayed or edited by the user. What we're considering is how the subforms should reference the Master. Linked on Control Good Linked Directly to Field bad If the link master field were MasterID which is an autonumber field and can't be edited, this would not be an issue.
It's also important that you rename the control so it's not the same name as the field name.
Setting Up Subforms One of the most powerful features of displaying data is the use of subforms. A subform is a form with its own data source that is embedded in another "master" form. From our sample database, open the form frmEditableMaster: The form uses the table tblMaster as its RecordSource: The two fields happen to contain the same information, but the EditableID field can be modified.Access Tutorial 6 - Forms with SubForms
This makes it easy for you to reset it to the MasterID value after changing it. Referencing the Control is Critical if It Can be Edited To see why referencing the control is important, open the form and change the value of the ID value from 22 to 23, then move to the Description control: The control's value has changed, but the underlying value in the table has not because the record is not saved.
That only occurs when the user leaves the record.
However, the subforms displayed on the screen are totally different. The one linked to the control left shows the records related to Master ID 23, while the one linked to the field rightcontinues to show the saved value of By referencing the control, the subform linked on the control automatically updates when the control's value changes.
This ensures the subform always reflects the displayed master field value. By referencing the field, it's easy to get confused and think the subform records are related to the value when they aren't.
Users think they're looking at data related to one master record, when they were really viewing another one. If you chose to have Access create a new form for the subform instead of using an existing form, Access creates the new form object and adds it to the Navigation Pane. Create a subform by dragging one form onto another Use this procedure if you want to use an existing form as a main form, and you want to add one or more existing forms to that form as subforms.
In the Navigation Pane, right-click the form that you want to use as the main form, and then click Layout View.
Drag the form that you want to use as the subform from the Navigation Pane onto the main form. Access adds a subform control to the main form and binds the control to the form that you dragged from the Navigation Pane.
Access also tries to link the subform to the main form, based on the relationships that have been defined in your database. Repeat this step to add any additional subforms to the main form.
To verify that the linking was successful, on the Home tab, in the Views group, click View, click Form View, and then use the main form's record selector to advance through several records.
If the subform filters itself correctly for each employee, then the procedure is complete. If the previous test does not work, Access was unable to determine how to link the subform to the main form, and the Link Child Fields and Link Master Fields properties of the subform control are blank. You must set these properties manually by doing the following: Right-click the main form in the Navigation Pane, and then click Design View.
Microsoft Access Subforms Should Reference the Control Rather than Field for Link Master Fields
Click the subform control one time to select it. If the Property Sheet task pane is not displayed, press F4 to display it.
In the Property Sheet, click the Data tab. Click the Build button next to the Link Child Fields property box. The Subform Field Linker dialog box appears. In the Master Fields and Child Fields drop-down lists, select the fields that you want to link the forms with, and then click OK.
If you are not sure which fields to use, click Suggest to have Access try to determine the linking fields. If you do not see the field that you want to use to link the forms, you might need to edit the record source of the master form or child form to help make sure that the linking field is in it.
For example, if the form is based on a query, you should make sure that the linking field is present in the query results. Save the main form, switch to Form view, and then verify that the form works as expected.
Create a form that contains a subform (a one-to-many form)
Open a subform in a new window in Design view If you want to make design changes to a subform while you are working on its main form in Design view, you can open the subform in its own window: Click the subform to select it.
This property can be set to the following values: However, if you set the Default View property of a subform to Datasheet, then the subform will display as a datasheet on the main form. To set the Default View property of a subform: Close any open objects. In the Navigation Pane, right-click the subform and then click Design View. If the Property Sheet is not already displayed, press F4 to display it. In the drop-down list at the top of the Property Sheet, make sure Form is selected.
For example, if you want to specify Datasheet as the default view for the form, make sure that the Allow Datasheet View property is set to Yes. Save and close the subform, and then open the main form to check the results.
Top of Page Add related data to a form without creating a subform It is not always necessary to create a separate form object to display related data.