Hi Experts:
I need some assistance with adding a sub-form where *displayed/matched* record are based on a dynamic search box on the main form.
Allow me to summarize the DB's table structure an existing main form.
Table background:
- DB includes a 3-tiered cascading table structure, where...
- ... 01_tbl_Questions = top/1st tier
- ... 02_tbl_Answers = middle/2nd tier
- ... 03_tbl_MultipleAnswers = bottom/3rd tier
Background on Data Entry (into table structure):
1. An exam question is entered into table "01_". Field [Question] is indexed.
2. Answers to various are entered into table "02_".
2a. Single answer response (radio button) are entered as is. Aka, "the answer is the answer".
2b. In some cases, a multiple-choice question may result in an answer where I need to check "all answers". Instead of entering all answer, my response = "Select All Answers".
2c. In other case, a multiple-choice question may result in a subset (e.g., 2 out of 4) answers. In this case, I enter 2 answers into the 3rd table AND where I add "(See field Multi-Answers)" in "02_tbl".
Background on Current Form:
- Open form "frm01_KeywordSearchWithoutDate"
- In the yellow search box, type XYZ keywords into the yellow-marked search box.
- Based on the entered string, any matching values (for field [QUESTION]) will continue to reduce the number of found questions.
- Now, based on the existing eleven (11) sample records, question ID 6 and 10 are scenarios of data entry 2c.
- NOTE: Answer for Tier 2 (Answer) are based on various dates. If the answers are the same for any dates , field [BEST CHOICE] is marked for all sub-records; however, if I end up changing my mind on the answer, I will uncheck the "BEST CHOICE" for incorrect responses. Most importantly though, the form/subform must display only [BEST CHOICE] = "TRUE" records.
Example:
- Enter "Match the following" into the yellow search box.
- This will result in the listbox displaying 3 records... based on 3 multi-answer (3rd tier)?
- ... and based on data entry scenario 2c, the form's value in [QUESTION] & [ANSWER] is repeated.
What I'd like to achieve:
- Modify the existing form and only include the 1st tier [QUESTION].
- Add a sub-form (2nd tier) for [ANSWER] and sub-sub-form (3rd tier) for [MULTI-ANSWER].
Now, just like presently, once I type in a value for the [QUESTION] in the yellow search box, I want to make sure that the sub-form also only displays the associated sub-records.
I'm open to merge the 3rd-tier answers into the 2nd tier answers to minimize the need for a sub-sub form.
My question: How do I create the sub-form that is dynamic enough so that it is updated as I type additional characters into the yellow search box on the main form.
Thank you,
EEH