Solved

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

Posted on 2013-11-22
4
430 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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