Open form filtering on sub-subform criteria

[corner40]

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

 

[lamarw]

What happens when you apply the filter?
When you pull up the properties page for the control what does the filter property have in it?

 

[TheAceMan1]

How are ya corner40 . . . . .

[blue]The way my form is set up [purple]I can't use record selectors effectively.[/purple] This makes things difficult for when someone wants to edit data after they have moved onto a new record.[/blue]

I don't see how Record Selectors [blue]add diffculty for editing.[/blue] Could you be a little more specific . . .

Anyway . . . I hate using the [blue]Filter[/blue] property as it [purple]can cause a great many headaches[/purple]. So I always use code (at least I can have that Rum & Coke and relax).

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

Be aware: the code assumes the [blue]ListBox IDs[/blue] used are autonumber or numeric. If Text, the [purple]FindFirst code lines[/purple] will have to be modified.

Also, you may find the [blue]Click[/blue] event [blue]more palatable[/blue] for operations than [blue]Double Click[/blue].

So, in the event of your choice, copy/paste the following code:

[blue]   Dim sfrm1 As Form, sfrm2 As Form, rst As DAO.Recordset
   
   Set sfrm1 = Forms!F_BookingHead!F_BookingHeadForm.Form
   Set sfrm2 = sfrm1!F_BookingDetail.Form
   
   [green]'Sync F_BookingHeadForm[/green]
   Set rst = sfrm1.RecordsetClone
   rst.FindFirst "[BookingHeadID] = " & lstEditBooking.Column(13)
   sfrm1.Bookmark = rst.Bookmark
   
   [green]'Sync F_BookingDetail[/green]
   Set rst = sfrm2.RecordsetClone
   rst.FindFirst "[BookingDetailID] = " & lstEditBooking.Column(6)
   sfrm2.Bookmark = rst.Bookmark
   
   DoCmd.Close acForm, "F_EditBooking"
   
   Set rst = Nothing
   Set sfrm2 = Nothing
   Set sfrm1 = Nothing[/blue]

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .