Complicated Combo box requiring VBA code to generate source with date complications

[robtyketto]

I have generated combo boxes based on contents of other combos and am newbie who slowly learning vba code.

Heres an example of vba code use to update one combo box from another

Me.comboArea.RowSource = "Select Area.Area_ID, Area.Area, Facility.Facility " & _
"FROM Facility INNER JOIN Area ON Facility.Facility_ID = Area.Facility_ID " & _
"WHERE Area.Facility_ID = " & Me.combofacility.Value & ";"

For the next combo source it require joining many tables which I could build up the SQL slowly.

The complication is using a date (dd/mm/yy) in a combo box and comparing against a yes/no field within a table, see below for details of tables and my problem.


Tables & fields listed below:-

Coach
=====
Coach_ID
Coach_Name

Monday
Tuesday
Wedneday
Thursday
Friday

(Need to workout out from ComboDate which day of week it is and select coach based on checkbox values of days fields
above, if data could be stored in a simpler more efficent way then I could change the database).

Coach_Availability
==================
Coach_Id
Time
(Would map onto the times in combo boxes comboStart & comboEnd)


CoachRates
==========
Coach_Id
Sport_Id


Coach availability is based on

a) Day coach works (Mon - Sunday checkboxes on Coach table against date selected in comboDate)
b) Time of day coach works (Coach_availability table - time field) (Coach_availability values 08:00,08:30,09:00, 09:30, 10:00 etc.., against comboStarttime & comboEndtime)
c) Can coach teach Sport Played ( Coach rates table Sport_id field lookup against sport selected in comboSport)


I arent worried about double booking yet just the criteria above.

Can anyone help?
Hope this makes sense (including relationship diagram)

[NeoPa]

If your complication is working with dates then there are a number of Date related functions that can convert a date to various formats for your use.
Weekday() is the first one that springs to mind, but there are various others that may help in your situation.

Weekday Function


Returns a Variant (Integer) containing a whole number representing the day of the week.

Syntax

Weekday(date, [firstdayofweek])

The Weekday function syntax has these named arguments:

Part Description
date Required. Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, vbSunday is assumed.

[robtyketto]

Trying to write some code to sit behind a combo box for Coaches on a form to check if a coach is available based on these factors:-

* Does he work that day & time on the rota (Coach_Availablity: day (0-6 represent day of the week) and time field)

Coach ID Day Time
1 1 08:00
1 1 08:30
1 1 09:00
1 2 08:00
1 2 08:30
1 2 09:00

The date, start & end time on entered in combo boxes on the screen already.

* Does he play the sport that the person has picked to play

The sport value_ID has already been selected in a combo box. coachrates table stores the relationship between
coach and sport he can play

Coach_ID Sport_ID
1 2
1 3
1 4

I have written sort of peusdo sQL below

# Checks if sport on form has any available coaches, then check if date & time booked are available within his
weekly rota)

Select c.Coach_Id, c.coach_first_name, c.coach_second_name
from Coach c, coachrates cr
where c.coach_ID = cr.coach_ID
and cr.sport_ID = me.comboSport.value
and weekday(me.comboDate.value) in
(
Select day
from coach_Availability
and me.comboStarttime.value = time
)

However this only check if hes available within the rota and not if a booking exists for the coach in the bookings table.

Select Coach_Id
from coach c, bookings b
where c.coach_ID = b.coach_ID
and me.combodate.valuee = booking.date
and me.starttime >= b.starttime and b.starttime <= me.endtime

Its all quite complex, can anyone help out or put me in the right direction.
Any help would be appreciated and thanks to all those who have helped me before.

[robtyketto]

[NeoPa]

I don't really get involved in very complicated or large questions as I find time is difficult as it is (I expect others might). I would suggest (just a suggestion mind - please ignore if it doesn't seem right to you) that bite-sized problems and questions are more likely to find answers. I do appreciate, of course, that some problems are not easily broken down in such a way. I'm just trying to show the situation from an 'expert's perspective.

However, I can see from your SQL code that you know a thing or two, so I thought I'd just bring a concept to your attention that might prove helpful in future.

Select Coach_Id
from coach c, bookings b
where c.coach_ID = b.coach_ID
and me.combodate.valuee = booking.date
and me.starttime >= b.starttime and b.starttime <= me.endtime

might benefit from JOINing the two tables.

SELECT c.Coach_Id
FROM coach c INNER JOIN bookings b 
ON c.coach_ID = b.coach_ID
WHERE me.combodate = b.date 
AND b.starttime Between Me.starttime And Me.endtime

I've also put the time checks into a Between structure.