List Box updating using another listbox's value as Header?

[zero2XIII]

Hay all,

I need to construct a very complex student/coach/time/day Database.

Every student can attend a lesson twice a week, on a particular day and time with a particular coach.

Every coach has his/her own time table (Days available, times available on those days).

So I want this effect on a form:
Day: Monday
Coach: Jack
Time: 10:00

So Coach is dependent on Day, Time is dependent on BOTH Coach and Day.

I got the first one working but I am stuck on the second.

I created a Table for every day of the week (Mon through Friday) and have each coach's name as the "header" of a column, in that column I have the times they are available:

Jack - Roy
08:00 - 09:00
08:20 - 09:20
08:40 - 09:40

In my form, in the event procedure of the second list box I have the following:

Select Case [Swimming_Day_1]
       
        Case "Tuesday"
           [Time_1].RowSource = "SELECT [Coach_1].Value " & _
                           "FROM [Tuesday Times] "
                      
    End Select

But now when I select for example "Jack" in the "Coach_1" List box, the "Time_1" list box lists 3 entries of "Jack", not "08:00, 08:20, 08:40" respectively.

How can I accomplish the above without having to create a separate Day/Times Table For each and every coach (There are 8 coaches and 5 days, and I want things as easy as possible to update coaches should there come extra or some leave maybe).

I hope what I am trying to accomplish is clear. I have been trying to get this to work for over a week now. No google or youtube yielded any usable results. :banghead:

Thanks in advance.

 

[CJ_London]

Sounds like you need to look at the format of your listbox. From what you describe I would expect you have something like the following:

Rowsource="SELECT Coach, Times From TblTimes WHERE Coach='Jack' AND Days='Monday' ORDER By Times"
BoundColumn=1
Columns=1
Columnwidths=""

If this is the case, simply change the rowsource to

Rowsource="SELECT Times From TblTimes WHERE Coach='Jack' AND Days='Monday' ORDER By Times"

Note if you are using field names like Day and Time, these are reserved words and should not be used - they can have unexepected results.

Another reason you may have this problem may be because you are using lookups in your table design - if you are, remove them, they are a bad idea

 

[zero2XIII]

Rowsource="SELECT Coach, Times From TblTimes WHERE Coach='Jack' AND Days='Monday' ORDER By Times"
BoundColumn=1
Columns=1
Columnwidths=""

Hay thanks for the reply, unfortunately that is not what I need.

If you can compare the listbox "Coach" to a variable then I want to be able to select times based ON that variable:

IF coach = "Jack" THEN rowsource = "08:00 - 09:00"
IF coach = "Roy" THEN rowsource = "09:00 - 10:00"

So as in your example:

Rowsource="SELECT [B]Coach[/B] From TblTimes WHERE Coach='Jack' AND Days='Monday' ORDER By Times"

Coach is NOT the column heading/name, but the actual coach name (as selected in the coach list. Thus I want something like:

Rowsource="SELECT [Coach_listbox_value] From TblTimes WHERE Coach='Jack' AND Days='Monday' ORDER By Times"

So that "Coach" in your example changes to the name of the coach as in the table.

 

[CJ_London]

I'm struggling to understand what you require. It might be helpful if you can post your table structure - perhaps in a small db with test data.

 

[zero2XIII]

Hay,

Thanks. I will try and put together a "small" version and upload it in a few minutes.

 

[zero2XIII]

Hay,

Here is a clean version of the current database.

It is no where near complete, but my issue is in the "student information" form.

Under the "office use" tab:
Swimming day = Fixed source, monday through friday.
Coach 1/2 = Dependant on day in Swimming day 1/2, from "coaches" table check boxes. (This is also an issue I still have to resolve as it is hacked from "coach days" but that needs to change)
Time 1/2 = Dependant on coach and Swimming day, gathered from *day* Times (eg Tuesday Times) table.

For example if:
Swimming day = Tuesday
Coaches = Christine, Devin, Ezanda. - Select Christine
Times = 9:00, 9:20, 9:40, 10:00. (From Tuesday Times).

All of this is working towards having a localized schedule for the swimmers and their coaches. A ground work for moving all the separate parts of the current system to a localized database with all the information in an easy to access and maintain database.

I hope this make things a little more clear. Currently a lot is being made to work with some real ugly hacks. I will need to start at one point and clean everything up, but first I need it functional and then optimize it.

Cheers and thanks.