Basic Booking Calendar

Sturmtruppe

Registered User.
Local time
Today, 14:29
Joined
Mar 26, 2009
Messages
18
In the image below i have a form for setting up peoples assesments in access 2003, that has the "InterviewDate" & "InterviewTime". Interview Date is just a default value of the previous entry. "InterviewTime" is a combo box that displays all the times from a lookup table.

What ive been trying to do is to find out if there is a way to have times greyed out (or not visible at all would be better i suppose) that are already booked on that day. so on 04/05/2009, 10:00 would be greyed out so i wouldnt select it since it is taken, otherwise a double booking would occur.

Users arent actually able to double book since ive set access to disallow duplicate times on the same day, but with this method, users have to try each day in a trial and error situation which takes up too much time.

I have had some help before which has given me a lot of guidance, which i is on the right track, but i dont understand how to implement points 3 and 4

this is very common. usually you would have:

1) a lookup table for available times throughout the day
2) a combo box for those times available on the form
3) a field i nthe appointments table that has the exact same data as in the lookup table
4) similar code like this for the combo (as the rowsource):
Code:
SELECT lookuptable.timefield 
   FROM lookuptable
      WHERE [TIMEFIELD] NOT IN (SELECT
         maintable.timefield FROM maintable
      WHERE [timefield] = [yourdateinquestion]);
 

Attachments

  • prvw_int.JPG
    prvw_int.JPG
    10.4 KB · Views: 400
Last edited:
Do you have a field in your table to store the time information? As indicated by Adam
Do you have a separate table with the times in it?

Please post the SQL version of your existing combobox recordsource.

Note to Adam:
That's an excellent piece of code you've got there, I think I'm going to steal it.
 
Yes, ive set up the combo box to use the times listed in a seperate table simply called "Assesment_Times_List", which records the data to the "Assesment Time" field in the table "Learner_Assesments"

By record source, I can find "Row Source" for the combo box, but no "record source".
 

Attachments

  • rowsource.JPG
    rowsource.JPG
    30.8 KB · Views: 274
Sounds like a cascading combobox scheme. See exemple.

JR
 

Attachments

just thinking about this

if you want to offer 1/2 hr time slots - then maybe its better to have a numeric lookup. where each index corresponds to a specific time.

so slot 1 is 8.00am, slot 2 is 8.30am etc. etc.

then you can give an easy look up to select the time.

--------
its just that entering correctly formatted times is never that easy - if you want discrete time intervals this might be easier.

Not sure what other developers do.

---------
edited - reading again, i can see this is what you are doing

yes - you can just include unbooked times - you need a query that finds the booked slots, then use that query in an unmatched query to find the unbooked slots - this is easy this way - i dont think you could get a combo box with greyed out slots, which was your other suggestion
 
Last edited:
Opp's...
By record source, I can find "Row Source" for the combo box, but no "record source".

I meant row Source...
 
sry not replied due to time off work.

Ok managed to get the standaloe form working using JANR's example,

Not In (SELECT tblBooking.IAGTime FROM tblBooking WHERE [IAGDate] = [Forms]![frmBooking]![IAGDate])

Now what do i do if [frmBooking] was a subform in [IAG_Call_List]? As for some reason, it will work in the frmBooking on its own, but when its a subform, the combo asks me to enter a parameter value.
 
[IAGDate] = [Forms]![frmBooking]![IAGDate]

If IAGDate is on the mainform can't you refrence it by changing [frmBooking] to [IAG_Call_List] ?


JR
 
iagdate is actually on the subform too. if it asks me to enter a parameter, if i enter something like 10:00 for example, i am still then able to choose from the available times.
 
Change to when refrence the subform control:

Code:
[IAGDate] = Forms![IAG_Call_List]![frmBooking].Form![IAGDate]

JR :)
 
Yes, its working perfectly. I just need the list of times to refresh when i move to the next record in the main form - Just so that the list of times refreshes to reflect the times unavailable.

Once i selet a new record in the main form id like to use similar to this code:

Me.InterviewTime.Requery
Me.Refresh

Is there any way possible? I tried using that exact code as an afterupdate event for interviewtime field itself, but the interviewtime field then goes blank (It reocrds the data, but doesnt show up in the subform)
 
Last edited:
Ive spent the week trying to add finishing touches to the form, but for some reason the "InterviewTime" field has stopped showing any times at all now (only after i enter a date) which is pretty much going to force me to start again from scratch. Ive checked all the coding relating to the times and nothing has been changed. Ive even redone it 3 or 4 times.

Note the image below, if the interviewdate field displays an actual date, the problem will occur, whereas if the date value remains as nul when i delete all the entries, the interviewtime field will display all the times.
 

Attachments

  • 975684876.JPG
    975684876.JPG
    12.2 KB · Views: 244
Here we go

SELECT IAG_Times.[Time ID], IAG_Times.Times FROM IAG_Times WHERE (((IAG_Times.[Time ID]) Not In (SELECT IAG_Interview_Date.InterviewTime FROM IAG_Interview_Date WHERE [InterviewDate] = Forms![IAG_Bookings_Not_Called_Yet]![IAG_Interview_Date]![InterviewDate]))) ORDER BY IAG_Times.Times;
 
Is the control still on a subform? if so you have forgotten .Form in your whereclause.

Code:
Forms![IAG_Bookings_Not_Called_Yet]![IAG_Interview_Date[COLOR=red]].Form![[/COLOR]InterviewDate])))

JR
 
it only seems to work if i remove the Me.Refresh for the onchange event for interviewtime. but i need this to remove the times already taken, otherwise the form has to be closed, then reopened for used times to dissapear. Is there anything i could use instead?
 
Last edited:

Users who are viewing this thread

Back
Top Bottom