Populate subform based on combobox values in the main form

Posted on 2012-09-04
Last Modified: 2012-09-13
I am not able to see the list in subform when selecting an option from combo box from Main Form

Please help

Attached is the file
Question by:mercybthomas74
    LVL 84
    ChanceryItems.Report_Category and SMSReportList.Category are not related, so you can't use that as the Master and Child links for those forms. The datatype of ChanceryItems.REport_Category is Numeric, and the other is Text, so there's little chance of them being properly related at all.

    What is your intent with the form? That is, what is the user expected to do with this?
    LVL 39

    Expert Comment

    Your table with report types have 2 columns: ID and Name, but in your table is stored name, so you should select name in combobox. Will be enough to change column in combobox parameters:
    comboboxBut will be better to change table SMSReportsList and store there ID.

    Author Comment

    The purpose is to show Reports based on the category selected from the combo box... in a sub form..
    LVL 84

    Accepted Solution

    Then you need to do some rework, as als315 suggets

    Your "Rpt_Category" table is fine. It lists the Report Categories, with their associated ID values

    Your SMSReportsList, however, should store that ID value (i.e. the Numeric value from Rpt_Category.RptID). Instead, you're storing the value of Rpt_Category.Rpt_Category. I'm not sure how you create the SMSReportsList table, so cannot suggest how to fix it moving forward, but for now do this:

    1) Change the Datatype of ChanceryItems.Report_Category to Number

    2) Add a new column (Rpt_ID, perhaps) to the SMSReportsList table and fill it with the correct value from Rpt_Category.RptID. For example, if SMSReportsTable.Category is currently "CTE Reports", then enter the value of 3 in the new RptID field.

    3) Change the Recordsource of your SMSReportsList Subform to this:

    SELECT SMSReportsList.Category, SMSReportsList.[Report Name], SMSReportsList.RptID FROM SMSReportsList;

    4) Change the Master/Child links of the subform on the ChanceryValidationItems form to

    And remember this: Anytime you need to associate one table with another you should store the Primary Key value of the Parent table in the Child.


    Also, in the future please be sure to Compact your database before uploading it. I did when I downloaded your old database, and it dropped from 2.9 mb to ~650kb. While many of the Experts enjoy a very fast connection, there are a LOT who depend on dialup or cellular networks, and downloading large items can be very slow (and expensive).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now