Open form filtering on sub-subform criteria

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
Jeremy
corner40Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
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
with
        .Filter = "BookingGroupID = " & lngGroupID
        .FilterOn = True

Good Luck!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
corner40Author Commented:
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
Jeremy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.