472,342 Members | 2,464 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,342 software developers and data experts.

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 3222
32,511 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,511 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

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

Similar topics

by: meganrobertson22 | last post by:
Hi Everyone- I have a question about how to add and then use the "All" selection in a combo box. I am trying to figure out how to: (1) add...
by: boss0021 | last post by:
Does anyone know how to: Populate a ComboBox with all the days of the year for 2005 with out doing it manually
by: Bob Alston | last post by:
I have a drop down combo box that gives the user to enter an item not in the list by adding it to the list. The list is a table. It works fine on...
by: Arnold | last post by:
Greetings Gurus, In a mainform's header, I have a combobox named comboStudents. The rowsource for this combobox is: SELECT -999 As StudentID,...
by: Jack | last post by:
Hi, I have a asp page where multiple rows for a client is generated using asp. Some of these are combo boxes and some are text and are coming from...
by: Jeremy Wallace | last post by:
Folks, Here's a write-up I did for our developer wiki. I don't know if the whole rest of the world has already figured out how to do this, but I...
by: billelev | last post by:
I have a query that selects data based on a particular date. This date is selected from a combo box on a form. The data from the query is then used...
by: wideasleep | last post by:
I have a subform that's a continuous form. In it I have a combo box that fills a date field. It works great except for one thing. It fills in all the...
by: jlcash | last post by:
I have the following situation in Access 2003 I need some help on: I have a form that I would like to set up so that when a user selects a time...
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.