Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2019
    Posts
    1,038

    Create dynamic subform based on search field (main form)

    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
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Maybe bind main form to Questions and subform to Answers and set Master/Child Links properties?

    Apply filter to main form and associated Answer records display.

    As for synchronizing MultipleAnswers records, review https://www.fmsinc.com/MicrosoftAcce...edSubforms.asp
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Feb 2019
    Posts
    1,038
    Thank you, June7... 'very helpful information.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 04-19-2019, 08:16 AM
  2. Replies: 13
    Last Post: 03-02-2018, 12:04 PM
  3. Replies: 3
    Last Post: 08-22-2012, 03:28 AM
  4. Search Main Form Based on Contents in Subform
    By MintChipMadness in forum Forms
    Replies: 1
    Last Post: 08-09-2012, 02:45 PM
  5. Replies: 4
    Last Post: 07-11-2012, 10:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums