REMINDER TICKER FORM

Introduction

This is an image of the Main Switchboard Screen, where a Reminder Ticker is actively scrolling a continuous stream of information. The Automotive Sales & Service Company manages vehicle service contracts with corporate customers for various durations, storing all related data in an MS Access database.

Each month, some of these contracts are due for renewal. The responsible staff must then contact the respective customers to confirm whether they wish to renew their maintenance contracts with the company.

The Reminder Ticker displays key details, such as the Customer Code, Vehicle Model, Chassis Number, Vehicle Description, and Expiry Date, with the latter appearing as the ticker scrolls into view.

The input data for the Reminder Ticker is retrieved from the Vehicle Maintenance Contract table using a query that filters records with expiry dates falling within the current month. For each contract, the Customer Code, Vehicle Model Number, Chassis Number, Vehicle Description, and Expiry Date are concatenated into a Variant variable (as a String variable may limit the length to 255 characters). This combined text is then displayed in the ticker using a Timer control.

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
  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 

Ticker Active/Inactive States

The code starts executing automatically when the form opens and continues to run until the form is closed.

When the form becomes inactive—such as when another form is opened over it—the ticker is automatically deactivated. After all, there's no point in keeping the program running when no one is watching. When the form becomes active again, the ticker resumes automatically. To add this behavior to the reminder ticker, copy and paste the following code into the form's module:

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

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

Download

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

Download Demo Database

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

Read More

FILE BROWSER IN MSACCESS

Introduction

SEARCHING FOR OTHER FILES FROM MSACCESS

In Microsoft Access, we can locate and open database files by choosing File → Open from the main menu. But what if we want to search for and open other types of files from the disk, such as documents, images, or PDFs?

In that case, we can use file dialog controls to browse and select files. Once a file is selected, we can perform several actions in Access:

• Create a hyperlink to the file and store it in a table field.

• Use the FileCopy Function to copy the file to a different location.

• Or store the file path for later retrieval or reference.

This makes it easy to manage and integrate external files into your Access applications.

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’s design a simple form for our project that includes a Common Dialog Control, a Textbox, and a Command Button, along with a few lines of VBA code to make it functional. The layout of the form will resemble the sample image shown below.

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

The rectangular object on the left side is the Common Dialog control, inserted from the ActiveX Controls group. When the user clicks the "Browse..." button, a file dialog box—similar to the one shown in 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’s time to bring in the star of our design—the Microsoft Common Dialog Control. To add it to your form, follow the steps below:
   
   • Select ActiveX Control from the Insert Menu

   • A list of ActiveX Controls will appear. Scroll down, select Microsoft Common Dialog Control, and click OK.
     If everything goes smoothly, a square-shaped control will appear on your form.

     However, if your MS Office installation is incomplete or not properly configured, you might encounter an error message like:
     "This ActiveX DLL is not registered. Please reinstall 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, overwriting 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  
  cmdBrowse_Click_Exit:
  Exit Sub
  cmdBrowse_Click_Err:
  MsgBox Err.Description, , "cmdBrowse_Click"
  Resume cmdBrowse_Click_Exit
End Sub 

Test Run

Open the form in Form View and click the Browse button. The file browsing dialog box, shown earlier on this page, will appear.
Select any file from your system and click Open. The full file path of the selected file will then be inserted into the TextBox control.

Download

Download Demo FileBrowser.zip

• Command Button Animation
• MsgBox with Office Assistant
• Reminder Ticker Control

Read More