Open form filtering on sub-subform criteria

Posted on 2004-11-24
Last Modified: 2006-11-17
Hi everyone

I have a form(f_bookinghead primarykey "BookingGroupID") that has a subform(f_bookingheadform primarykey "BookingHeadID") and the subform has a subform(f_bookingdetail primarykey "BookingDetailID").

The way my form is set up I can't use record selectors effectively.  This makes things difficult for when someone wants to edit data after they have moved onto a new record. For this situation the BookingGroupID won't be changing at all.  What I want to be able to do is navigate to a record based on the BookingHeadID and BookingDetailID.
I have set up a command button called "EditBooking".  It pops up a form "f_EditBooking" that displays all of the records for the particular BookingGroupID in a listbox "lstEditBooking".  What I would like to do is on the double click event of lstEditBooking, display the infomation based on the BookingDetailID (lstEditbooking.column(6)) and BookingHeadID (lstEditBooking.column(13)).
What I have tried is to set a variable "bdid" to lsteditbooking.column(6) and another variable "bhid" to lsteditbooking.column(13), then filtering each form to those specific variables. To be honest, this may be the right approach but I was just going about it wrong.  I don't know...or I tried closing the form and opening it based on the criteria.  I wasn't successful in either attempt, but I also wasn't very confident in what I was doing.
Any suggestions would be terrifically appreciated.  I'll try whatever you guys/gals think of.
thank you
Question by:corner40
    LVL 58

    Accepted Solution

    You obtain somehow (in f_EditBooking) three keys: lngGroupID, lngHeadID and lngDetailID. You want your form and subforms to display the selected Detail within the selected Head within the selected Group. Here we go:

        With Forms!f_bookinghead
            .Recordset.FindFirst "BookingGroupID = " & lngGroupID
            With .sub_bookingheadform.Form
                .Recordset.FindFirst "BookingHeadID = " & lngHeadID
                With .sub_bookingdetail.Form
                    .Recordset.Findfirst "BookingDetailID = " lngDetailID
                End With
            End With
        End With

    Of course, you might want to add some error-catching :) And yes, I used "sub_<name>" for the name of the control that displays the form "f_<name>". They can be different and if they are, the name of the control must be used, not the name of the form...

    If you prefer the filtering approach, replace for instance:
            .Recordset.FindFirst "BookingGroupID = " & lngGroupID
            .Filter = "BookingGroupID = " & lngGroupID
            .FilterOn = True

    Good Luck!

    Author Comment

    Hey Harfang
    thanks for the response.  It worked great.  I had tried to filter before the way you had suggested, but was unsuccessful.  
    thanks a lot for your help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    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…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    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…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    760 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

    10 Experts available now in Live!

    Get 1:1 Help Now