Control subforms

[Real Wally]

What looked like to a pretty simple thing to design turns out to be not so simple, not to me anyway.
I've been trying to make a database in which I can easily view what patents a company holds in a certain area.
The start was easy enough, two tables, tblCompanies and tblArea with a coupling table holding CompanyID and AreaID. What (may) make it even easier is that I'd like to monitor the same 7 activities in all chosen companies. The third table holds the patent information (PatentNum, Abstract, etc). The difficulty is that I don't see how I can link the 3 tables on one form so that the right patent show per Company and Area. What I thought of doing was to have a form with a combobox that allows me to chose company and seven tab pages, one for each category. The tab pages of course should contain the patent info. This means that these pages should be controlled by the a hidden value on the main form. How do you achieve that? The next problem is that on the tab page there'll be a list with patents. I'd like to be able to open a form with details of a selected patent on the list with a button but how can you select a value in a list?

I cannot see the forest through the trees anymore, it's too complicated for me but I suspect that in fact it's not that difficult. How do I approach this?

Thanks for your advice,

Walter

 

[llkhoutx]

Add a CompanyID and AreaID to your Patents table, such that

tblCompanies
CompanyID
sCompany
etc.

tblAreas
AreaID
sArea
etc.

tblPatents
PatentID
CompanyID
AreaID
lngPatentNumber
sAbstract
etc.

 

[Real Wally]

Thanks.

Of course! I don't know why I couldn't think of that myself. Your guiding hand is greatly appreciated.

Walter