Solved

Urgent!! Calendar Control Help Needed!!!

Posted on 2007-04-10
19
242 Views
Last Modified: 2010-04-30
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!!!
0
Comment
Question by:Mrdogkick
  • 9
  • 8
  • 2
19 Comments
 
LVL 4

Expert Comment

by:quiklearner
ID: 18882912
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?
0
 

Author Comment

by:Mrdogkick
ID: 18883079
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??
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18883131
sounds good to me.  But now I am left wondering what exactly your question is.
0
 

Author Comment

by:Mrdogkick
ID: 18883211
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???
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18883313
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...
0
 

Author Comment

by:Mrdogkick
ID: 18883633
Sorry, im not that hot on VB so what u just said is kinda confusing...
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18883645
I take it you already have a database connection correct?
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18883804
are your textboxes each individual textboxes or are they part of an textbox array?
0
 

Author Comment

by:Mrdogkick
ID: 18883846
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?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 4

Accepted Solution

by:
quiklearner earned 500 total points
ID: 18883984
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
DSN
UID
PWD
DRIVER
SERVER
DATABASE
DBQ

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=127.0.0.1;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
        ors.movenext
    Wend
    ors.Close
    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
0
 
LVL 5

Expert Comment

by:mah8473
ID: 18887831
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.....
0
 

Author Comment

by:Mrdogkick
ID: 18889623
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!
0
 

Author Comment

by:Mrdogkick
ID: 18889648
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???
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18890433
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?
0
 

Author Comment

by:Mrdogkick
ID: 18890764
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??
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18890858
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?
0
 

Author Comment

by:Mrdogkick
ID: 18891139
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??
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18891218
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..
0
 
LVL 5

Expert Comment

by:mah8473
ID: 18902634
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 :)

mhorman@convergency.com.au
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now