Linked combo boxes

[dstockman]

Simple description
Inside a table control I have two combo boxes. The first combo box holds categories of screening tests such as Colon Cancer Screening (CRC). The second combo box holds specific tests under CRC, such as guaiac cards and colonoscopy. When a user chooses the first combo box content (category of screening test), I want the second combo box to only offer specific tests for just the choice in the first combo box. I hope a simple SQL statement in the second combo box would limit the query results displayed based on the choices in the first combo box, but I have not figured out how to do this. I suspect I will have to write a macro to link the 2 combo boxes. Any guidance would be appreciated.

More details
Main table – used as basis for table control in form
AutoNum – primary key
MRN – linked to specific patient
HCMcategory – where results of first combo box get stored
HCMspecific – where results of second combo box get stored
Date ordered
…..

Second table – HCMcategory
AutoNum
HCMtxt – displayed text for first combo box

Third table – HCMspecific
AutoNum
HCMtxtNum
HCMspecificTxt – displayed text for second combo box

The second and third table have a relationship via the (1) AutoNum to (Many) HCMtxtNum fields. When I create a form (HCMcategory as table) with a subform (HCMspecific as table) just to manage the contents of the second and third table, it works as expected.



I saw a recent post on a related issue, but I am not sure my problem is fully answered by that. That involved using a dummy table to hold choices for filtering. I did not fully understand it unfortunately and will play more with it if it applies directly to my problem.


A related but separate problem
On a related note, I do not understand how to limit the choices offered in a combo box to the choices pertaining to a specific record.

MRN = medical record number, unique for each patient
Combo box that should display only the diagnoses pertaining to the currently active MRN (patient), not all the diagnoses in the table holding all diagnoses.

It seems I need to filter the combo box by the currently active MRN. My feeble attempts so far have failed. Can anyone point me in the correct direction?

In the screen shot below, the pt identifier info is the form (from a demographics table and the MRN is the primary key) and the med data is a subform from another table (linked by the MRN). The contents for the “Link Diagnosis to Med” comes from a Diagnosis table that is linked to the Demographics table via the MRN.


It appears I do not understand how to post an image. Sorry.

Any help with either problem above would be greatly appreciated.

Doug

[kabing]

For the first issue, it will require a macro. See this thread in the forums where I asked a similar question. I can't help beyond pointing you there, but no doubt others can help you more.

It would be better to ask the second issue as a separate question/thread, as the issues involved are somewhat different.

kabing