Choose table for subform?

[Matthew Snook]

Imagine a "Tbl_Event" table for recording events. (This is a database of fish culture info.) Fields are:

Event_ID (unique to each event)
Observation_Type (selected from limited list)
Event_Date (a date)
Entity_ID (linked to a fish entity)

This table can be linked to any other table in the database through the "Event_ID", depending upon the "Observation_Type." For instance, if the Observation_Type is "Metric", then there is a record in the "Tbl_Metric" table with a matching "Event_ID" and pertinent metric data. If the Observation_Type is "Mortality" then there is a record in the "Tbl_Mort" table with a matching "Event_ID" and some other data, like "Cause_Of_Death."

Now, I would like to choose from a list of "Events" in a subform, and have a second subform retrieve the pertinent info from another table. How do I write a procedure for this? The second form will not know which table to query until I choose an Event! Then it must read the Observation_Type, figure out which table to query, run to that table, find a matching Event_ID, and bring back all the fields in that table for the matching record. Is this doable?

Thanks,

Matt

 

[mattc]

Hey matt,
This shounds dooable, but I think that you will need to make a new form for each type of event. Meaning, you will neet a frm_mort, and a frm_babies or whatever. Then write a procedure that takes the value of "observation_type" and loads the apropriate subform.

That would be the easiest way I can think of
mattc

 

[Rich]

Can be done easily using a list box to display the required data, just switch its recordsource depending on your selection criteria.