Solved

Review of transition from Allen Browne’s Calendar form to Stephen Lebans’ API Calendar.

Posted on 2013-11-22
4
437 Views
Last Modified: 2013-12-06
Good day.

My concern; I want to ensure that I’m not causing a bigger problem down the road transitioning from Allen Browne’s Calendar form to Stephen Lebans’ API Calendar.  Wanted to run this by the Experts, thank you for your time.

Recently our organization upgraded to Office 10; so doing laid many of my databases to waste.  I was using a Calendar utility from Allen Browne that greatly simplified adding dates to the many forms throughout my databases.  Office 10 (as you probably already know) does not include the calendar object so I sought out another solution.  I ran across the calendar produced by Stephen Lebans (and others) that according to the authors does not suffer from versioning problems like ActiveX Controls.  I will admit that my knowledge of VBA is limited; although I can typically work through understanding basic code.

With the Allen Browne version to ‘Fire’ the calendar the user would place the following in the controls OnClick event (Original) function.:
=CalendarFor([tTextBox],"Title for Calendar Form")

I used the format of the first Function to create a function for Stephens Lebans calendar so I didn’t have to add the OnLoad and UnLoad events for every form/control using the original function.
=CalendarFor([Form], [Control],"Title for Calendar Form")

Before I implement this across my database, is this a reasonable solution to my problem?

**** Allen Browne Original Code ***********************************************

    'Author:  Allen Browne. allen@allenbrowne.com
    'Calendar form variable:
'Public gtxtCalTarget As TextBox 'Text box to return the date from the calendar to.
Public mc As clsMonthCal

'Public Function CalendarFor(txt As TextBox, Optional strTitle As String)
'On Error GoTo Err_CalendarFor
'
'    'Purpose:   Open the calendar form, identifying the text box to return the date to.
'    'Arguments: txt = the text box to return the date to.
'    '           strTitle = the caption for the calendar form (passed in OpenArgs).
'    'Example    =CalendarFor([dBegDate],"Set the Beginning Date")
'
'    Set gtxtCalTarget = txt
'    DoCmd.OpenForm "frmCalendarPopup", windowmode:=acDialog, OpenArgs:=strTitle
'    'DoCmd.OpenForm "frmCalendar", windowmode:=acDialog, OpenArgs:=strTitle
'
'Exit_CalendarFor:
'    Exit Function
'
'Err_CalendarFor:
'    Call GblErrHandle(Err.Description, Err.Number, "modCalendar", "CalendarFor", Erl)
'    Resume Exit_CalendarFor
'
'End Function

**** New Function ***********************************************

    'Author:  Stephen Lebans
    ‘Used Allen Browne to Call Stephen Lebans Calendar

Public Function CalendarFor(frm As Form, ctl As Control, Optional strTitle As String)
On Error GoTo Err_CalendarFor
       
'    Purpose:   Since Office 10 does not include the Calendar control I had to redesign my process.
'        Before I would open the calendar form, identifying the text box to return the date to.
'        Now, using Stephen Lebans calendar process I want to be able to use the newly created
'        function and not add the OnLoad and UnLoad events for every form/control for the API
'        calendar using the original function.
'    'Arguments: frm/ctl = the Form and Control that called the function and where the date is to return.
'    '                    strTitle = the caption for the calendar form (passed in OpenArgs).
'    'Example    =CalendarFor([Form],[tDate],"Inspection Month")

'    Added the OnLoad, UnLoad and Control Events to this Function
   
        ' Create an instance for the calendar class
    Dim hWnd As Long

        ' This must appear here to create an instance of our Class
    Set mc = New clsMonthCal
        ' You MUST SET the class hWndForm prop!!!
    mc.hWndForm = hWnd
        ' Let's default to PositionAtCursor
    mc.PositionAtCursor = True
   
        ' return the value from the calendar to the calling form
    Dim sFrm As String
    Dim sCtl As String
   
    sFrm = frm.name
    sCtl = ctl.name
       
        ' Open the calendar
    Dim blRet As Boolean
    Dim dtStart As Date, dtEnd As Date

    dtStart = Now()
    dtEnd = 0

    blRet = ShowMonthCalendar(mc, dtStart, dtEnd)
    If blRet = True Then
        Forms(sFrm).Controls(sCtl) = dtStart
    Else
        MsgBox "Hey - You forgot to select a Date!", vbOKOnly, "Did you forget to select a date?"
    End If
       
        ' Clear the form and control
    Set ctl = Nothing
    Set frm = Nothing

        ' This is required in case user Closes Form with the Calendar still open.
        ' It also handles when the user closes the application with the Calendar still open.
    Dim Cancel As Integer
   
    If Not mc Is Nothing Then
        If mc.IsCalendar Then
            Cancel = 1
            Exit Function
        End If
     
        Set mc = Nothing
    End If
   
Exit_CalendarFor:
    Exit Function

Err_CalendarFor:
    Call GblErrHandle(Err.Description, Err.Number, "modCalendar", "CalendarFor", Erl)
    Resume Exit_CalendarFor
   
End Function

**** Stephen Lebans Original Code ***********************************************

Private Sub Form_Load()

' Create an instance of our Class
Set mc = New clsMonthCal
' Set the hWndForm Property
mc.hWndForm = Me.hWnd

End Sub
 
Private Sub Form_Unload(Cancel As Integer)

' This is required in case user Closes Form with the
' Calendar still open. It also handles when the
' user closes the application with the Calendar still open.

If Not mc Is Nothing Then
    If mc.IsCalendar Then
        Cancel = 1
        Exit Sub
    End If
 
Set mc = Nothing
End If

End Sub

** Code that is entered into each control

Private Sub txtSelectDate_DblClick(Cancel As Integer)

Dim blRet As Boolean
Dim dtStart As Date, dtEnd As Date

dtStart = Nz(Me.txtSelectDate.Value, 0)
dtEnd = 0

blRet = ShowMonthCalendar(mc, dtStart, dtEnd)
If blRet = True Then
    Me.txtSelectDate = dtStart
Else
' Add any message here if you want to
' inform the user that no date was selected
End If

End Sub
0
Comment
Question by:Imoutwest
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 300 total points
ID: 39670503
"Office 10 (as you probably already know) does not include the calendar object "

Actually, Access 2010 does. There is now a built in calendar popup (aka 'Date Picker') that will appear in any text box that contains a date value.  You can also turn this off on a per text box basis.

My personal recommendation is to use this calendar, and let the legacy calendars go, as there is no guarantee they will work in future versions of Access - especially Leban's VBA/API calendar.

mx
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 200 total points
ID: 39670584
I ran across the calendar produced by Stephen Lebans (and others) that according to the authors does not suffer from versioning problems like ActiveX Controls.
That is a very old comment made before the 64-bit versions of Windows and  Office were available.

I agree with MX about avoiding API calendars because of versioning problems.

I opt for using a pop-up calendar built with native Access controls. It was originally development in Access 2.0 running on  Windows 3..  It has  worked with every version of Access and Windows  since. Including the 64-bit Office versions.
0
 
LVL 7

Author Comment

by:Imoutwest
ID: 39670787
Thank you for your comments, I have seen the new calendar popup that shows on newly added textboxes.  I agree with your assessment, it is just that I have to redo every control that uses a textbox, really tired of starting over. Maybe that is for the best, at least I won't have to change this point forward.
0
 
LVL 7

Author Closing Comment

by:Imoutwest
ID: 39701190
Not the answer I wanted, but as I expected. Split the points; database MX was faster.  Thank you both, imoutwest
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

685 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