469,890 Members | 1,419 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

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

108 100+
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:-



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

(Would map onto the times in combo boxes comboStart & comboEnd)


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)

Nov 24 '06 #1
4 3134
32,231 Expert Mod 16PB
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.


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.
Nov 24 '06 #2
108 100+
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.
Nov 24 '06 #3
108 100+

Nov 24 '06 #4
32,231 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT c.Coach_Id
  2. FROM coach c INNER JOIN bookings b 
  3. ON c.coach_ID = b.coach_ID
  4. WHERE me.combodate = b.date 
  5. AND b.starttime Between Me.starttime And Me.endtime
I've also put the time checks into a Between structure.
Nov 24 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by boss0021 | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.