Urgent!! Calendar Control Help Needed!!!

Hello all! I am making an appointment booking system for a hair dressing salon and am using the Calendar Control in VB... At the moment I have the calendar and beside it are a list of all the slots (as text boxes). My first question is, when inputting an appointment on the appointment page, how could i set it to a specific time slot (eg 9:00-9:30 - the text boxes are listed as half hour slots) and my second question is, how could I get the program to display all the different appointments in their designated time slots on a specific day by clicking on a day on the calendar??? Any help would be much appreciated!!!
Who is Participating?
it would be easiest to use an array of them.  Remove all but one, named something generic, then copy and paste it back on the form.  you will be asked if you want to create an array. choose yes.  Paste until you have a total of 48 boxes (24 hours * 2 half hours per hour) the indexes will be 0 - 47.  make sure they are in order so calculations will work properly.  As far as making the connection, for this more advanced stuff you are trying to do here, you should just make a programmatic connection, not try to use a control.  If you need a control, you should be able to use the programmatic connection as the datasource for the control.  To create an ADODB connection you:

Dim oCon as object
set oCon = createobject("ADODB.Connection")
oCon.Open connectstring

the connectstring will be a semicolon delimited list of some of the following items

For an odbc database DSN,UID and PWD are required ex:
oCon.Open "DSN=MyDataSource;UID=sa;PWD=password"
For an access database thats not in odbc:
oCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\data\mydayabase.mdb;UID=;PWD="
For a sql database thats not in odbc:
oCon.Open "DRIVER={SQL Server};DATABASE=MyDatabase;SERVER=;UID=sa;PWD=pass"

There are more advanced ways of putting together a connectstring using direct algorytims but this is probably the easiest to understand.  And you can always change the connect string later..  Once you have all of this done here is some code i would start with..

Private Sub Calendar1_Click()
    ' new date is clicked
    Dim lPoint As Long
    For lPoint = textbox1.lbound To textbox1.ubound
        textbox1(lPoint).Text = ""
    Next lPoint
    Dim ors As Object
    Set ors = dbconnection.execute("SELECT * FROM TABLE WHERE APPOINTMENTDATE='" & Calendar1.Value & "'")
    While ors.EOF
        With ors.fields
            SetTextBoxes .Item("AppointStart").Value, .Item("AppointEnd").Value, .Item("AppointName").Value
        End With
    Set ors = Nothing
End Sub

Private Sub SetTextBoxes(StartTime As Date, EndTime As Date, Name As String)
    'assuming textbox array and assuming half hour intervals for boxes
    '1 hour = 2 boxes
    'starts at 0 and goes to 47
    Dim iSHour As Integer, iSMin As Integer
    Dim iEHour As Integer, iEMin As Integer
    iSHour = DatePart("h", StartTime) * 2 ' 2 per hour to match indexes
    iSMin = DatePart("n", StartTime)
    iEHour = DatePart("h", EndTime) * 2 ' 2 per hour to match indexes
    iEMin = DatePart("n", EndTime)
    ' modify hour setting based on minutes rules
    If iEMin = 0 Then ' if ends at even hour
        iEHour = iEHour - 1 ' dont include this period in the range
    ElseIf iEMin > 30 Then
        iEHour = iEHour + 1 ' half hours aren't included but more is
    End If
    If iSMin >= 30 Then iSHour = iSHour + 1 ' move start time
    Dim iTemp As Integer ' even times are included in this one unlike end
    For iTemp = iSHour To iEHour ' move through appropriate boxes
        textbox1(iTemp).Text = Name
    Next iTemp
End Sub

Hopefully pretty simple based on whats going on..  I will be more than happy to answer any questions on it though
So you have a calander control with all of the time slots in text boxes next to it?  When you click on a day does it populate those text boxes?  The "appointment page" is a seperate form and contains start and end times?
MrdogkickAuthor Commented:
yes, I was thinking that I could have a drop down menu when selecting the time slot on the create appointmenty form which would display all the available slots that day. I thought this would make it easier when attempting to populate the list??? Any ideas??
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

sounds good to me.  But now I am left wondering what exactly your question is.
MrdogkickAuthor Commented:
I need to know how i connect the calendar control to the db so that when I click on a block on the caklendar it will display all appointments for that day???
You will have to build the code to query the database and populate the boxes manually within the calander's click() event.  Inside of the ciick event, run and execute on you connection object (assuming you are usiong ado for db connectivity) with criteria specified that only queries for that day.  Take the recordset from that query and iterate through it populating what boxes need to be filled and clearing the ones that don't..  You may also want to store the non-filled ones, say in a collection, for making the dropdown you spoke of easier to do in you appointment form...
MrdogkickAuthor Commented:
Sorry, im not that hot on VB so what u just said is kinda confusing...
I take it you already have a database connection correct?
are your textboxes each individual textboxes or are they part of an textbox array?
MrdogkickAuthor Commented:
Actually, I have been trying to set up my db connection but am finding it difficult. I did it a couple of years ago and am not sure where I'm going wrong...I'm using a adodc but can't see where I set the path or the tables...I know this is a simple task but am stumped!!!

They are individual text boxes, would it be better to do it the other way? How would I go about that?
OK although it is not an exact fit to your problem I have some Calendar functionality in one of my Apps that may help you, although it is an ENTIRELY different approach to what you are attempting here....BUT it could VERY easily be adapted to what you are trying to achieve.....and I have no problem posting the code if your interested? But I won't do so unless you are interested because it is not exactly an answer to your question but rather a different approach to achieve similiar.

The back round - It's a VB 6.0 App and basically my requirements were to display an entire month view (by days) those employees that were either On Leave (or out of the Office) and the type of leave as coloured blocks (including public holidays). By clicking a blocked out time a popup screen appears to display details of the leave period etc.....

I have an MSHFlexGrid that displays an entire month... Days as the columns and Employee Name as the  rows. ...........I'm thinking you could easily be convert the Rows into  hour blocks in a day to record client details etc.

Let me know if you're interest because I really don't want to take you away from the path your on if that is the best fit for your App.....
MrdogkickAuthor Commented:
Thanks very much for the offer mate but I thin I will stick to my original plan, It has taken me a while to envision it so I don't want to go astray. Thanks again!
MrdogkickAuthor Commented:
To Qucklearner:

"Paste until you have a total of 48 boxes (24 hours * 2 half hours per hour) the indexes will be 0 - 47"

I have changed it so that it is hour blocks form 09:30 am - 10:30 pm so there are now 12 boxes...I don't quite understand how you worked out the index part???
Your question states half-hour (eg 9:00-9:30 - the text boxes are listed as half hour slots); i was allocating all of the way from midnight to midnight..  Let me explain how the indexes worked in my example and we will go from there:
First off i want to point out that the first box of your 12 is index 0. (0-11 will be your range, 0-47 was mine)
datepart("h") returns a value from 0-23 (military)
Notice both start with 0
Now because there are two boxes per hour (half-hour increments) for every 1 added to the hour, the index representing that same time, in the boxes, will need to have 2 added:
12am = 0 * 2 = index 0
12:30 am = index 1 ' added this to fill in the time hole but this would be based on more than just the hour..
1am = 1 * 2 = index 2
2am = 2 * 2 = index 4
and so on..
For ease think of it as this: first half hour of each hour is even
second half hour of each hour is odd
After figuring out the whole hour, you will see i next check the minutes.  End time minutes are treated slightly different than start time minutes in that an end time of 4 pm usually would be represented the same way as 3:59pm because you may have something else that starts at 4pm and wont want to affect the 4pm hour, (same thing with the exact half hour X:30)
datepart("n") returns the number of minutes in a date (0-59).
For an end date, if it happens to be 0 then I remove 1 from the index placeholder as i don't actually want to affect that index (refer to two sentences ago)  If the minutes figure to be >30 then i add one to the index holder
For the startdate, the even minutes do count so first off i don't have to consider anything weird about a 0 and when adding 1 to this index holder the 30 is included (if it starts at 10:30 i will want to start at index 21 ((10 *2) + 1).  Make any sense whatsoever or do I just suck?
MrdogkickAuthor Commented:
I kinda get what your saying now, but I'm thinking it's going a bit more complex than it nweeds to.. I dont really need the text boxes to be times if you get me, more like blocks that are either vacant or full, just with a label that says its a time period. Then i would have these same blocks as a drop down list in my create new appointment form and this would then mark taht block as full on whichever day it was on when the correct date was selected from the calendar...The text box would probably just display the customers name and the treatment then there will be a button beside each text box which when c,licked will link to another page with a detailed description of the appointment (price, materials used, etc...) Do you follow??? What do you think??
If you are just going to show a box for each appointment, then add "order by starttime" and just populate a record into each box.  By doing it this way, you are moving away from the more regimented scheduling I was doing and may make it much more complex in dealing with open timeslots later on..  I have answered both parts of your question though correct?
MrdogkickAuthor Commented:
Although I'm still a little confused, you've done all you can I guess. If I open another question will you have a look? Is there any way I could get your email add. so I can send you some screen shots or whatever so you can see what I'm trying to do??
If you are still confused on the info i have provided, let me know where and i will explain it in detail..  I just wanted to verify that I answered the question as it was stated in [Question:].  I will definitly have a look.  You can post yours.  Usually if they are on EE, they are in profile descriptions of people, but I like privacy, so I haven't posted nor intend to post mine..
no worries just thought I'd offer as it's some really simple but extremely effective code...If you would like to have a look just out of interest here's my email drop me a line and I'll send some screenshots to you coz as they say a picture tells a thousand words :)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.