We have already seen how to create and install a Reminder Ticker that runs on a straight line on the Main Screen. We could do this with few lines of VBA Code and a Label on the Main Screen. We will try something different this time. This one not runs on a straight line but runs in a Zigzag form. An image of a sample run of this method is given below:



To create this Ticker we need a series of labels arranged in a wave like form and each one of them must be named in such a way that we could address them easily in code. A sample design is given below:



There are about 42 identical Labels to create. Even if we create them once manually, arranging them in this fashion is not an easy task. But we can do it with a small Program. The Program creates a new Form and creates all 42 Labels, name them as lbll to lbl42, changes other Properties as shown above.

1. Copy the following Code into a Global Module of your Database and save it.



Public Function ZIGZAG()
'-----------------------------------------------------------
'Author  :  a.p.r. pillai
'Date    :  01/10/2008
'URL     :  www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim frm As Form, ctrl As Label, t As Long, lngleft As Long
Dim lngwidth As Long, lngheight As Long, lngtop As Long
Dim j As Integer, k As Integer, h As Long, G As Long

h = 30: G = 0: t = 0
lngwidth = 0.1146 * 1440
lngheight = 0.2083 * 1440
lngtop = 1 * 1440
lngleft = 0.16667 * 1440
Set frm = CreateForm
For j = 1 To 42
Set ctrl = CreateControl(frm.Name, acLabel, acDetail, , , lngleft, lngtop, lngwidth, lngheight)
lngleft = lngleft + lngwidth
With ctrl
    .Name = "lbl" & j
    .FontName = "Tahoma"
    .FontSize = 8
    .Caption = ""
    .BackStyle = 0
    .ForeColor = 255
 End With
 Next
G = 0
For j = 1 To 3
     For k = 1 To 7
        G = G + 1
        Set ctrl = frm.Controls("lbl" & G)
        With ctrl
            .Top = .Top - (h * k)
        End With
        DoEvents
     Next
    t = frm.Controls("lbl" & G).Top
     For k = 1 To 7
         G = G + 1
        Set ctrl = frm.Controls("lbl" & G)
        With ctrl
            .Top = t + (h * 1)
        End With
        t = frm.Controls("lbl" & G).Top
        DoEvents
    Next
Next
End Function



2. You can run the above Code directly by placing the cursor in the middle of the Code and pressing F5 Key, or running from a Command Button's On Click Event Procedure or from a Macro.


Every time when the code is run it will create a new Form with the Labels arranged in a zigzag form. After you create it once, export that Form into your other Projects where you want to install the ZigZag Ticker. Or you may install the code in a Common Library Database and run it after attaching the Library File to your Project.


3. After creating the Labels, click somewhere outside the Labels and drag over them so that all the Labels are selected without disturbing the arrangement of the labels.


4. Select Copy from Edit Menu.


5. Open the Main Switch Board (Control Form) in Design View and Paste them.


6. When all the labels are still in selected state drag and place the Labels into position where you want the Ticker to appear on the Form.


We have two more Sub-Routines which are run from the Form_Load() and Form_Timer() Event Procedures. On the Form_Load() Event Procedure we can create a Text Message in a String either with a constant value or with Field Values from a Table/Query that provide useful information to display to the User as a reminder. Refer the earlier example Reminder Ticker Form which uses information from within the Application for Reminder.


The Form_Timer() Event Procedure will control the Display of Label values shifting one character at a time in succeeding labels giving it a sense of motion.



7. Copy and the Paste the following Sub-Routines into the Form Module where you have pasted the above labels.



Option Compare Database
Option Explicit
Dim txt As Variant

Private Sub Form_Load()
    txt = Space(42) & UCase("Excellence is not a matter of chance. It is a matter of Change. It is not a thing to be waited for. It is a thing to be achieved.")
    Me.Timerinterval=250
End Sub



8. See that the Dim txt As Variant is placed in the Global Area of the Module, which is referenced from the Form_Load() and Form_Timer() Event Procedures.



Private Sub Form_Timer()
Dim x As String, k As String, j As Integer, ctrl As Control

  x = Left(txt, 1)
  txt = Right(txt, Len(txt) - 1)
  txt = txt & x
  k = Left(txt, 42)
For j = 1 To Len(k)
    Set ctrl = Me.Controls("lbl" & J)
    Ctrl.Caption = Mid(k, j, 1)
Next
End Sub
 




9. The following lines of code are useful if you plan to disable the ticker when the Main Form is inactive and run it again when the Main Form is active again, so that other processes not interrupted by the Ticker.

Private Sub Form_Deactivate()
   Me.TimerInterval = 0
End Sub


Private Sub Form_Activate()
   Me.TimerInterval = 250
End Sub

---

Opening dBase Files Directly
Opening External Data Sources
PIE Chart Object and VBA
Column Chart and VBA
Working with Chart Object in VBA

Labels: msaccess animation

Since, we have tried many options with Office Assistant earlier we will do some fun stuff this time. Those who entered straight into this page may like to look into the following Topics. The links to the earlier Articles are given below:

1. Message Box with Office Assistant


2. Message Box with Menu Options using Office Assistant


3. Message Box with Check Box type Menu with the use of Office Assistant


4. Command Button Animation (Visit this page to Link the Microsoft Office Library File to your Project).

We all know that we can select the Office Assistant of our choice from Help Menu of MS-Access or other MS-Office Applications as well. Instead, we will design a Form in MS-Access to organize all the Office Assistant Characters available on our PC on the Form, display them and select the one that we like, set it as the default Office Assistant, as we please.


A sample image of the Program Run is given below:




We need a small Table with the Office Assistant’s File names for a List Box as shown at the left side of the Form above.

1. Create a Table with the Field Names as shown below, save it with the name Assistant and enter the Data from the List.




Out of eleven items the first seven files are on the same location and others are found elsewhere. Before entering the above list into your Table it is a better idea if you run a search for all the files with the File Extension .ACS on your machine and use those files for the list.


To search for the files select Start -> Search -> For Files or Folders -> All Files and Folders and enter the search term *.ACS on the Search Control and Click Search. When the search is completed, note down the correct path of all the files displayed and then enter them into your Table with the full Path Name.



2. Design a small Form and set the Properties of the Form and controls as given below to match the design.





3. Create a List Box on the Detail Section of the Form. Click on the List Box (if it is not selected), Display the Property Sheet (View -> Properties) and change the Property Values as shown below:



• Name          :     AsstList
• Row Source Type  :      Table/Query
• Row Source  :      SELECT Assistant.id, Assistant.asstChar FROM Assistant;
• Column Count  : 2
• Column Heads  : No
• Column Widths  : 0”;0.875”
• Bound Column  : 1
• Multi Select  : None
• Left   : 0.125”
• Top   : 0.3125”
• Width   : 1.2083”
• Height   : 1.4375”
• Font Weight  : Bold




4. Create a Label on the top of the List Box and change the Caption to Office Assistant.



5. Draw a Rectangle Control at the right side of the List Box and change the Properties as given below.




• Left  :  1.4271”
• Top  : 0.3125”
• Width  : 1.7188”
• Height  : 1.4375”
• Back Style : Transparent
• Special Effect  : Sunken




6. Create two Command Buttons below the List Box as shown on the Design. Display the Property Sheet of the Left Command Button. Change the Properties as given below:



• Name            :  cmdSetAsst
• Caption         :  Set as Default




7. Change the properties of the right-side Command Button.



• Name        :   cmdCancel
• Caption     :   Cancel




8. Now, to change the Properties of the Form click on the left top corner of the Form (at the intersection where both the vertical and horizontal Scales meet). Display the Property Sheet, click on the All Tab of the Property Sheet, if that is not the current one, and change the Property Values as given below.



• Caption             :  Office Assistant
• Default View        :  Single Form
• Scroll Bars         :  Neither
• Record Selectors    :  No
• Navigation Buttons  :  No
• Dividing Lines      :  No
• Auto Resize         :  Yes
• Auto Center         :  Yes
• Pop Up              :  Yes
• Modal               :  No
• Border Style        :  Dialog
• Control Box         :  Yes
• Min Max Buttons     :  None
• Close Button        :  Yes
• Whats This Button   :  No
• Width               :  3.3333”
• Allow Design Changes:  Design View Only



9. Click on the Detail Section of the Form. Display the Property Sheet, if you have already closed it, and change the Property:



• Width = 2.4271”




10. Select Save from File Menu and save the Form with the name Assistant and stay in Design View, don’t close the Form.



11. Display the VBA Module of the Form, View -> Code. Copy and Paste the following Code into the VBA Module, save and close the Form:



Dim defaultAssistant As String
Dim strAsst() As String
Private Const m_left As Integer = 500
Private Const m_top As Integer = 225


Private Sub AsstList_Click()
Dim vID As Byte, strFileName, FRM As Form, l As Long
Dim ctrl As Control


On Error GoTo AsstList_Click_Exit:


vID = [AsstList]
With Assistant
    .On = True
    .fileName = strAsst(vID)
    .Animation = msoAnimationGetAttentionMajor
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
    .Visible = True
End With

AsstList_Click_Exit:
Err.Clear
End Sub



Private Sub cmdCancel_Click()
On Error GoTo cmdCancel_Click_Exit
With Assistant
    .On = True
    .fileName = defaultAssistant
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
    .Visible = True
End With
DoCmd.Close acForm, Me.Name

cmdCancel_Click_Exit:
Err.Clear
End Sub




Private Sub cmdSetAsst_Click()
DoCmd.Close acForm, Me.Name
End Sub




Private Sub Form_Load()
Dim db As Database, rst As Recordset
Dim acsFiles As Integer, i As Integer

On Error GoTo Form_Load_Exit
defaultAssistant = Assistant.fileName

acsFiles = DCount("* ", "Assistant")
If acsFiles = 0 Then
   MsgBox "Assistants File Not found. "
   Exit Sub
End If
ReDim strAsst(1 To acsFiles) As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Assistant", dbOpenDynaset)
i = 0
Do While Not rst.EOF
i = i + 1
strAsst(i) = rst![Path]
rst.MoveNext
Loop
rst.Close

With Assistant
  If .On = False Then
    .On = True
  End If
    .fileName = defaultAssistant
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
  If .Visible = False Then
    .Visible = True
  End If
End With
Form_Load_Exit:
Err.Clear
End Sub




12. Open the Form in normal View. Click on the items in the List Box one by one giving a little time gap to the Assistant Character to appear. When your favorite image appears, click on the Set as Default Command Button to set the current Character as default Office Assistant. If you change your mind then click Cancel Button to retain the existing one.

You can export this Form into your other Projects and use it there after linking the Microsoft Office Library File to your Project.


NB: At the beginning of the Code I have defined two Constants m_left = 500and m_top = 225 to position the Image on the Form when it appears based on my machine's Screen Resolution 1024 x 768. You may change this value to adjust it to your machine’s Screen Resolution.

---

Automated Email Alerts.shtml
Configure Outlook for Lotus Notes
MS-Access and Email
Dynamic Report
MS-Access & Mailmerge-3

Labels: msaccess animation

We have seen the usage of Office Assistant for Message Boxes and Message Box with Options Menu in the earlier Article.


Like I said before, when we design Applications there must be something different in it better than what we did earlier, better and attractive to the User as well as to the outside world.


Data processing has its own importance and I am not overlooking it here. Every Application that we develop is different and a challenge in its own merit and we have to devise new methods or techniques to deal with the issues in them.


But, repeating the same type of Application design and using the same kind of controls like the Command Buttons or Message Boxes over and over again is a boring experience, to the developer and to the User as well. I think that is one of the reasons why Windows or Microsoft Office Applications comes out with more features and better looking Programs all the time.


We will try the Check Box type Menu Options in Message Box with Office Assistant. We will use the same sample Form that we have created for running the earlier program. An image of the run of the Program is given below:




If you have not tried the earlier example please read the Article on MsgBox with Options Menu and go through the preparations that I have mentioned there, in order to run the programs given here without errors.


I will be preparing sample databases on most of the Articles that I have published so far and will upload them into the site shortly, so that you can download and try them out instantly.


We have moved recently into our own site: www.msaccesstips.com and I am in the process of updating the site and dealing with the initial teething problems. Once that is over I will concentrate on the above task.

1. Open the Form OptionCheck that we have created in the earlier example in design view.


2. Create a second Command Button on the Form.


3. Click on the Command Button to select it and display the Property Sheet (View -> Properties). Change the following property values as given below.

Name = cmdChk

Caption = Get Checked



4. While the Form is still in design view, display the VBA Module of the Form (View -> Code).


5. Copy and paste the following Code into the VBA Module of the Form and save the Form.



Private Sub cmdchk_Click()
Dim OptionArray(1 To 5) As String
Dim i As Integer, msg As String, title As String

OptionArray(1) = "Display Report Source Data"
OptionArray(2) = "Print Preview"
OptionArray(3) = "Print"

msg = "Please Select an Option"
title = "Report Options"

i = MsgGetChk(msg, title, OptionArray())

If i > 0 Then
    Me![optTxt] = i & " : " & OptionArray(i)
End If

Select Case i
    Case -2
       msg = "Cancelled"
       Me![optTxt] = i & " : Cancelled"
    Case 1
        'DoCmd.OpenForm "myForm", acNormal
        msg = "Display Report Source Data"
    Case 2
        'DoCmd.OpenReport "myReport", acViewPreview
        msg = "Print Preview"
    Case 3
        'DoCmd.OpenReport "myReport", acViewNormal
        msg = "Print"
End Select

        MsgOK "Selected: " & msg

End Sub



This is a copy of the same Program that we have used for Baloon Type Options which is already there in the Form Module. I have made changes in two places in the Code to use it for this example.




• Number of elements in the OptionArray() Variable reduced to three after removing the Cancel item, because the OK and Cancel Buttons will be included in the main program.



• Included testing for the Cancel condition in the Select Case…End Select statements and terminates the program if the User clicks on the Cancel Button. When you implement this method into your Project you can remove the Case -2 and next two statements underneath it. Here it is inserted for demonstration purpose only.





6. Copy and paste the following main program into a Global VBA Module in your Project and save it.




Public Function MsgGetChk(ByVal strText As String, _
ByVal strTitle As String, ByRef obj) As Integer
Dim X As Integer, i, c As Integer, k As Integer
Dim Bal As Balloon, vSelect As Integer

On Error GoTo MsgGetChk_Err

Set Bal = Assistant.NewBalloon
i = 0
k = UBound(obj)
k = IIf(k > 5, 5, k)

For X = 1 To k
   If Len(obj(X)) > 0 Then
     i = i + 1
   End If
Next

ForceEntry:

With Bal
   .Animation = msoAnimationWritingNotingSomething
   .Icon = msoIconAlert
   .Heading = strTitle
   .Text = strText
   .BalloonType = msoBalloonTypeButtons
   For X = 1 To i
     .Checkboxes(X).Text = obj(X)
     Next
   .Button = msoButtonSetOkCancel
  vSelect = .Show
   
  If vSelect = -2 Then
        MsgGetChk = vSelect
        Exit Function
  End If
   
  c = 0
  For X = 1 To i
    If .Checkboxes(X).Checked Then
       MsgGetChk = X ' get the item checked by the user
       c = c + 1 'more than one item checked
       If c > 1 Then
          Exit For
       End If
     End If
   Next

  If c > 1 Then
     strText = "Select only one item. "
     GoTo ForceEntry
  End If

  If c = 0 Then
     strText = "Select one of the Options or Click Cancel! "
     GoTo ForceEntry
  End If

End With

Assistant.Visible = False

MsgGetChk_Exit:
Exit Function

MsgGetChk_Err:
MsgBox Err.Description, , "MsgGetChk"
Resume MsgGetChk_Exit
End Function



This main program is almost same as the Options Menu that we have used for our earlier example. The statement .labels(X).Text = MaxArray5obj(X) changed to .Checkboxes(X).Text = obj(X). Added few more lines of code to check, whether the User



• put check marks in more than one item, if so, forces to select one item only.


• clicked OK Button without selecting any of the Options. If so, asks to select one of the options before clicking OK Button and suggests clicking Cancel Button if there is a change of mind.





7. If the office Assistant is not visible, turn it on, Help - > Show the Office Assistant or press Alt+H followed by Alt+O.



8. Right-Click on the Office Assistant and select Choose Assistant. Click on the Next or Back Button to select the Office Cat Character.


The program works with any Office Assistant Image but the initial Animation Type: msoAnimationWritingNotingSomething set in the Program works fine with the Office Cat and I love the Office Cat.


9. Open the OptionCheck Form in normal View. Click on the Get Checked Command Button. If every thing went through well you will see the Office Assistant with Check Boxes as shown in the image at the top.



10. Try clicking the OK Button with check marks in more than one item. Try clicking OK Button without putting check mark in any of the options.

---

Configure Outlook for Lotus Notes
MS-Access and Email
Dynamic Report
MS-Access & Mailmerge-3
MS-Access & Mailmerge-2

Labels: msaccess animation

This is an extension of my earlier Article on MsgBox with Office Assistant. Here, we will try to make use of Office Assistant to display a set of Menu Options and ask the User to select one of them. When the User clicks on one of the Options we will test for the selected value and execute the action programmed for it.


A demo image of the Baloon Type Menu Options displayed in Office Assistant is given below:

1. Design a simple Form with a Text Box and a Command Button as shown above to run the Demo Program.



2. Click on the Text Box and Display the Property Sheet (View - > Properties) and change the Name property Value to OptTxt. This is used only for displaying the selected Option Text for Demo purposes.



3. Click on the Command Button and display its property sheet. Change the Name Property Value to cmdOpt and the Caption property Value to Get Option.



4. While the sample form is still in Design View, display the Form’s VBA Module (View -> Code). Copy and paste the following Code into the Form’s Module and save the Form with the name OptionCheck or with any name you prefer.

Private Sub cmdOpt_Click()
Dim OptionArray(1 To 5) As String
Dim i As Integer, msg As String, title As String

OptionArray(1) = "Display Report Source Data"
OptionArray(2) = "Print Preview"
OptionArray(3) = "Print"
OptionArray(4) = "CANCEL"

msg = "Please Select an Option"
title = "Report Options"

i = MsgGetOpt(msg, title, OptionArray())

If i > 0 Then
    Me![optTxt] = i & " : " & OptionArray(i)
End If

Select Case i
    Case 1
       'DoCmd.OpenForm "DataForm", acNormal
       msg = "Display Report Source Data"
    Case 2
       'DoCmd.OpenReport "myReport", acViewPreview
        msg = "Print Preview"
    Case 3
        'DoCmd.OpenReport "myReport", acViewNormal
        msg = "Print"
    Case 4
        msg = "CANCEL"
End Select

    MsgOK "selected: " & msg

End Sub

Let us look into what we are doing in the above Routine. First, we have defined a String Array Variable OptionArray with a maximum of 5 elements and loaded them with values in 4 elements out of 5 defined. A maximum of 5 elements or less are allowed in the main program MsgGetOpt() (the Code is given below). Even if you define more than 5 elements it will be ignored by the main program. If you need more than 5 options to handle by the main program MsgGetOpt() then you may modify the line k = IIf(k > 5, 5, k) replacing the value 5 with your required value.


The msg and Title variables are initialized with Option Text and calls the Program MsgGetOpt() with its parameters: msg, Title and OptionArray variable, passing the OptionArray values by reference.


If you have already tried out the MsgBox with Office Assistant and Command Button Animation Topics earlier continue with step 3. But, Copy and Paste these VBA Codes on the same Project where you have placed the Main Programs earlier. Or copy the Main Programs again from the following pages.

1. Copy and Paste the main programs from the Page MsgBox with Office Assistant into a Global Module in your Project and save it.



2. You must Link the Microsoft Office Library File to your project as well. The procedure for doing that is explained on the Page Command Button Animation. A List of other essential Library Files are also given there.



3. Copy and paste the following MsgGetOpt() Code into a VBA Global Module and save it.

Public Function MsgGetOpt(ByVal strText As String, _
ByVal strTitle As String, ByRef MaxArray5obj) As Integer
Dim intVal As Integer, X As Integer, Bal As Balloon, k As Integer
On Error GoTo MsgGetOpt_Err

k = UBound(MaxArray5obj)
k = IIf(k > 5, 5, k)

With Assistant
  If .On = False Then
    .On = True
    '.FileName = "OFFCAT.acs"
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Visible = True
  End If
End With

Set Bal = Assistant.NewBalloon
With Bal
   .Animation = msoAnimationWritingNotingSomething
   .Icon = msoIconAlert
   .Heading = strTitle
   .Text = strText
   .BalloonType = msoBalloonTypeButtons
   For X = 1 To k
    If Len(MaxArray5obj(X)) > 0 Then
     .labels(X).Text = MaxArray5obj(X)
    End If
     Next
   .Button = msoButtonSetNone
    intVal = .Show
End With

Assistant.Visible = False
MsgGetOpt = intVal

Set Bal = Nothing

MsgGetOpt_Exit:
Exit Function

MsgGetOpt_Err:
Err.Clear
Resume MsgGetOpt_Exit
End Function

Open the Demo Form OptionCheck and click on the Command Button. You should see the Office Assistant based MsgBox displaying the Options as shown in the sample image given on top of this page. Click on one of the Options. Another MsgBox will display the Option Text that you have selected. The selected option text with its corresponding sequence Number will appear in the TextBox on the Form as well. The User-selected option value is returned into the variable i in the cmdOpt_Click() Sub Routine. Performs a test on the variable i within the Select Case…End Select structure to find out which option the user has selected and executes the statements programmed there.


Next we will look into the Check Boxes with Office Assistant.

---

MS-Access and Email
Dynamic Report
MS-Access & Mailmerge-3
MS-Access & Mailmerge-2
MS-Access & Mail-Merge

Labels: msaccess animation

In our earlier discussion on Animated Floating Calendar, the method which we have used to display the Calendar Control needs refinement, as I have mentioned there. We will look into the following points and how to deal with them:

1. Advantages & disadvantages of Custom Toolbar Button & Shortcut Menu Button for displaying the Calendar Control and whey we need both?


2. When there is no need for a Calendar Control on a Form how to disable the Buttons?


3. When the Target Control is not a Date type field how to prevent the Calendar from appearing?


4. Do we need the Custom Toolbar or Shortcut Menu Button to display the calendar, is there any other method?

Let us look into the above Questions, one by one.

1. The Custom Toolbar Button creation method was easy to explain without going too deep into the Shortcut Menus. Shortcut Menus are Popup Class of Menus and different from Toolbars. We will learn more about creating Custom Menus, Toolbars & Popup Menus for our Applications later.
   
   To run our Calendar the easiest option is using the shortcut menu button. We can right-click on a field to make that field active and then click on the shortcut menu button, which appears near to the field.
   
   To use the Toolbar button, which normally appears at the top, first you must click on a field on the Form to make that field active and then go up and click on the toolbar button. And every time we will be going up and down this way to use the Calendar.
   
   Since, the button that we have copied is on a built-in shortcut menu, when you open another database on the same machine (with the Calendar Control and its associated programs of course) the button on the shortcut menu will be available to display the calendar control, but not the New Toolbar that we have created in the other database.
   
   This trick will work only on the Machine that you have created the button on the built-in shortcut menu. If you open the same database on another machine the shortcut menu button will not appear there. But the Custom Toolbar Button goes along with the Database to the new machine. We can easily make a copy of our toolbar button on the shortcut menu while installing our Application there.
   
   If we need a copy of the Custom Toolbar in a different database then open that database and Import it from the one, which already have the Custom Toolbar. Select File - > Get External Data - > Import Option and select the database that you already have the Custom Toolbar and click Import button. Click the Options>> button on the ‘Import Objects’ dialogue control to display the import options section and put a check mark in Menus and Toolbars. Don’t select any of the other objects like Tables, Query and Forms etc. Only the Menus and Toolbars will be imported.
   
   
   So we have the advantages of both options, in such situations.
   
   
2. There must be a way to restrict the use these Buttons on specific locations where we actually need it and put away or disable it in other times. This way we can prevent the user from running the program by mistake.
   
   Then, there must be a way to address our Button through Code and enable or disable it at situations that demands it. The best approach is disabling the buttons at startup. Enable the button when the user opens a form with the Calendar Control. When the form is being closed disable it again.
   
   The Menu Bars, Toolbars & Shortcut Menus falls into a Class of Objects known as Commandbars Collection and each Commandbar have a name and an index number. We baptized our Custom Toolbar (Commandbar) as ToolCal (Toolbar for Calendar in short, what a name!). Each Button also falls into the category of Commandbar.Controls; we have named our button as Calendar, so easy to remember. With this background knowledge we will deal with the enable/disable business.
   
   Copy the following Code and paste it into the same Global Module where you have copied our earlier routines of the Calendar programs. We can run this routine by adding a line of code in the appropriate locations on the Forms.
   
   

   
   Private Sub EnableDisable(Byval intStatus as integer)
   Dim cbr1 As CommandBar, cbr2 as Commandbar
   
   DoCmd.ShowToolbar "ToolCal", acToolbarYes 
   
   Set cbr1 = CommandBars("ToolCal")
   Set cbr2 = CommandBars("Form View Control")
   
      Select Case intStatus
         intStatus 0
            cbr1.Enabled = False
            cbr2.Controls("Calendar").Enabled = False
         intStatus 1
            cbr1.Enabled = True
     cbr2.Controls("Calendar").Enabled = True
      End Select
   
   End Sub
   

   
   
   
   
   • The Docmd.ShowToolbar "ToolCal", acToolbarYes will bring the Toolbar up if it is not already visible
   
   
   • Cbr1.Enabled = False will disable the full Toolbar controls, which means, if you have more than one Button on the Toolbar all of them will be disabled.
   
   
   • cbr2.Controls ("Calendar").Enabled = False: Here we are addressing a particular button on the ‘Form View Control’ CommandBar to disable only that button leaving others untouched.
   
   
   
   The best point to run a routine to disable all the buttons that runs our Calendar Control is on the Startup Screen Module. Copy and paste the Code given below into the Startup Screen’s Form_Load() Event Procedure. Since we already have a Form_Load() Event Procedure running there we need to copy the middle line into the module:
   
   

   
   Private Sub Form_Load()
      EnableDisable 0 ‘ 0 to disable and 1 to enable.
   End Sub
   

   
   Now we can easily provide our user the facility to use the Calendar without running into trouble and attend to his/her distress calls every now and then. All we have to do is to introduce the following lines of code into the Form Module where we have installed our Calendar control:
   
   

   
   Private Sub Form_Load()
    EnableDisable 1
   End Sub
   
   Private Sub Form_UnLoad()
    EnableDisable 0
   End Sub
   

   
   
3. All the fields on a Form don’t need the use of the Calendar. Since, our Calendar is any time anywhere type implementation we need to limit its activity where we actually need it. As I have mentioned earlier we need little more code to do this. Before we go into the code let us list out the steps that we need to take, to write the code when the user clicks on the Calendar Button.




• We must see whether this control is an Unbound Text Control or a Bound Text Control linked to a table or Query field.


• If it is an Unbound Text Control but it is intended to accept a date then we must identify the control by checking its Format Property or Input Mask Property, which of course we will be designing and setting it up for the convenience of the User and for us too.


• If it is a Bound Text Control and the Format or Input Mask Property is already set with appropriate string values it will make the task easier.



Otherwise we have to take the Control Source and Record Source Property Values and go deep into the Control Source Table or Query and pull out the Data Field and check its type and determine whether it is a Date type field or not. Any way, we must be prepared for both situations.

Once we got the result of the above validation we can determine to show or not to show the Calendar. Copy the following Code into the same Global Module that you have copied the Calendar() Program:



Public Function Check4Date() As Boolean
Dim ctl As Control, frm As Form
Dim RecSource As String, ctlSource As String
Dim dtFormat As String, ctlType As Integer
Dim fldType As Integer, fldformat As String
Dim ctlName As String, statuscode As Integer
Dim tblDef As TableDef, qryDef As QueryDef
Dim tblDefFlag As Boolean, cdb As Database

On Error GoTo Check4Date_Err

dtFormat = "dd/mm/yyyy"

Set frm = Screen.ActiveForm
Set ctl = Screen.ActiveControl

ctlType = ctl.ControlType
If ctlType <> 109 Then 
'not a Textbox Control, terminate the function
   Check4Date = False
Exit Function
End If

ctlSource = ctl.ControlSource
If Len(ctlSource) = 0 Then
    statuscode = 1 ' unbound textbox
Else
    statuscode = 2 ' bound textbox
End If

fldformat = ctl.Format

'bound/unbound if format is date then valid
If fldformat = dtFormat Then
   Check4Date = True
   Exit Function
End If

If statuscode = 2 Then
    RecSource = frm.RecordSource
    ctlName = ctl.Name

    Set cdb = CurrentDb

'Check it is a Table or not
    tblDefFlag = False
    For Each tblDef In cdb.TableDefs
        If tblDef.Name = RecSource Then
            tblDefFlag = True
            Exit For
        End If
       Next

'if it is table then check the field type
    If tblDefFlag Then
        Set tblDef = cdb.TableDefs(RecSource)
        fldType = tblDef.Fields(ctlName).Type
        If fldType = 8 Then
            Check4Date = True
            Exit Function
        End If
    End If

'it is not a table check in Query Definitions
    Set qryDef = cdb.QueryDefs(RecSource)
    fldType = qryDef.Fields(ctlName).Type
        If fldType = 8 Then
            Check4Date = True
            Exit Function
        End If
End If

Check4Date_Exit:
Exit Function

Check4Date_Err:
MsgBox Err.Description, , "Check4Date_Err"
Resume Check4Date_Exit
End Function


Add the following Code snippet at the beginning of the Calendar() Program immediately below the Dimension statements to run the Check4Date() routine and decide whether to show the Calendar or display a message that the active Control doesn’t deserve the use of a Calendar:



Dim fldstatus As Boolean
Fldstatus = false ‘ initialize variable
fldstatus = Check4Date() ‘ check the control type 
If fldstatus = False Then
  MsgBox "Sorry, Not a Date Type Control."
  Exit Function
End If



4. Without going through all the trouble of creating Custom Toolbars, Shortcut Menus, Validation checks etc. you can use the Calendar on specific fields on the Form by doing some extra work on every form that we insert our Calendar Control. Look at the sample Screen given below:

We have created two small Command Buttons one each for Visit Planned Date & Actual Visit date fields and selected its button image ‘Calendar’ from the picture collection of the Command Button Wizard. The command button is resized and placed close to the Field’s right edge. Now we need to run two lines of code on the On Click Event Procedures of both buttons to set their corresponding field active and call the Calendar Program to display the Calendar and insert the date clicked into the active field. In our example, the command buttons were named as cmdCal1 and cmdCal2 and the Click event procedures are given below:

Private Sub cmdCal1_Click()
             Me.PlannedDt.SetFocus
             Calendar
End Sub

Private Sub cmdCal2_Click()
           Me.VisitDt.SetFocus
           Calendar
End Sub

I hope this will clear things up and you will be encouraged to start experimenting some of these methods and if you have different ideas, please share with me.

Labels: msaccess animation

Calendar (the ActiveX Control) is a good piece of object for clicking and inserting a Date into a field instead of typing. There is only one problem, let us say more than one, it occupies lot of space on the form and it comes in the way of designing other elements of the screen as well. If you need to enter date in more than one field, each field requires different Calendar Controls and we may not be able to accommodate all those copies in one place. Finally, a convenient method become totally inconvenient and we may have second thoughts and decide to go by the traditional method: typing everything manually, after all somebody else is going to do that.

We are going to use the Calendar Control and we don’t accept defeats and all those above reasons will not prevent us from using it. The method that we are going to try out here is kind of hard to implement for the first time. But it will be very easy on other Forms in the same Project.

Open your Database with a Form that you have already designed to use or design a new one with at least two date fields on the Form. Let us do the easy part of our project first. Copy and paste the following VB Code into a Global Module and save it:

Option Compare Database
Option Explicit

'Global Declarations
Public Const twips As Long = 1440
Dim mm_actctl As Control

Public Function Calendar()
Dim sngStart As Single, CalCtrl As Control
Dim ctl As Control, frm As Form, t_height As Long
Dim m_left As Long, m_top As Long, i As Double
Dim w As Long, h As Long, y As Double
Dim caltop As Long, calheight As Long
Dim secHeight As Long
Dim frmWidth As Long, t_width As Long

On Error GoTo Calendar_Err

Set ctl = Screen.ActiveControl
Set mm_actctl = ctl
Set CalCtrl = Screen.ActiveForm.Controls("Cal1")
Set frm = Screen.ActiveForm

CalCtrl.Width = 0.1458 * twips ' 0.1458"
CalCtrl.Height = 0.1563 * twips ' 0.1563"
m_left = ctl.Left + ctl.Width
m_top = ctl.Top + ctl.Height
caltop = m_top
calheight = ctl.Height + (15 * twips * 0.106) '0.105"

secHeight = frm.Section(acDetail).Height
frmWidth = frm.Width
t_height = caltop + calheight
t_width = m_left + (15 * twips * 0.17) '0.17"

If t_height > secHeight Then
    m_top = secHeight - (calheight + (0.106 * twips))
End If

If t_width > frmWidth Then
   m_left = frmWidth - (15 * twips * 0.17) ' 0.17"
End If

CalCtrl.Left = m_left
CalCtrl.Top = m_top
CalCtrl.Visible = True

sngStart = Timer
i = 0.05: y = i
Do While Timer < (sngStart + 0.75)

If Timer >= sngStart + y Then
    y = y + i
    w = CalCtrl.Width + (0.17 * twips) ' 0.17"
    CalCtrl.Width = w
    h = CalCtrl.Height + (0.106 * twips) ' 0.105"
    CalCtrl.Height = h
       DoEvents
End If
Loop

Calendar_Exit:
Exit Function

Calendar_Err:
MsgBox Err.Description, , "Calendar"
Resume Calendar_Exit

End Function

‘Insert Date into the active Field

Public Function Cal1Click()
Dim m_cal As Control, m_ctl As Control

On Error GoTo Cal1Click_Err

Set m_cal = Screen.ActiveForm.Controls("Cal1")
mm_actctl.Value = m_cal.Value
m_cal.Width = 0.1458 * twips ' 0.1458"
m_cal.Height = 0.1563 * twips ' 0.1563"
mm_actctl.SetFocus
DoEvents
m_cal.Visible = False

Cal1Click_Exit:
Exit Function

Cal1Click_Err:
MsgBox Err.Description, , "Cal1Click"
Resume Cal1Click_Exit

End Function

NB: While running this code if you end up with some error, please refer my earlier Post Command Button Animation and link the essential Library Files to your Project as explained there.

1. Open the Form in Design View.
2. Select ActiveX Control… from Insert Menu.
3. Scroll through the Displayed List and find Calendar Control as shown in the image below:






4. Select it and click OK. A Calendar Control is inserted into your Form.






5. Drag it anywhere at a convenient place for the time being. We are going to change its properties. Click on it and display its property sheet and change the following property values as shown below:

1. Name = Cal1
2. Visible = False
3. Special Effect = Raised
4. Border Color = 0
5. Back Color = 11139322
6. Month Length = System (Medium) – Access 2003, e.g.: Jul 2007
7. Grid Lines Color = 2147483632
8. Grid Font Color = 10485760
9. Title Font Color = 10485760
10. Width = .1458”
11. Height = .1563”

After changing the width & height properties the Calendar become a small rectangle and you can place it anywhere on the Form at a convenient place.

We need to copy a few more lines of Code in our Form’s Code Module. When the Form is in Design View click the Code toolbar button above or select Code from View menu and paste the following code into the Form Module:

Private Sub Cal1_Click()

Cal1Click

End Sub

Private Sub Detail_Click()

Me.Cal1.Visible = False

End Sub

Private Sub Form_Load()

Me.Cal1.Value = Date

End Sub

1. When you click on a Date on the Calendar it calls the Routine Cal1Click() and inserts the clicked date into the Active Field and the Calendar disappears.

2. You have displayed the Calendar but you don’t want to click on it to insert a date and at this point you want to hide the calendar, then click on an empty area of the detail section of the Form, the Calendar will disappear. This is achieved through the Detail_Click() Routine.

3. When the Form with the Calendar is open the System Date is set as default value for the Calendar with the Form_Load() Event Procedure. The Calendar will always open up with current date.

If you are new to customizing Menus and Toolbars you may be little confused on the next part of this article. Don’t worry, we are going to familiarize something that is already there for us to use and we must know about it sooner or later, earlier the better.

In short, we are going to create a new toolbar button and attach our program, that we have copied into the Global Module, to it. We will place a copy of the toolbar button in a Shortcut Menu as well, for easy running of the Animated Floating Calendar.

Select the following Menu Option:

1. View - > Toolbar - > Customize. Click New… to create a new Toolbar and name it as ToolCal then click OK. A new small empty Toolbar will show up on top.







2. Select the Commands Tab . Click and drag the New… button and place it on the new toolbar.
3. Right-click on the new toolbar button, point the cursor on the ‘Change Button Image’ option on the displayed menu to show up several Button Images. Select one of the Images.




If you prefer to create a button image yourself you can do so by selecting ‘Edit Button Image option from the menu. I have selected the image of a fish, after all it is a Floating Calendar. Right-click the button again to display the menu and type &Calendar in the Name: &Name… control. We can set the button style to Image only, Text only or Image and Text. Default style, which is already selected is just fine for now.



4. Now, we must link our Calendar() program to the toolbar button. Right-Click on the toolbar button and select Properties from the displayed Menu. Type =Calendar() (don’t forget the = sign) in the On Action : text control and click Close.

Our Toolbar Button is ready. We can use this Button to run our Calendar on the Form and we can stop here, if we need to. But we plan to go little further and add a copy of this button in the Form View Control Shortcut Menu so that we can right-click on a Date Field on our target Form and click the button on the Shortcut Menu to display the Calendar. We are trying to make things little easier for the Users. The Calendar is not visible till you click on the button.


5. Select the Toolbars Tab on the Customize Dialogue Control and put tick mark for Shortcut Menus.



Before attempting to copy the button let us locate the target spot, where we are going to place our new button. On the Shortcut Menus Options you will find the heading Form, click on it to display a lengthy list of Submenu groups, among them find the one Named ‘Form View Control’ and click on it. A set of Options like Filter by Selection, Cut, Copy, Paste etc. can be seen. This is the default shortcut menu that we see when we right-click on a Text Control in Form View. Look at the image given below:







The shortcut menus are different when you right-click on an empty area of the Form or on the record selector at the left border etc. So it is important that you make a copy of our new button on this particular Shortcut menu. There are some side effects to this method that we are going to use, like if you click on the new toolbar button when there is a Form open without a Calendar Control on it or the Calendar control's name is not Cal1 etc., the program will show Error Messages. We will look into those things when we discuss about Custom Menu-bars, Tool-bars, Shortcut Menus and how to use them on Forms etc. under a different topic.



6. Now, let us get on with our project. Press and hold Ctrl Key with one hand, click on the new toolbar button and keep the mouse button pressed with the other hand to make a copy of the button (if you don't keep the Ctrl Key pressed the button will be draged out of the Toolbar, rather than making a copy), drag and point on the Form Menu on the Shortcut Menubar to display the Submenu groups and drag towards the Form View Control Sub-Menu and point on it to display the Shortcut Menu options. Drop the button at the left border of the menu at a convenient location where other icons are appearing. Click the Close button to come out of the customization process.
   

We are ready to try out the Animated Floating Calendar. Open your Form in Form View and right-click on a Date Field. The new button on the Shortcut Menu with the Fish Icon and Calendar Caption should be visible as shown below:

Click the Calendar Button on the shortcut menu. The Calendar control will slowly unfold with yellow background. It will appear to the right and below the Date Field that you have right-clicked. Not necessary that it should be a Date Field, you can right-click on any field and run the Calendar.

If we need to restrict inserting date into date type Fields only from the calendar then we have to validate the control source of the active field from the underlying Query or Table’s Field Type and decide whether to show the calendar or display a warning message and block the calendar from showing up etc. That kind of refining can be done but it takes lot more code to implement. For the time being we will stick to the simple thing and look into those aspects at a later stage.

Click on a Date on the Calendar to insert that date into the field that you have right-clicked. If you don’t want to insert a date and put away the Calendar instead, as I mentioned above, click on an empty area on the detail section of the Form, the Calendar will disappear.

Normally the Calendar will appear to the right and bottom edge of the control that you have right-clicked. This may change if the Date Field is too close to the bottom or right edge of the Form. Then the Calendar will appear within the Form itself in an appropriate place nearest to the field, to fit its size. This may overlap the field that you have right-clicked. But you can click on the Calendar and insert a date into that field.

NB: The Program may not work correctly if you attempt to use the method on a Sub-form.

>> Startup Screen Design

Labels: msaccess animation

This is an image of a Screen where a Reminder Ticker is active and scrolls with information as a continuous reminder. The Automotive Sales & Service Company enters into Vehicle Service Contracts with Corporate Customers for various periods and maintains the data in an MS-Access Database. Every month a few Vehicle Contracts are due for renewal and the Staff concerned are suppose to contact the Customers and check whether they would like to renew their Maintenance Contract with the Company. The Reminder Ticker displays the Customer Code, Vehicle Model, Chassis Number, Vehicle Description and Expiry Date (not yet moved into the visible area of the Ticker).

The Input Data for the Ticker is extracted from the Contract Table based on the Expiry Date falling within the current month, with the help of a Query. Customer Code, Vehicle Model Number, Chassis Number, Vehicle Description & Expiry Date Values of each contract record is concatenated into a Variant Variable (String Variable may limit the length of the String into 255 characters) and used for the Ticker with the help of Timer.

The VB Code is given below:

Option Compare Database
Option Explicit
'Global Declaration
Dim strTxt

Private Sub Form_Open(Cancel As Integer)
Dim db As Database, rst As Recordset
Dim rstcount As Integer, currMonth As Integer, marqMonth

On Error GoTo Form_Open_Err

currMonth = Month(Date)

' Expiry_Marque is a parameter Table which holds 
' the Start-Date & End-Date of Current Month and uses to pick
' the Contract Expiry Cases falls within this period.

marqMonth = Month(DLookup("ExpDateTo", "Expiry_Marque"))

If currMonth <> marqMonth Then

' when the month is changed the parameter table is 
' updated with changed period.
' i.e. Start-Date and End-Date of the Current Month

DoCmd.SetWarnings False
DoCmd.OpenQuery "Expiry_Marque_Updt", acViewNormal
DoCmd.SetWarnings True
End If

'checks whether any contract expiry cases are there
'during the month.

rstcount = dCount("*", "Expiry_MarqueQ")

If Nz(rstcount, 0) = 0 Then

strTxt = String(60, " ") & "*" NO CONTRACT EXPIRY CASES FOR " 
strTxt = strTxt & Format(Date, "mmmm yyyy") & " **"

GoTo Form_Open_Exit
End If

' builds the String strTxt with ticker data.

Set db = CurrentDb
Set rst = db.OpenRecordset("Expiry_MarqueQ", dbOpenDynaset)

strTxt = String(60, " ") & "Expiry Cases: "

Do While Not rst.EOF

With rst
  strTxt = strTxt & " ** {" & rst.AbsolutePosition + 1 & "}. CUST: ["
  strTxt = strTxt & ![CUST_COD] & "] MODEL :[" & ![MODL_COD]
  strTxt = strTxt & "]  CHAS :[" & ![CHASSIS] & "](" & ![DESC]
  strTxt = strTxt & ") EXP.: " & ![EXP_DATE]
End With

rst.MoveNext

Loop

rst.Close

'A Text Box on the Form is set with the Total Number
'of Contracts getting expired.

Me![mVehl] = rstcount & " Vehicles."

' the Timer is invoked and the time to refresh 
' the control is set with quarter of a
' second. This value may be modified.

Me.TimerInterval = 250
Set rst = Nothing
Set db = Nothing

Form_Open_Exit:
Exit Sub

Form_Open_Err:
MsgBox Err.Description, ,"Form_Open"
Resume Form_Open_Exit
End Sub


Private Sub Form_Timer()
Dim x

On Error GoTo Form_Timer_Error

x = Left(strTxt, 1)

strTxt = Right(strTxt, Len(strTxt) - 1)

strTxt = strTxt & x

' Create a Label with the Name lblmarq 
' on your Form to scroll the values
' The value 200 used in the Left Function may be
' modified based on the length of the
' Label. Format the Label with a fixed width font
' like Courier New so that you can correctly determine
' how many characters can be displayed on the legth
' of the Label at one time and change the value accordingly.

lblmarq.Caption = Left(strTxt, 200)

Form_Timer_Exit:
Exit Sub

Form_Timer_Error:
MsgBox Err.Description, , "Form_Timer_Error"
Resume Form_Timer_Exit

End Sub

When the Form become inactive after opening other Forms, deactivate the Ticker. When nobody is watching no point in running the Program. When the Form become active again then re-activate the ticker. To do this add the following Code into the Form Module:

Private Sub Form_Deactivate()
    Me.TimerInterval = 0
End Sub

Private Sub Form_Activate()
Me.TimerInterval = 250
End Sub

File Browser in Access
MsgBox with Office Assistant
Create 3D Headings on Forms/Reports

Labels: msaccess animation

The left side message box is the default style of MS-Access and the right-side one is with the use of Office Assistant. The Program uses the default Office Assistant Setting for Message Boxes. Here the Office Cat is the default setting, hence it is appearing with the Message Box. By adding a few Functions in a Global Module of your MS-Access Project will help you to make use this feature wherever you need them. Some of the very commonly used Functions are created separately for ease of use limiting the maximum Number of Parameters needed for the Functions to two and 2^nd Parameter for Title is Optional and can be omitted. First Parameter for Message Text and the second one for Title. Button Type and Icon Type is already added to the Function. The following Functions are available and their usage Syntax is as follows:

MsgOK("Message Text","Title") - MessageBox with only OK Button

MsgYN("Message Text","Title") - MessageBox with Yes & No Buttons. Returned Value is vbYes or vbNo

MsgOKCL("Message Text","Title") - MessageBox with OK, Cancel Buttons. Returned Value is vbOK or vbCancel