none
Sub Forms and Tab Control primary key help RRS feed

  • Question

  • I am a school student working on a Microsoft access database, and have little experience in the software. The scope of the task is beyond what has been studied, and is complex.

    I have created an input form for students to enter their information using sub forms and tab controls. I have 5 separate tables that record specific data: student details, contact details etc. From these I made related forms. In my combined student input from, each tab in the tab control has a sub form from these individual table forms. After inputting all of my data, I opened the related tables and realised that the primary key used by all these tables/forms was only being recorded from the first tab (student details). In the other tables/forms (contact details etc.) a primary key for that record is given, but not the relating foreign key.

    When I created the sub forms I linked them through the wizard with this primary key, however, it does not record in my tables. When I created the blank from my initial record source was the Student Details tables, however I’m not sure if this has something to do with the problem. Is there a method to either fix or work around this issue?

    Sunday, February 4, 2018 3:50 AM

All replies

  • Tabs are part of the main form. Subforms, regardless of where they are located are related by the  by their master/child relationship properties.

    Assuming arguendo, subform1 has the primary key

    Subformb2 Master property is forms!mainform!subform1.form.primarykey;

    Subform2 Child property is forms!mainform!subform1.form.foreignkey.

    If subform2 doesn't tumble to the correct row when form1 changes rows, on the form1 AfterUpdate eevent, add forms!mainform!subform2.requery.

    Sunday, February 4, 2018 9:23 PM
  • Take a look at FormsDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates four possible interfaces for tables in a one-to-many relationship type: tabbed forms, linked forms in dialogue mode, linked forms in synchronized mode, and linked forms in which the 'child' form is not filtered to rows which relate to the main form's current record, but in which the record pointer is moved to the first record which relates to the main form's current record.

    The option for tabbed forms is the model for you to follow.  In this two subforms are placed on pages 2 and 3 of a tab control, while page 1 has controls bound to columns in the parent form's RecordSource table.

    If you open the form in design view and select either the second or third page of the tab control, and then select the subform control on that page, you'll see that the LinkMasterFields and LinkChildField properties of the subform control are each set to ClientID, i.e. the first references the primary key of the parent form, and the second references the corresponding foreign key in the subform's table.  By virtue of the linking mechanism, when a row is inserted into the subform the correct value is automatically inserted into the foreign key column.  Neither of these columns need to be shown in bound controls of course, as their values have no semantic significance; they merely do their work in the background.

    Ken Sheridan, Stafford, England

    Monday, February 5, 2018 6:30 PM
  • PS:  Out of interest, the way you have designed your subforms, binding each to one of the tables and embedding them all in a single unbound form, is how it is done if you want to 'correlate' the subforms.  This is done when you want the rows from the 'parent' table to be shown in continuous forms view.  As you then navigate from row to row in the 'parent' subform, the other subforms' recordsets are reloaded (which is what the Requery method does) so that they only show rows which reference the currently selected row in the 'parent' subform.

    You'll find an example of this in CorrelatedSubs.zip in my same OneDrive folder.  This little demo uses Northwind data as its example, and illustrates correlated subforms, and nested subforms.  In the latter the linking is done as described in my first reply.  In the former, however, the 'child' subform's RecordSource is a query which references the first subform's primary key as a parameter.  In the first subform's Current event procedure the 'child' subform is requeried so that they show only those rows related to the currently selected row in the first subform are shown.  When a new row is inserted into a subform the correct value is assigned to its foreign key column by code in its BeforeInsert event procedure.

    You could use correlated subforms to do what you want, but I think the simpler solution in my FormsDemo file is more appropriate to the task which you've been set.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, February 5, 2018 6:57 PM Typo corrected.
    Monday, February 5, 2018 6:56 PM