I'm considering to use a subform table for two or more main tables and I wonder what is the best way (and if it is a good idea after all).
Let me explain my problem with a simple example: I have to tables 'customers' and 'products' and each needs a function for creating comments. This can be easily set up with two tables 'customers_comments' and 'products_comments', both again connected with the subform object to the records in their corresponding main tables. This principle sure works, but is redundant and becomes unintelligible with an increasing amount of additional main tables that need a "comment function" too.
So I thought about "sharing" a subform table with more than one main table. While creating records in a subform object of the main table is easy (corresponding table and foreign key is clear), the other way is difficult: When I create a new comment in my 'comments' table I cannot easily assign this back to a main table record, since I need not only the record ID as foreign key but also the table ID. For comments, the back link from subform table to main table is not that important, but I want to use this principle also for other purpose where I maybe also create/manage subform table objects and link them with a lookup object to a main table record afterwards.
First I tried to use two lookups object in the subform: The first to look up the table and the second should use the value as a hash cookie for the table/form where to look for the record. If that had work out, my problem would have been solved, but apparently the lookup object cannot handle hash cookies in their properties. Then I considered different ways to acomplish that, especially by setting hash cookies and/or some SQL browse with both information. Maybe even a third table to choose table IDs. My latest idea - roughly explained - was to set an hash cookie with the main table ID and insert it automatically in a new subform record and for the subform table - the other way - create a main table lookup (to get the main table ID) and use this table ID as a hash cookie for SQL browse of different table to pick one record from the previously chosen table.
I haven't tried this yet, but I wonder if there is an easier - and more native - solution for my problem. I would be happy if someone who solved an similiar problem would share the solution
