OK,
Rabbit... today, I seem to be more of the tortoise and mule... slow and dense:
I understand the concept behind the subform. I use them quite often if I need to show related information for either different but related queries or from different but related tables at the same time (not just a single table/query, but two or more in their own subform) - which is the only adavantage I've seen thus far.
So, let me try this with a different slant. Using just two related tables from many: Let us say, I have a database with a table lisiting inventory(and supporting tables), a table with possible installation points (systems... storage area are treated as installation locations), and a history table that tracks the inventory: when received, when and where it was installed to/removed from a given system, maintenance record, and when discarded. The history table is a "snapshot" in time as we need to be able to trace the who/what/when/where incase of issues or audits.
So let's take a look at the history table.
- tbl_history
-
[hist_ID] - Autonumber - PK
-
[FK_tblinv_id] - Long - required - Foriegn Key from inventory table.
-
[FK_tblsys_id] - Long - required - Foriegn Key from the systems table.
-
[FK_Action_ID] - Long - required - Foriegn Key from the actions table.
-
[FK_Employee_ID] - Long - required - Foriegn Key from the Employee table.
-
[comments] - text(100)
and the second... something along the lines of:
- tbl_inventory
-
[inv_ID] - Autonumber - PK
-
[FK_tblprd_id] - Long - required - Foriegn Key from inventory table.
-
[inv_sn] - text(50) - required.
-
[comments] - text(100)
So there are tons of queries and forms that can be created to view tbl_history.
Normally, I'll query the history table for a given inventory item, get the human readable names from the supporting tables, build the reports, etc...
However, we don't always need a paper report, just the information for review; thus, to do the same thing using a form; In the header, using a series of cascading comboboxes based on the tbl_inv (if the user doesn't know the [FK_tblinv_id] otherwise there is a textbox) to filter down the history table... set the filter... voila... the related records from tbl_history as per NeoPa's example. The nice thing here, is that I can use the human readable text in the cbobox.
Now, I can do the same thing using a subform. Create the master form showing the inventory item's [INV_ID][Manfacture][Desctiption] etc... build the subform based on tbl_history linking the realated parent/child field ([INV_ID]:[FK_tblinv_id])... once again, I get the same information that the filtered form version gave me (ofcourse I would base the forms on a query otherwise the FKs would be numeric instead of the human desired text).
Heres an example from a production db showing both a form_subform and a filtered form for the same information:
So, where is the advantage?
-z