Solved

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

Posted on 2013-11-22
4
395 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 - Access MVP) earned 300 total points
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
Not the answer I wanted, but as I expected. Split the points; database MX was faster.  Thank you both, imoutwest
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 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

11 Experts available now in Live!

Get 1:1 Help Now