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

SEARCHING FOR OTHER FILES FROM MSACCESS
How to Browse for Folders or Files from MS-Access? If we can, then what do we do with the File(s) we bring into Access. Well, you can copy it from one place to the other for an example. Anyway let us get to work with the first part. But before that, a preview of the Run of our whole Project is given below and we will be working towards that with a small Form and with an On-Click Event Procedure:

This is the same Dialog Control that Opens up when you select the File(Open Menu Item. But here we got it when you click the Browse… Button given next to the Text-Box Control. When it comes up we are free to move around and select what we want and when you do the selected File will be inserted into the Text-Box control to the left of the Browse... Button.

Now let us get to work. Design a Small (or Big as you wish) Form as shown below and the Controls on the Form are already familiar to you as explained above except one. But I will list all of them below for info.

1. Open a new Form and Create a Text-Box Control.

i) Change the Caption of the child-label to File Path Name:
ii). Display the property sheet of the Text-Box control and change the Name property to lbldb.


2. Create a Command Button as shown in the above Design, display its Property Sheet and change the properties as given below:



i) Name = cmdBrowse
ii) 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 met any trouble on the way you will find a square shaped control sitting on your design surface. 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’ etc.

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.

• Copy the Following Visual Basic Code and paste it into our Form’s Code Module and complete the design by saving 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

Now activate the Form and click on the Browse Button. The File Browsing Control that we have seen at the beginning should open up. Select a file from any location and click Open. The selected file with its complete location address is inserted into the Text Box control.

Command Button Animation
MsgBox with Office Assistant
Reminder Ticker Control

Labels: msaccess forms