Team LiB
Previous Section Next Section

Using the Form Timer Event

The Timer event lets you set an event to fire at the end of an interval. In your Timer event procedure, you can cause anything to happen that you can program using VBA. The samples in this section introduce you to forms with three applications for this exceedingly useful event. The first is a splash form that demonstrates an application that needs to run the Timer event just once. The second sample highlights how to use the Timer event to refresh a form automatically. This second application repeatedly runs the Timer event, and it reconnects a computer screen with a record source so that the screen always shows the most recent data in the source. The third sample applies the same kind of approach to publishing a table from an Access database to a page at a Web site. By repeatedly publishing the page at regular intervals, the application assures that visitors will obtain recent data. It is interesting that you can accomplish this goal with just a few lines of VBA code—no knowledge of HTML or Active Server Pages (ASP) is required.

Splash Screen Forms

One easy way to get started with forms is by creating a splash screen. A splash screen is a form that appears before another, more interactive form appears. Splash screens often state what an application does or who created it. You can easily control how long this form stays visible using the Timer event. Figure 5-1 shows a sample splash screen from the Chapter05.mdb file, which you can adapt to your own purposes. The Chapter05.mdb file is available with the companion content for this book.

Click To expand
Figure 5.1: A sample splash screen.

Creating a Splash Screen

You can start by creating a tiled background. You do so by setting two form properties while the form is open in Design view. First, you set the form's Picture property (on the Format tab of the property sheet) to the path and filename for the image. You can use a bitmap in .eps, .ico, .dib, .wmf, or .emf format, as well as other graphic file formats. Access can also use any graphic file formats for which you have filters installed. Images that contain gray or other muted colors work best for backgrounds because they make your foreground images and text look more prominent. Second, you change the form's PictureTiling property to Yes (the default is No).

Note 

Access 2003 embeds images in a database by default. This means that the database file grows to accommodate any images that you add to an application. You can choose Linked instead of Embedded as the PictureType property to allow Access to utilize a graphic file outside the database file. If you take this approach, then you must coordinate the location of graphic image files with the database files through the form's Picture property. If you move or alter the graphic image files, the form will not appear as initially designed. The splash screen in Figure 5-1 contains three graphic image files embedded in the database, but these files are also available in the sample materials for this chapter so that you can test the Linked setting for the form's Picture property.

Next, you can add a foreground image by choosing Picture from the Insert menu. You can also add an Image control to the form. Either method opens the Insert Picture dialog box, where you can select an image to add. Clicking OK automatically sets the image's Picture property. Access lets you programmatically set the Picture property with VBA so that you can construct the look of a form dynamically in response to user input (such as a text box entry) or environmental factors (such as a user's security ID). You can position and size the image and use the special effects created by various SizeMode property settings. The property accepts three values: Clip, Stretch, and Zoom.

As you build this sample, there are at least six other form properties that you will want to change from their default settings. Three of these are the Record Selectors, Navigation Buttons, and Dividing Lines properties. By default, these properties are set to Yes, which generally works well for forms that permit users to display and edit data for a form bound to a record source. However, a splash form does not show data. Therefore, changing the three properties from their default setting of Yes to No creates a more attractive form by removing unnecessary design elements. Two additional properties, Scroll Bars and Border Style, affect the look of the form. Change the Scroll Bars setting to Neither, and the Border Style setting to None. You can use drop-down controls on the Format tab of the form's property sheet to adjust five of the six property settings. When you close the form to commit your design changes, assign the name frmSplashTimer (or whatever name you prefer) to the form. This sets the form's Name property, which you can reference programmatically or through the Rename command on the shortcut menu when you right-click the form in the Database window.

You complete the splash screen by adding one or more Label controls. You can use VBA to set properties for the Label controls at run time. This lets you dynamically format a splash screen's text. You can set a splash screen to open automatically by choosing Startup from the Tools menu and selecting the splash screen's form name in the Display Form/Page drop-down list in the Startup dialog box. You can also hide the Database window when an application opens by deselecting the Display Database Window check box in the Startup dialog box. Click OK to save your choices.

Controlling Display Duration

The following pair of event procedures displays a splash screen for 10 seconds. (To get to the VBA behind a form in Design view, right-click on the form, choose Build Event from the shortcut menu, and then select Code Builder in the Choose Builder dialog box and click OK.) The Form_Open event procedure sets the form's TimerInterval property value to 10,000. (This value is equal to 10 seconds; the TimerInterval property is in milliseconds.) Notice that the Form_Open event procedure uses the Me keyword to denote the form. You could replace the first instance of "frmSplashTimer" with Me.Name in the Form_Timer event procedure; the Me naming convention is more robust because it lets you change a form's name without revising the code. Replacing the second instance of "frmSplashTimer" is also possible, but you must use a slightly different technique because the form to which Me refers is closed when the procedure reaches the second instance.

Because of the settings made in the Startup dialog box, Access opens the form when the database is opened. As the form opens, Access fires the Open event, which causes the Form_Open procedure to run. The Form_Open procedure sets a timer interval that causes Access to fire the Timer event when the interval expires. When the Timer event fires, Access calls the Form_Timer event procedure.

Private Sub Form_Open(Cancel As Integer)

'When form opens, wait 10 seconds before
'firing the Timer event
Me.TimerInterval = 10000
   
End Sub
   
Private Sub Form_Timer()
#Const OpenDBWindow = True
   
'When the timer event happens, close the form and
'then expose the Database window or
'a switchboard form
DoCmd.Close acForm, "frmSplashTimer"
   
#If OpenDBWindow = True Then
    DoCmd.SelectObject acForm, "frmSplashTimer", _
        True
#Else
    DoCmd.OpenForm "frmHyperlinkSwitchboard"
#End If
   
End Sub

The Form_Timer event procedure closes the form and opens either the Database window or a sample switchboard form. A conditional compilation constant and an #If…Then…#Else statement control which option the procedure selects. Use the OpenForm method of the DoCmd object to open a form. This method can take several arguments. However, if you simply want to show a form, just name the form using a string variable or string constant. Exposing a hidden Database window is a less obvious process. To accomplish this, you can select an object from the Database window and elect to show the selected object in the Database window. The DoCmd object's SelectObject method can accomplish this. This method takes three arguments for this purpose, listed here with the values used in our sample:

  • ObjectType  The intrinsic constant acForm denotes a form object.

  • ObjectName  Using the name of the splash form, frmSplashTimer, ensures that the object exists in the database.

  • InDatabaseWindow  Using True causes Access to select the object in the Database window.

Automatically Requerying Forms

While the splash screen application for the Timer event is interesting, it falls more in the category of "look and feel" rather than data delivery. However, automatically refreshing a form or report at intervals can ensure a timely display even with inexperienced operators. Because the Timer event recurs at regular intervals—namely, the interval specified by the TimerInterval property—you can force a form or report to requery the data source at the end of each interval. The TimerInterval property can assume values from 0 milliseconds through 2,147,483,647 milliseconds (nearly 25 days). Because computers with sufficient power to run Access are inexpensive, organizations can afford to deploy them in many situations that demand constantly fresh data—for example, manufacturing and health applications.

Figure 5-2 shows two forms based on the Shippers table from the Northwind database. The table in the bottom window shows the addition of a new record (highlighted). The two forms at the top are the same, except that one has a Timer event procedure that requeries the data behind the form every 10 seconds. Therefore, that form reflects the new record count of 4. Because the form on the left has no Timer event, it reflects the original record count of 3.

Click To expand
Figure 5.2: Two forms that reveal the ability of the Timer event to requery the record source behind a form to show the most recent data automatically.

The code behind the form on the right appears next. Again, the sample uses two procedures. The Open event procedure, shown first, sets the requery interval to 10 seconds. Neither of the two event procedures in this sample close or change the display. Therefore, the Timer event will continue to recur at 10-second intervals until a user closes the form.

The second procedure is a Timer event procedure. It controls the action that occurs when the timer goes off every 10 seconds, which in this case is to requery the form's record source. This requerying allows the form on the right to reflect without manual intervention data changed from other workstations. The procedure starts by saving the primary key for the current record in int1. Then, it turns off screen refreshing by invoking the DoCmd object's Echo method with a setting of False. After that, the procedure requeries the data source. The requery captures new additions or updates and clears away deletions from other workstations, and it moves the current record position back to the first record in the source behind the form. Using the FindRecord method for the DoCmd object with the saved primary key value from before the requery allows the application to restore the initial position. (If the record being viewed was deleted, the current position reverts to the top of the data source.) Setting the focus to the ShipperID field before invoking the FindRecord method allows the method to run without a special argument setting that tells the method to search only the current column.

When using the Echo method, be sure to restore screen updates if you turn them off. (To turn screen updates off, use a value of False with the Echo method.) You can restore screen updates by running the Echo method with a value of True. If you fail to restore screen updates, your application's users will not be able to view any changes to the screen.

Private Sub Form_Open(Cancel As Integer) 

'When form opens, wait 10 seconds before
'firing the Timer event
Me.TimerInterval = 10000
   
End Sub
   
Private Sub Form_Timer()
Dim int1 As Long
   
'Save value of primary key before requerying
int1 = Me.ShipperID
   
'Turn off echo and requery
DoCmd.Echo False
Me.Requery
   
'Move focus to primary key field and move to
'record in field that was current before requerying; 
'turn echo back on
Me.ShipperID.SetFocus
DoCmd.FindRecord int1
DoCmd.Echo True
   
End Sub 

Automatically Publishing a Datasheet to a Web Site

Web applications are a convenient way to present information such as the contents of an Access database. A browser is all a client needs to view content at a Web site. Making a Web page dynamic so that it shows the most recent information can involve programming ASP, however. With ASP, developers can build live connections to their databases and guarantee fresh data, but this approach demands skills that are unfamiliar to many Access developers. The OutputTo method for the DoCmd object in combination with an Access form's Timer event offers more familiar tools for generating dynamic Web content based on an Access database.

The frmPublishShippersTable form in the Chapter05.mdb file illustrates an approach to this task, as well as some general form design issues. Figure 5-3 presents the form in both Design view (top) and Form view (bottom). Notice that in the Design view the form contains a single label control with a Caption property setting of Anything. In addition, the label control is small and poorly positioned on the page. In Form view, the label grows to accommodate a label that describes the purpose of the form. Since the Design view shows the form and control settings at design time, a program must perform the transformation in the form's appearance at run time. By programmatically assigning values to the label control as well as to other aspects of the form, you create a VBA script that is easy to read and edit. While you lose the convenience of graphical form design, you gain the ability to easily modify a design by modifying a program. Even more importantly, you can copy the code for a control or other aspect of a form's design to another form in the current application or even to another application.

Click To expand
Figure 5.3: The frmPublishShippersTable form in Design view (top) and Form view (bottom).

The code behind frmPublishShjppersTable consists of two event procedures. The Form_Load event procedure performs two primary tasks. First, it assigns a value of 30 seconds to the form's TimerInterval property. Next, the Form_Load event procedure formats the form and its label control. You can perform these assignments using the form's graphical design interface. However, if you have ever found yourself furiously searching through a property sheet to find the property that you want to manipulate, this approach might be worth the effort.

Every 30 seconds, the Form_Timer event procedure runs. This procedure uses the OutputTo method for the DoCmd object to post the Shippers table to the pma11 Web site on the local Web server. The table's contents at the Web site exist in a file named ShippersTable.htm. If the file exists at the Web site already, the OutputTo method copies over it. Users can open this page in any standard browser that can read plain HTML. The content of the Web page will be no more than 30 seconds behind any actual updates to the Shippers table in the Northwind database file. This approach demonstrates a fast and easy way to publish dynamic content to a Web site that takes advantage of the Timer event for an Access form. You also can use the OutputTo method to post the data values of other Access objects, such as queries, forms, and reports.

Note 

The pma11 Web site was introduced in the "Printing Web Folder Contents" section of Chapter 2. Chapter 14 demonstrates how to create a Web site like pma11 with FrontPage 2003.

Private Sub Form_Load()
Const conTextAlignCenter = 2
Const conScrollBarsNeither = 0
   
'Set timer interval to 30 seconds
Me.TimerInterval = 30000
   
'Format form
Me.RecordSelectors = False
Me.NavigationButtons = False
Me.DividingLines = False
Me.ScrollBars = conScrollBarsNeither
   
'Format label control
'The Caption property takes a string expression;
'all other property settings are in twips,
'except for FontSize, which is in points
Me.Label0.Caption = _
    "Leave me open to re-publish the Shippers table every" & _
    CStr(CLng(Me.TimerInterval) / 1000) & " seconds."
Me.Label0.Top = Me.InsideHeight * 0.25
Me.Label0.Left = 360
Me.Label0.Width = Me.Width
Me.Label0.Height = 1080
Me.Label0.TextAlign = conTextAlignCenter
Me.Label0.FontSize = 18
   
End Sub
   
Private Sub Form_Timer()
   
'Publish the Shippers table
DoCmd.OutputTo acOutputTable, "Shippers", acFormatHTML, _
    "C:\inetpub\wwwroot\pma11\ShippersTable.htm", False
        
'Print time table was published
Debug.Print "Refreshed ShippersTable.htm at "; Now()
   
End Sub 

Team LiB
Previous Section Next Section