Open form filtering on sub-subform criteria

Posted on 2004-11-24
Medium Priority
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

harfang earned 300 total points
ID: 12671378
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

ID: 12672646
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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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