Opening a Form with sub-form and sub-subform based on criteria

[corner40]

Hi everyone
I asked a similar question not too long ago but have a new spin on it. Take a look and offer any help you can.

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").

I have a search form where the user searches for the child. It comes up with a list of dates that that child is booked in for. When they double click on the date in the listbox I want it to take them to form with the appropriate information displayed.
This is my search sql statement

Code:

Me.ctlLstBxResult.RowSource = "SELECT BookingDetail.KID, Kid.KidFNAME, Kid.KidLNAME, BookingHead.BookingDate, " & _
"BookingGroup.BookingGroupID, BookingHead.BookingHeadID, BookingDetail.BookingDetailID, bookinggroup.pid " & _
"FROM (BookingGroup INNER JOIN BookingHead ON BookingGroup.BookingGroupID = BookingHead.BookingGroupID) " & _
"INNER JOIN (Kid INNER JOIN BookingDetail ON Kid.KID = BookingDetail.KID) " & _
"ON BookingHead.BookingHeadID = BookingDetail.BookingHeadID " & _
"WHERE kidlname LIKE '*" & Me.ctlTxtSearch.Text & "*' " & _
"ORDER BY Kid.KidLNAME, BookingHead.BookingDate;"



this is the code I have been trying to use:
Code:

Dim stDocName1 As String
Dim stLinkCriteria1 As String
Dim stDocName2 As String
Dim stLinkCriteria2 As String
Dim bdid As Integer
Dim bhid As Integer
Dim bgid As Integer
Dim form1 As Form
Dim form2 As Form
Dim form3 As Form


stDocName2 = "f_Adult"

stLinkCriteria2 = "[PID]=" & Me.ctlLstBxResult.Column(7)
DoCmd.OpenForm stDocName2, , , stLinkCriteria2

Forms![f_adult].Tag = Me.ctlLstBxResult.Column(0)
Forms![f_adult].Visible = False

stDocName1 = "f_bookinghead"

DoCmd.OpenForm stDocName1, acNormal, , , acFormAdd

Forms![f_bookinghead].PID = Forms![f_adult].PID
Forms![f_bookinghead].SignInID = Forms![f_login].SignInID
Forms![f_bookinghead].SetFocus
Forms![f_bookinghead].txtFocus.SetFocus

Set form1 = Forms![f_bookinghead].Form
Set form2 = Forms![f_bookinghead]![f_BookingHeadForm].Form
Set form3 = Forms![f_bookinghead]![f_BookingHeadForm]![f_BookingDetail].Form

bdid = Me.ctlLstBxResult.Column(6)
bhid = Me.ctlLstBxResult.Column(5)
bgid = Me.ctlLstBxResult.Column(4)
MsgBox "BookingDetail " & bdid
MsgBox "BookingHeadID " & bhid
MsgBox "BookingGroupID " & bgid
MsgBox "PID Control " & Me.ctlLstBxResult.Column(7)
MsgBox "PID Form " & Forms![f_bookinghead].PID

With form1
.Recordset.FindFirst "bookinggroupid = " & bgid
With form2
.Recordset.FindFirst "bookingheadid = " & bhid
With form3
.Recordset.FindFirst "bookingdetailid = " & bdid
End With
End With
End With



It ends up opening f_bookinghead with a new record.
any help would be much appreciated.
I apologize for posting this in two differeny forums!
thanks
Jeremy