Discussion: Advantages of a Subform vs. Filtering

[zmbd]

Ok,
So I've created forms with both filtering as in
http://bytes.com/topic/access/insigh...filtering-form, same concept with cascading comboboxs, and I have used Main/Subform formats too for related tables/queries.

I think it is my inexperience in that I do not see the inherent advantages of the subform vs. the filtered form and have used both methods depending on what forms I'd already constructed (and their underlying vba). Both methods appear to correlate the related record(s), both can allow editing, deleting, and adding records and so forth.

When and why would one method be a better solution than the other?

I've tried Google/Bing/etc... to find an article about this without much success and I haven't seen a discussion, or at least the search didn't return it, here about it either.

The discussion would be great, a link(s) pointing me in the correct direction is also appreciated.

-Z

[Rabbit]

Filtering is used to find records that match a set of criteria while a subform is used to show related records in a different table. They're used for different purposes.

[zmbd]

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

[Rabbit]

I think in this case I would go with a subform. Simply because it requires no code. So it will work even if the user has vba/macros disabled. I avoid code whenever possible because of that.

[zmbd]

Rabbit, that's a very good point with it working if the vba is disabled.

So it brings another question to mind that I'll start in a new thread about the controls because I'd really like to have other people's opinions regarding this topic here.

-z