473,320 Members | 1,974 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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:-

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)

Nov 24 '06 #1
4 3288
NeoPa
32,556 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.

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

Nov 24 '06 #4
NeoPa
32,556 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

4
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 "All" as a selection to a combo box and then (2)...
3
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
9
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 Access2003 but fails on Access2002/XP. ON XP, it...
7
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, "<Add New Student>" As FullName, "aaa" As...
8
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 a recordset. Now, the following is the code to...
0
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 hadn't ever seen it implemented, and had spent a...
7
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 to generate a report. This report can be...
3
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 other date fields for each record and the combo...
1
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 (Quarter/Year) from a combo box, the next field, a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.