Filter list box to subdata

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    I have a form with many tabs the form is a member database. on one of the tabs the members children are stored. They have there own table and are set as a subdata sheet. The tab has their form on it. A form in a form. The kids are filtered to the parents. Have J Smith selected and only his kids appeared. I was asked to put a listbox to select the kids easier. But I cant get it to see the same filter. So instead of seeing J Smiths 3 kids I see all 250 kids in the table. The form will only show the 3 kids but the listbox has them all. Any ideas, thoughts, macros or suggestions? Thanks!

    [SIZE=2]I should change my name to STUMBED![/SIZE]

    Re: Filter list box to subdata


    You need to define the record source of thelist box to include a where clause based on the selected parent.


    Select the list box and open the properties dialog. Click on the Record Source and click the ... on the right. This will open up the query designer. Then add the field IDParent or similar firld that uniquely identifies a parent. Right click in the criteria boxes under the field and select build. Now navigate to the main form and select the unique ID for the parent.


    Click OK and save teh form. The list box should now contain only the children for the selected parent.


    HTH,


    A.

    Re: Filter list box to subdata


    I only get the designer for the row source. So something appear to be backwards.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

    Re: Filter list box to subdata


    Well I have been busy,
    Its still not guite how I want it but its closer.


    Edited the properties as:
    Control Soure has nothing
    Row source has the query set with the ID and then the criteria is set with the forms ID, (only way I could get it to work)
    Have a afterupdate code to refresh the data if you click on the list box.


    So as of right now the listbox will only show the parents kids, but if you select one in box nothing will happen. If you change the parent on the form the kid will change but the box will not unless selected.


    I tried what you said A9192Shark, but I am just not understanding.
    Would still like help getting it correct, please.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

    Re: Filter list box to subdata


    A9192Shark I just reread your post and I believe I get it now. It is still running as I said in the last post but thanks for showing me the criteria method, I still probaply set up all wrong, but hay!

    [SIZE=2]I should change my name to STUMBED![/SIZE]

    Re: Filter list box to subdata


    Chris,


    Now that you have the criteria set up we need to add an OnCurrent event to the form. The event will requery the list box each time a new record is entered.


    Sounds like you know how to set up an event for a form, so do this for the OnCurrent event for the main form. Add the code

    Code
    ListBox.Requery

    to the event procedure.


    A.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login