Multi Level Form

[bkelly]

I need a what might be called a three leveled form but cannot get it to come out right. The goal is a form that operates as follows:

First, the user opens a club selection combo box to pick a single golf club, each of which can have multiple courses.
Second, the user opens a course selection combo box to pick a single course in order to edit hole information for that course.
Third the user enters or edits hole data. There will generally be 9 or 18 records of hole data for each course.



I created a hole edit form based on a club select query having the club name and index. The form only shows the club name. A combo box limited to only field names shows the club names and works fine.

Then a course form is created based on a query that shows courses and is dragged and dropped into the first form. The problem is that the combo box for the courses shows all the courses and is not limited to the courses for the club that was selected.

How can the course select combo box be limited to those available for the selected club?

This might direct the reader to something I am doing wrong. I create the second form for courses (a sub form) and add the combo box to select a course name. When I show the form and try to pick a course, there is a VBA error. Selecting debug shows:

Private Sub Combo12_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Course_Name_And_Tee] = '" & Me![Combo12] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The debugger highlight this line:

rs.FindFirst "[Course_Name_And_Tee] = '" & Me![Combo12] & "'"

This code was created by Access when I used the Wizard to create the combo box. I do not know enough yet about SQL to detect an error.

But that is just additional info.
How do I make a form with two levels of selection, each from a separate table, then have fields to edit from a third table?

Thanks for your time,
Bryan

[dcrake]

Essentially what you need is 2 combo boxes on the main form

The first one is for the Gold Clubs, this will be taken from the main golf clubs table. Once you select a club the after update changes the sql of the row source of the second combo box (normally referred to as cascading combo boxes) so that is lists only those courses that apply to the golf club.

Then in the second cobmbo box you then select the course. At this point the subform has its master and child relationships set up to be

Master:Combo box Gold Clubs; Combo box Courses
Child : Matching fields in the subforms underlying query/table

Every time you change the selection in the clubs combo box you will need to change the rowsource in the courses combo box as well.

David

[bkelly]

I am getting myself confused about this. Some google searches turned up an example from Blue Claw Database Design and an example called Dependent Combo Box. In there the form has two combo boxes but no sub forms, and it does not seem to use a query going directly to the table.

But it is rather simplistic.

Maybe I should simplify to a couple of simple questions. I have too many options and need to narrow down my problem space.

First: Should I build my form so that it accesses the three tables directly, or from one, two, or maybe three queries? Maybe once I get that nailed down, I can work this some more and see what happens.

Second: I tried to fill in a text box by using an After Update event for the combo box:

Private Sub cboSelectClub_AfterUpdate()
Me.txtClubType = [tbl_Golf_Clubs].[Club_Type]
End Sub

The error is "...can't find the field '|' referred to in your expression."
I don't see any '|' in my expression. Maybe it will help to include the "Row Source" field for the combo box that drives this statement:

SELECT tbl_Golf_Clubs.Golf_Club_Index, tbl_Golf_Clubs.Golf_Club_Name FROM tbl_Golf_Clubs ORDER BY [Golf_Club_Name];

I hope this isn't too much to ask of a forum and volunteer advice.
Thank you,
Bryan