How do I use Activex Calendar in a form

I have a form with fields
StartDate and EndDate.
I would like to use the activex calendar.
I've added it (calendar) to the form - but how do I get it to place date values in StartDate and EndDate fields?
capital052798Asked:
Who is Participating?
 
paaskyConnect With a Mentor Commented:
Capital,

Calendar control has click event but as I mentioned earlier ActiveX controls are poorly documentated and some of their properties are hidden.

You told me you have named (actually Access has named) your Calendar "ActiveXCtl12" so I used that name in my sample. It would be wiser to use better names, something like XCalendar.

I have made you an example form your can download from http://www.iwn.fi/paasky/pub/ 

The file name is calendar1.mdb, you can test and modify it to fit your needs.

Regards,
Paasky


0
 
wesleystewartCommented:
I haven't been able to get one calendar to give me both start and end dates.  I use two calendars named dtmFrom and dtmTo like this:

strSelect = "SELECT *"
strFrom = " FROM Orders "
strWhere = " WHERE (((Orders.outstanding) Is Not Null) AND ((Orders.DateEntered) Between #" & dtmFrom & "# And #" & dtmTo & "#));"

Wes
0
 
BrianWrenCommented:
The calendar has events that don't show in the properties window.

If you go into the mdule for the form, and on the left select the control from the drop-down list, then drop-down the list on the right, you'll see Click, DoublClick, etc., etc. available for the object.

The objects current value is the date selected.

So you can use clicks and so on, or you can put a StartDate and EndDate filed on your form, and two buttons, labelled Start Date and End Date.  When a button is clicked,

   Me!StartDate = Me!CalCtrlObj

Brian

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
paaskyCommented:
Hi capital,

Wesleystewart's solution is good if you have plenty of room in your form. Usually that's not the case so you could make a small 'Calendar' button next after textbox. When the user presses the button he/she will get a popup calendar (a form with calendar) and can choose date.

Calendar like other ActiveX component is poorly documented. Most important properties are Value (default) and Today -method (sets value to current date).

Regards,
Paasky
0
 
BrianWrenCommented:
I'm sorry paasky, wes:

I don't see how an SQL statement addresses the issue of putting the value of the Calendar control into fields...

At first I thought is was a case of failing to read the question well, (which I, unfortunately, do all the time).  But now TWO people find that to be an answer, and I'm confooosed...

Brian
0
 
paaskyCommented:
BrianWren,

Hmmm.. you're absolutely right. I guess I read wesleystewards first lines careful enought and forget the rest when I had scrolled it off my sight. The older I get the shorter my memory is. Wesley propably ment that code just a sample for using two calendar objects.

capital,
Just forgot my referencing to wesley's SQL-clause if you're not comparing anything.

Paasky
0
 
wesleystewartCommented:
I guess my thought was:

Why bother putting the date into a field on the form?  The calendar provides a graphic display of the date you have chosen, and two of them provides both a date display and criteria for your query.  It seems like a lot of code and clicking to click a button, select a date, fill in a field, and then reference that field in a query, and then do the whole thing over a second time for the end date.

Simplify, simplify, simplify.

Of course, if you must fill in 2 text boxes, then by all means, get coding:-)

Wes
0
 
BrianWrenCommented:
But Wes, no query was mentioned in the question . . .

It might be that this is being used to fill in several records in a table, record--by--record.

Personally, the way I like to use the calendar control is to have it on a pop-up.  The user double clicks the field where the info is to go, and the calendar pops up to select the date.  Then it goes away.

Brian
0
 
wesleystewartCommented:
C'mon, there's got to be a query in there somewhere . . . .
0
 
phil2freeCommented:
Capital,

There have been a lot of comments on this question already, but I had some code that was complete that I think can help you.
This is how I fill in a field from a calendar.  The Calendar's visible property is false by default and it is toggled by pressing cmdCalendar.
The calendar appears just below the button.

This is copied out of so code of mine so ignore the field names.

Private Sub Calendar_AfterUpdate()
    txtBoardDate.value = Calendar.value
    cmdCalendar.SetFocus
    Calendar.Visible = False
End Sub

Private Sub Calendar_Click()
    cmdCalendar.SetFocus
    Calendar.Visible = False
End Sub

Private Sub cmdCalendar_Click()
    Calendar.Visible = Not Calendar.Visible
End Sub

Hope this helps,

Phil
0
 
capital052798Author Commented:
go easy on me!
I need to understand step by step - how to do this.
The activex control for calendar is named ActiveXCtl12.

Re the programme lines above - do I place the code on one of the events for ActiveXCtl12.

I guess re the button solution
I set up the button - then on event say "click" - i write some code that says put the focus of ActiveXCtl12
into form field StartDate - then another button ditto to "EndDate".
Have I got the logic right?
0
 
paaskyCommented:
capital,

Here's a sample code. It works with a form where are two text boxes named txtStartDate and txtEndDate and Calendar object ActiveXCtl12 and two buttons B_SelStartDate and B_SelEndDate.

Option Compare Database
Option Explicit
Dim iDateFieldID As Integer

Private Sub ActiveXCtl12_Click()
    ' User clicks (picks) calendar date
    ' Move selected date to textbox and remove focus from Calendar
    Select Case iDateFieldID
        Case 1: txtStartDate = ActiveXCtl12.Value
                txtStartDate.SetFocus
        Case 2: txtEndDate.Value = ActiveXCtl12.Value
                txtEndDate.SetFocus
    End Select
   
    ' Hide Calendar
    ActiveXCtl12.Visible = False
End Sub

Private Sub B_SelEndDate_Click()

    ' User press button next to EndDate
    iDateFieldID = 2
    ActiveXCtl12.Visible = True
   
    ' Set EndDate value to Calendar if not null
    If IsDate(txtEndDate.Value) Then
        ActiveXCtl12.Value = txtEndDate.Value
    End If
End Sub

Private Sub B_SelStartDate_Click()

    ' User press button next to StartDate
    iDateFieldID = 1
    ActiveXCtl12.Visible = True
   
    ' Set StartDate value to Calendar if not null
    If IsDate(txtStartDate.Value) Then
        ActiveXCtl12.Value = txtStartDate.Value
    End If
   
End Sub

Private Sub Form_Open(Cancel As Integer)
    ' Hide calendar object
    ActiveXCtl12.Visible = False
End Sub


I hope this helps, regards,
Paasky
0
 
paaskyCommented:
One addition:

In case you want to set Calendar default date as current Date when txtStartDate or txtEndDate value is null make these changes to my code above:

....
    ' Set StartDate value to Calendar if not null
    If IsDate(txtStartDate.Value) Then
        ActiveXCtl12.Value = txtStartDate.Value
    Else
        ActiveXCtl12.Today
    End If
....


....
    ' Set EndDate value to Calendar if not null
    If IsDate(txtEndDate.Value) Then
        ActiveXCtl12.Value = txtEndDate.Value
    Else
        ActiveXCtl12.Today
    End If
....

Paasky



0
 
capital052798Author Commented:
paasky
where do I put the code?
e.g.
Private Sub ActiveXCtl12_Click()
    ' User clicks (picks) calendar date
    ' Move selected date to textbox and remove focus from Calendar
    Select Case iDateFieldID
        Case 1: txtStartDate = ActiveXCtl12.Value
                txtStartDate.SetFocus
        Case 2: txtEndDate.Value = ActiveXCtl12.Value
                txtEndDate.SetFocus
    End Select
     
    ' Hide Calendar
    ActiveXCtl12.Visible = False
End Sub

I assume this goes against the "OnClick" event for ActiveXCt112 - however the only events available are
On Updated
On Enter
On Exit
On GotFocus
On LostFocus

OR perhaps this all goes against the class module for the form - if so what event.
0
 
capital052798Author Commented:
I'm making progress

I've got paasky's code into the form class module
Option Compare Database
Option Explicit
Dim iDateFieldID As Integer
Private Sub ActiveXCtl12_Click()
    ' User clicks (picks) calendar date
    ' Move selected date to textbox and remove focus from Calendar
    Select Case iDateFieldID
        Case 1: txtStartDate = ActiveXCtl12.Value
                txtStartDate.SetFocus
        Case 2: txtEndDate.Value = ActiveXCtl12.Value
                txtEndDate.SetFocus
    End Select
     
    ' Hide Calendar
    ActiveXCtl12.Visible = False
End Sub
Private Sub B_SelEndDate_Click()

    ' User press button next to EndDate
    iDateFieldID = 2
    ActiveXCtl12.Visible = True
     
    ' Set EndDate value to Calendar if not null
    If IsDate(txtEndDate.Value) Then
        ActiveXCtl12.Value = txtEndDate.Value
    End If
End Sub

Private Sub B_SelStartDate_Click()
' User press button next to StartDate
    iDateFieldID = 1
    ActiveXCtl12.Visible = True
     
    ' Set StartDate value to Calendar if not null
    If IsDate(txtStartDate.Value) Then
        ActiveXCtl12.Value = txtStartDate.Value
    End If
     
End Sub
Private Sub Form_Open(Cancel As Integer)
    ' Hide calendar object
    ActiveXCtl12.Visible = False
End Sub


I'm getting a compile error
ActiveXCtl12  variable not defined!
0
 
BrianWrenCommented:
Put

   Me!

in front of every reference to the control in the class module

  Case 1: txtStartDate = Me!ActiveXCtl12.Value

Etc.

The default property of the control is .Value, so you can put:

  Case 1: txtStartDate = Me!ActiveXCtl12

everyplace that you are actually refering to the date in the control.

Capital, when I posted my response, (2nd Comment), why didn't you respond?  Here now is paasky implementing the same thing that I was getting at, and which I would readily have helped you with.  But now it's his thread . . .

Shoot!

Brian
0
 
paaskyCommented:
BrianWren,

I hope you don't feel I'm stepping on your toes, that was definetily not my purpose and I apologize if I did. Capital asked step-by-step instructions how this calendar thing should work so I was only trying to help him.

Sincerely,
Arto Pääskynen

0
 
BrianWrenCommented:
Arto,

Not to worry.  (S)He asked for it at 1 in the morning, and you gave a response at 3am, (my time).

You were there firstest with the mostest, following the request.  I do wish the request had come along sooner;  I have really thought this to be a fun question to answer...  Oh well.

Brian
0
 
capital052798Author Commented:
Brian
Re getting back to you - I posted my question in the evening - when I checked the next day (elapsed time 17 hours) there was a number of responses. Non of which I could follow with my limited experience - hence I asked for the step by step instructions paasky was first back on this.
The way I work this forum is the first response that I can follow and works wins - BUT I do appreciate everyone's help and I only wish I had enough points to give everybody some.
Perhaps there should be some way of letting the "expert" know the level of experience the questioner has so that the reply can be scripted accordingly.
0
 
capital052798Author Commented:
paasky - thanks finally got there.
Downloading your mdb example was the best way for me to get it solved.

Brian - I'll try and give you some points next time - I'm bound to have another question before long BUT we may have a problem re world time clock.
As I write its 16:30 hours here in the UK what time is it where you are?
0
 
paaskyCommented:
BrianWren,

I'm glad you're not in bad mood because of me. Personally I think different time zones makes EE more exciting. When I sleep you get your change to answer first and vice versa. ;)

Regards,
Paasky
0
All Courses

From novice to tech pro — start learning today.