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

[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.
thanks
Jeremy

 

[xhonzi]

This isn't an answer, just a suggestion...
Did you know that there was a VBA forum here? Just looking at your post makes me think it would be better suited there...

Microsoft: Access Modules (VBA Coding)

Xhonzi

 

[GingerR]

on f_bookinghead, is the Data Entry property set to YES?

 

[GingerR]

sorry for my last post, i wasn't thinking.

I'm not quite sure what you are doing.
So you are opening the form to a new record and populating the fields, then trying to move to the correct records in each of the forms/subforms? Why not just open the main form to the correct BookingId and have the subforms linked to the main with Child/Master Links and the queries of the subforms reference the search form?

 

[corner40]

Hi Ginger
The reason i can't use the subforms queries to get the correct data is that the form I am opening is mostly openned as a blank form and data is entered. Furthermore it is opened from a seperate form. What I am trying to achieve is the ability to edit a previously entered booking. To do this I need to be able to open the booking form with the correct data to be manipulated.
Thanks for the suggestion though, I'd appreciate more if you have them.
Ignore the part of the code that opens the form [f_adult} that is just a step that is needed but of no importance.
thanks again
jeremy

 

[GingerR]

Hi Jeremy. Sorry for all the questions but I'm trying to get clear on it:

You have a form/subform/subform set up. Sometimes you open this form to add a new record. Sometimes you open it from a different place to edit current records. Is this correct? And what we are talking about here is when you want to open it displaying an existing record so that you can then edit it?

Unless I'm reading this wrong, I guess I'm confused as to why you are opening the form in ADD (DoCmd.OpenForm stDocName1, acNormal, , , acFormAdd) instead of opening it where BookinggroupID = Me.ctlLstBxResult.Column(4). Since you are opening it in ADD mode, it's starting a new record.


Maybe the best thing to do would be to create a duplicate set of forms, one for adding and one for editing, where the editing subforms reference your combo box. Something else I noticed is that it looks like you are only looking for the one record for one date for one Kid...so maybe you don't even need to open the form/subform/subform package if all you need to do is display the one record?

 

[TheAceMan1]

How are ya corner40 . . . . .

Of the three forms, which [blue]PrimaryKeys[/blue] are included in the listbox of the search form?

What are the [blue]relationships of the underlying tables and the connecting keys?[/blue]

See Ya! . . . . . .

 

[corner40]

Hi Ginger and Aceman
Having the form open in add mode was one of the last things I had tried. I had originally had the stlinkcriteria set to column4 of the listbox. It still didn't work.
As for having a duplicate form where i have one open one way and the other form open the other way...I would do that except that this is a form that is going to be continually changing for the next few months while bugs/user preferences are worked out and making sure to update both could become a hassle.
Additionally, yes I am looking to open only one record. The record is the bookingdetailID.

The main form is (f_bookinghead, PK bookingGroupID). The subform is (f_bookingheadform, PK BookingHeadID, FK BookingGroupID, relevant Field BookingDate). The sub-subform is (f_bookingDetailID, PK BookingDetailID, FK BookingHeadID, relevant Field KidID)

what I am hoping to achieve is open the forms displaying the booking information for Kidid = ctlLstBxResult.column(0), and BookingDate = ctlLstBxResult.column(3)

all of the ID's are in the list box
thanks for all the help you guys
Jeremy

 

[corner40]

Hi guys
I got it to work using this:

stDocName1 = "f_bookinghead"
DoCmd.OpenForm stDocName1, , , stLinkCriteria
Forms![f_bookinghead].Filter = "bookinggroupid = " & Forms![editbookingsearch].ctlLstBxResult.Column(4)
Forms![f_bookinghead].FilterOn = True

thanks for all the help. Greatly appreciated
Jeremy

 

[TheAceMan1]

corner40 . . . . .

You asked for [blue]KidID[/blue] & [blue]BookingDate[/blue]. This will be for the current BookingGroup in view.

Are you sure you don't want to add the [blue]BookingGroupID[/blue] as well?

See Ya! . . . . . .

 

[TheAceMan1]

corner40 . . . . .

The following 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 scroll/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.

In the [blue]AfterUpdate[/blue] event of [blue]ctlLstBxResult[/blue], cpoy/paste the following code:

[blue]   Dim rst As DAO.Recordset, LB As ListBox
   Dim frm As Form, sfrm1 As Form, sfrm2 As Form
   
   Set frm = Forms!F_BookingGroup
   Set sfrm1 = frm!F_BookingHeadForm.Form
   Set sfrm2 = sfrm1!F_BookingDetail.Form
   Set LB = Me!ctlLstBxResult
   
   Set rst = frm.RecordsetClone
   rst.FindFirst "[BookingGroupID] = " & Val(LB.Column(4))
   frm.Bookmark = rst.Bookmark
   
   Set rst = sfrm1.RecordsetClone
   rst.FindFirst "[BookingDate] = #" & LB.Column(3) & "#"
   sfrm1.Bookmark = rst.Bookmark
   
   Set rst = sfrm2.RecordsetClone
   rst.FindFirst "[KidID] = " & Val(LB.Column(0))
   sfrm2.Bookmark = rst.Bookmark
   
   Set rst = Nothing
   Set sfrm2 = Nothing
   Set sfrm1 = Nothing
   Set frm = Nothing
   Set LB = Nothing[/blue]

Thats it . . . . give it a whirl & let me know . . .

See Ya! . . . . . .