18 September 2011

Create a Form with Sub Form Using Form Wizard

Creating a form with a sub form in MS Access is best applied to the table that have the one-to-many relationship with another table. The objective is to eliminate redundancy in one or more fields in a table.

In general, tables that will be the form is a parent table, while the table to be used as a sub form is a child table that is relevant to the parent table. For example, forms invoices, bills, purchase orders, and various other forms that have a number of details of more than one item. Of the tables we have created, table tblOpenTransactionJournalParent will become a form with table tblOpenTransactionJournalChild that will become its sub form; table tblPermTransactionJournalParent and tblPermTransactionJournalChild are as well.

However, for reporting purposes, sometimes we also create a form with sub form. In the table relationship picture, we can see there are several child tables that have more than one parent table. Examples are the tabel tblOpenTransactionJournalChild  that have parent table tblMainAccount, tblDerivativeAccount1, and tblDerivativeAccount2. Although the contents of records from the child table has no relevance directly with them, but for reporting and analysis purposes, we can create a form with sub form.

How to create a form with sub form in MS Access

To create a form with sub form, do the following steps:
  1. On the navigation pane, point or highlight table tblPermTransactionJournalParent.
  2. Click Create tab and choose More Forms - Form Wizard in the Forms group.
  3. Add all the Available Fields in the Table/Queries "Table: tblOpenTransactionJournalParent" to Selected Fields.
  4. Click Combo Box Tables/Queries, and choose "Table: tblOpenTransactionJournalChild", like the picture below

  5. Except fields Number and Journal ID, add all the Available Fields into Selected Fields. Click Next button to continue.

  6. Leave as displayed above and click Next button.
  7. Choose option button Datasheet and click Next button.
  8. Choose AutoFormat1 and click Next button.
  9. Give title for Form: frmOpenTransactionJournalParent and Subform: frmOpenTransactionJournalChild

    Click Finish to Open the form to view or enter information
  10. MS Access should display the Form View of frmOpenTransactionJournalParent like this:

    Next, switch the Form View to Layout View. Change the title frmOpenTransactionJournalParent to Open Transaction Journal Entry. Highlight label frmOpenTransactionJournalChild and remove Layout by click right mouse to display shortcut menu, choose Layout - Remove, and finally delete label frmOpenTransactionJournalChild_Label.
  11. Save all form and sub form, and finish. Final display will look like this:

    You may change the format such as width of the fields JournalID,  JournalType, JournalNumber, etc. See this posting: Modifying the Journal Entry Form for more guidelines.

No comments :

Post a Comment