Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.



This is an image of the Main Switchboard Screen, where a Reminder Ticker is active and scrolls with a continuous stream of information. 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 have to contact those Customers and check whether they would like to renew their Maintenance Contract with the Company or not.

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 Vehicle Maintenance 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 to 255 characters) and used for the Ticker with the help of Timer.

The VBA Code

The VB code that does this trick 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
'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  
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)  

Exit Sub  
MsgBox Err.Description, , "Form_Timer_Error" 
Resume Form_Timer_Exit  
End Sub 

Ticker Active/Inactive States

The code starts running immediately after opening the form and runs continuously, till you close the form.

When the Form becomes inactive, after you open some other Form over it, the Ticker is deactivated. If nobody is watching there is no point in keeping running the Program. When the Form becomes active again the ticker starts running again. To add this capability to the reminder ticker copy and paste the following Code also into the Form Module:

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

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

You can download a demo sample database from the download link given below:

Download Demo Database





We can search for Microsoft Access database files on disk by selecting the File - - > Open option from the main menu.  But, if we want to search, select, and open some other file from the disk then how do we do that?  If we are able to do that then what do we do with those File(s) in Access? Well, we can create a Hyperlink to the file and store it in a data field or use the FileCopy function to make a copy of it into a different location.

Demo Run Preview

Anyway, let us get to work with the first part. But before that, a preview of the Run of our Project is shown below:

Designing a Demo Form

Open a Database from your Computer or from the Network Drive.

Let us design a small Form with the Common Dialog Control, a Textbox, and a Command Button with a few lines of VBA Code for our project.  The design will look like the image given below. 

Tip: You can download a demo database from the bottom of this page.

The rectangular-shaped object on the left side is the Common Dialog control inserted from the ActiveX control group. when the user clicks on the Browse. . . button the dialog box like the first image above opens up.

Now let us get to work.

  1. Open a new Form and Create a Text-Box Control, wide enough to hold the Path and Filename selected from the disk.
    • Change the Caption of the child label to File Path Name.
    • Select the Textbox control, display the property sheet (F4), and change the Name property value to lbldb.
  2. Create a Command Button as shown in the above Design, display its Property Sheet, and change the following property values as shown below:
    • Name = cmdBrowse
    • Caption = Browse. . .
  3. Now it is time to bring in the real hero element of our design: the Microsoft Common Dialog Control, to do that follow the procedure given below:
    • Select ActiveX Control from Insert Menu
    • You will find a List of ActiveX Controls opens up, scroll down and select the Microsoft Common Dialog Control, and Click OK. If you didn't meet any trouble on the way out after clicking the OK button you will find a square-shaped control sitting on your Form. If your MS-Office installation is not properly done it is likely that you may end with a message like 'this ActiveX DLL is not registered, re-install it or something similar.

    Display the Property Sheet of the Common Dialog Control and change the Name property to cmDialog1. You can place it anywhere at your convenience, it will not be visible when you activate your Form.

  4. Click on the Command Button to select it.
  5. Display the Property Sheet (F4).
  6. Click on the On Click Event property and select [Event Procedure] from the drop-down control.
  7. Click on the build (. . .) button to open the VBA Module Window of the form.

The VBA Code

  • Copy the Following Visual Basic Code and paste it, over-writing the existing empty procedure lines, into the Module, and save the Form.
  • Private Sub cmdBrowse_Click()
    Dim VFile As String
    On Error GoTo cmdBrowse_Click_Err
    ChDrive ("C") 
    ChDir ("C:\")
      cmDialog1.Filter = "All Files (*.*)|*.*| _ Text Files (*.txt)|*.txt|Excel WorkBooks (*.xls)|*.xls"  cmDialog1.FilterIndex = 1
      cmDialog1.Action = 1
      If cmDialog1.FileName =  "" Then
      VFile = cmDialog1.FileName 
      Me!lbldb = VFile
      End If  
      Exit Sub
      MsgBox Err.Description, , "cmdBrowse_Click"
      Resume cmdBrowse_Click_Exit
    End Sub 

    Test Run

    Open the Form in normal view and click on the Browse button. The File Browsing Control, that we have seen on the top of this page, will open up. Select a file from any location you like and click Open. The selected file with its complete location address will be inserted into the Text Box control.


    Download Demo FileBrowser.zip





    Post Feed


    Popular Posts

    Blog Archive

    Powered by Blogger.


    Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code