Chapter 8: Using Code to Add Advanced Functionality to Forms (1 of 2)
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
This article is an excerpt from Expert Access 2007 Programming by Rob Cooper and Michael Tucker from Wrox(ISBN 978-0-470-17402-9, copyright Wrox 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.
Next Part: Chapter 8:Using Code to Add Advanced Functionality to Forms (2 of 2)
Aside from the data itself, forms are another key part of an application. Many Microsoft Access developers tend to prevent users interacting with the tables directly, which makes forms the primary mechanism for presentation for data in the application. Unlike reports, forms also provide users with the ability to update data, making them the primary means for interacting with the data as well.
Because forms represent the user interface of the application, what you can do with them is virtually unlimited (or at least within the amount of hours you can bill if you're a consultant). Regardless of the layout and presentation, however, the ultimate goal for creating forms is to create something for the user that is both useful and enjoyable. Because the requirements for users and the application itself can greatly vary from one application to the next, this is an open statement. Keeping issues of style aside, however, there are several form-related features found throughout database applications and this chapter focuses on those. More specifically, in this chapter you will:
Work with events on forms to perform tasks such as validating data and moving a borderless form
Work with events on controls to perform tasks such as working with list boxes to present usable interfaces, creating custom progress bars, and to validate data
Learn how to create common types of forms such as menus for navigation, splash screens, and dashboards
See different ways you can create navigation experiences for your users
Important |
---|
The code marked as Visual Basic in this chapter is written in Visual Basic for Applications (VBA). |
Contents
Working with Form Events
Working with Controls
Working with Form Events
Events are one of the key components of Windows programming, and Access forms take full advantage of them. Access forms provide events that enable you, as the developer, to manipulate both the data and the user interface (UI). This section discusses some of the different events you can use at the form level in Access 2007.
How to Determine When Data Is Added
The two events on a form that are related to inserting data are BeforeInsert and AfterInsert. The BeforeInsert event fires when the user types the first character in a new record, and the AfterInsert event fires after a new record has been added to the form. As with the other Before events in Access, the BeforeInsert event handler includes a Cancel argument to cancel the event if needed.
Let's say that you are creating a form that tracks orders and to receive credit for placing the order, you want to know who placed the order. The BeforeInsert event can be used to fill a control with the logon name of the user as shown here.
(Visual Basic for Applications)
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.txtPlacedBy = Environ(“USERNAME”)
End Sub
The AfterInsert event can be used to take an action after a new record has been added. For example, let's say that you sell books online and want to send an e-mail to customers who place orders with you as confirmation. Here's how you could do this using the AfterInsert event.
(Visual Basic for Applications)
Private Sub Form_AfterInsert()
' send mail after an order is placed
Dim strTo As String
Dim strSubject As String
Dim strBody As String
strTo = Me.EmailAddress
strSubject = “Order Confirmation - Order Number: “ & Me.[Order ID]
strBody = “Thank you for your order!%0a%0d” & _
“Order Date: “ & Me.[Order Date] & “%0a%0d” & _
“Shipping Date: “ & Me.[Shipped Date] & “%0a%0d” & _
“Ship To: “ & “%0a%0d” & _
Me.[Ship Name] & “%0a%0d” & _
Me.[Ship Address] & “%0a%0d” & _
Me.[Ship City] & “, “ & Me.[Ship State/Province] & “ “ & _
Me.[Ship ZIP/Postal Code]
SendEmail strTo, strSubject, strBody
End Sub
We're using the SendEmail routine defined in Chapter 2 to create the e-mail. Here is that code as a reminder.
(Visual Basic for Applications)
Private Declare Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” ( _
ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Const SW_SHOW As Long = 5
Sub SendEmail(strTo As String, strSubject As String, strBody As String)
Dim rc As Long
Dim strFile As String
' build the lpFile argument
strFile = “mailto:” & strTo
strFile = strFile & “?subject=” & strSubject
strFile = strFile & “&body=” & strBody
rc = ShellExecute(0, “open”, strFile, “”, “”, SW_SHOW)
End Sub
How to Determine When Data Is Changed
The AfterUpdate event of a form fires when the value of any control in a record is changed. There are many scenarios in which you might use this event. Here are some of them:
Creating a change history of data in a table
Updating some other information on screen such as status or calculations
Showing or hiding controls
Synchronizing record values between databases
Synchronizing records in a subform
Writing the results of a query to a file
Let's say that by default you provide a read-only view of data in a form by setting the AllowAdditions and AllowEdits properties of the form to False. On the form, you might include a button that sets these properties to True to allow users to edit a record. Use the AfterUpdate event of the form as follows to lock the form after the user commits a change to the data.
(Visual Basic for Applications)
Private Sub Form_AfterUpdate():
Me.AllowEdits = False
Me.AllowAdditions = True
End Sub
How to Determine When Data Is Deleted
Access fires one of three different events when records are deleted. The Delete event fires for each record that is being deleted. The BeforeDelConfirm event fires when Access is about to delete records and prompts you to do so. And the AfterDelConfirm event fires after the records have been deleted (or the deletion was cancelled.
Of the three, the Delete event is perhaps the most useful. This event includes a Cancel argument so that it can be canceled based on some criteria. In addition, because it fires for each record that is being deleted, you can also use this event to mark records for deletion instead of actually deleting them. Many types of applications have requirements about how data is handled and deletions are an important aspect of such applications. Say that you have a table with a Yes/No field called MarkedForDeletion. The following code in the Delete event of a form shows you how to update this field and cancel the actual deletion.
(Visual Basic for Applications)
Private Sub Form_Delete(Cancel As Integer)
' mark records for deletion
Dim stSQL As String
stSQL = “UPDATE tblCustomersDeleteEvents SET MarkedForDeletion=True “ & _
“WHERE ID=” & Me.ID
' run the sql
CurrentDb.Execute stSQL
' cancel the delete
Cancel = True
' refresh
Me.Refresh
End Sub
Validating Form Data
One of the more important tasks you're likely to do in a form is to validate data. For this you need the BeforeUpdate event of the form. If you want to validate data at the control level use the BeforeUpdate event of the control instead.
Because validation may fail for multiple controls when using the BeforeUpdate event of the form, it might be helpful to notify the user of all places where validation has failed. This prevents them from seeing an error message on individual controls until validation succeeds. As with most things in Access, there are a number of ways you can do this. We like to give visual cues for the controls where validation has failed rather than a list in a message box so that the user doesn't have to write down the controls. We sometimes include error text in the control itself or in another text box that displays the errors.
Using our examples of Orders from earlier, create a new form based on the Orders table in the Northwind 2007 sample database. In order for an order entry to be valid in this form, it must meet the following criteria:
The order date cannot be in the past.
The shipped date must be greater than or equal to the order date.
When the payment type is specified, the paid date must be specified.
When the paid date is specified, the payment type must be specified.
To validate these criteria, use a function called IsValidData, as shown in the code that follows. This function returns a Boolean value that determines whether the data on the form is valid. You start out by assuming that all data is valid and initialize a flag to True. When you hit an invalid case, set it to False.
(Visual Basic for Applications)
Private Function IsValidData() As Boolean
Dim fValid As Boolean
Dim iRule As Integer
' assume all is good
fValid = True
' contains the validation data for the form
ResetControlFormatting
' if the payment type has been set, make sure the paid date is set
If (Not IsNull(Me![Payment Type]) And IsNull(Me![Paid Date])) Then
Me.[Paid Date].BackColor = vbYellow
fValid = False
End If
' if the paid date is set, make sure the payment type is set
If (Not IsNull(Me![Paid Date]) And IsNull(Me![Payment Type])) Then
Me.[Payment Type].BackColor = vbYellow
fValid = False
End If
' make sure shipped date is > order date
If ([Shipped Date] < [Order Date]) Then
Me.[Shipped Date].BackColor = vbYellow
fValid = False
End If
' make sure order date is >= today's date
If ([Order Date] < Date) Then
Me.[Order Date].BackColor = vbYellow
fValid = False
End If
' return
IsValidData = fValid
End Function
If you encounter invalid criteria, set the BackColor property of the control to yellow using the vbYellow constant. Because controls may have this property set before validation, you need some way to reset the back color. Add the following routine to the form to reset this property for combo boxes and text boxes.
(Visual Basic for Applications)
Private Sub ResetControlFormatting()
Dim c As Control
' reset control backcolor
For Each c In Me.Controls
If (c.ControlType = acTextBox Or c.ControlType = acComboBox) Then
c.BackColor = vbWhite
End If
Next
End Sub
Now, to prevent an update to the database with invalid data, add the following code to the BeforeUpdate event of the form. This code sets the Cancel parameter value of the event handler based on the return value of the IsValidData function.
(Visual Basic for Applications)
Private Sub Form_BeforeUpdate(Cancel As Integer)
' validate data in the form
Cancel = Not IsValidData()
End Sub
There may still be controls that have the yellow back color set if a user presses the escape key to undo their changes. Add the following code to the Undo event of the form to reset the BackColor property.
(Visual Basic for Applications)
Private Sub Form_Undo(Cancel As Integer)
ResetControlFormatting
End Sub
The Undo event fires when changes are undone on the form.
Suppressing Access Error Messages
When you're working with data in a form, several data-related errors can occur. For example, errors can occur if the user enters a duplicate primary key, or violates a validation rule, or hits a write conflict. An error may also occur if the user tries to delete records in a parent table and records cannot be deleted from a child table because cascade delete is not enforced. In many cases, the error messages provided by Access are long and difficult to read. Use the Error event of the form to provide your own error message and take action based on a particular error, such as setting focus back to a control.
Using the Northwind 2007 sample database, create a new form based on the Customers table. This table is related to the Orders table and the cascade delete option is not set for the relationship. If there are related records in the foreign table, you get an error if you try to delete records in the parent table. The following code shows you how to use the Error event of the form to provide your own error message for this scenario.
(Visual Basic for Applications)
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const ERR_RELATED_RECORDS As Long = 3200 ' Cannot delete related records
Const ERR_PRIMARYKEY As Long = 3022 ' Primary key violation
Const ERR_VALIDATION_RULE As Long = 3317 ' Validation rule violation
Const ERR_INVALID_INPUTMASK As Long = 2113 ' Invalid entry for input mask
Dim stMsg As String
Dim fHandled As Boolean
' assume we're handling the error
fHandled = True
Select Case DataErr
Case ERR_INVALID_INPUTMASK
stMsg = “Invalid input mask”
Case ERR_PRIMARYKEY
stMsg = “You have entered a duplicate primary key”
' put focus back in the field
Me.ID.SetFocus
Case ERR_RELATED_RECORDS
stMsg = “The company [“ & Me.Company & “] has related orders. “ & _
“Please delete these orders prior to deleting the company.”
Case ERR_VALIDATION_RULE
stMsg = “Invalid data has been entered”
Case Else
' unknown - let Access handle it
fHandled = False
End Select
' hide Access' error message and show our own
If (fHandled) Then
Response = acDataErrContinue
MsgBox stMsg, vbExclamation
End If
End Sub
Notice that we're handling several errors here as defined by the constants in the code. We're also using a flag called fHandled to indicate whether we are handling the error or if Access should handle it. When we handle a given error, we've set the Response argument of the event to acDataErrContinue. This tells Access not to display its error message.
How to Determine If a Modifier Key Is Pressed
There might be times when you want to process keystrokes on a form. For example, you might want to provide custom navigation using arrow keys or handle keystrokes that the autokeys macro does not handle. Because the autokeys macro enables you to specify modifier keys (Alt, Shift, and Control), you need some way to do this with a form as well.
Forms include an event called KeyDown that includes an argument called Shift. This argument is a bitmask that includes the different modifier keys. Because this is a bitmask, the following code tests for the different modifiers.
(Visual Basic for Applications)
fIsShiftSet = ((Shift And acShiftMask) = acShiftMask)
fIsAltSet = ((Shift And acAltMask) = acAltMask)
fIsCtrlSet = ((Shift And acCtrlMask) = acCtrlMask)
Let's say that you want to enhance the selection semantics for a form in datasheet view to make it easier to copy data to the clipboard. The semantics to add are: Shift+Ctrl+Down Arrow to select an entire column, Shift+Right Arrow to select a row, Ctrl+Right Arrow to move to the last field, and Ctrl+Left Arrow to move to the first field. Add the following code to the KeyDown event of a form in datasheet view to enable these semantics.
(Visual Basic for Applications)
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If (KeyCode = vbKeyRight And ((Shift And acShiftMask) = acShiftMask)) Then
' select an entire row: Shift+Right
Me.SelLeft = 1
Me.SelWidth = Me.Recordset.Fields.Count ' # of fields in the datasheet
ElseIf (KeyCode = vbKeyDown And ((Shift And acShiftMask) = acShiftMask) And _
((Shift And acCtrlMask) = acCtrlMask)) Then
' select an entire column: Shift+Control+Down
Me.SelTop = 1
Me.SelHeight = Me.Recordset.RecordCount ' Records in the datasheet
ElseIf (KeyCode = vbKeyRight And ((Shift And acCtrlMask) = acCtrlMask)) Then
' move to the last field: Control+Right
Me.SelLeft = 10
ElseIf (KeyCode = vbKeyLeft And ((Shift And acCtrlMask) = acCtrlMask)) Then
' move to the first field: Control+Left
Me.SelLeft = 1
End If
End Sub
Important |
---|
Be sure to set the KeyPreview property of the form to Yes or this event will not fire. |
Windows defines key codes for a given keyboard using what is called a virtual key code. These codes are defined in VBA in the KeyCodeConstants module. This module in VBA defines the vbKeyDown and vbKeyRight constants in the previous code.
The Difference Between KeyDown and KeyPress
You've probably noticed that there are two events in Access that appear to be similar in nature: KeyDown and KeyPress. While it's true that both events fire when the user presses a key on the keyboard, the similarity ends there. The KeyPress event only responds when ASCII keys are pressed on the keyboard, hence the name of the argument for its event handler: KeyAscii. The KeyDown event on the other hand is considerably more granular — it fires for any key pressed on the keyboard, including control keys, such as the arrow keys.
If you need to handle arrow keys or other control keys, use the KeyDown event. If you need to determine whether a modifier is pressed, use the KeyDown event. If you only need ASCII keys, use the KeyPress event.
The other difference between these two events is that the KeyDown event also enables you to suppress keystrokes by setting the KeyCode argument to 0.
Periodic Backups Using the Timer Event
Here's a little confession. When we first discovered the Timer event of a form, we just used it to provide cute little animations and to flash labels on the form. While we still use it to provide animations (more for progress bars these days), we've since discovered that it's far more useful than we originally realized.
It goes without saying that backing up data is pretty important. There may even be requirements for backing up data, depending upon the industry in which you're working. To make sure that data is always backed up, you might consider implementing a backup strategy into your application if you haven't already. The Timer event can be used here to create a backup of the data on a particular form to a file on a periodic basis. This is only a partial solution — it only works when the form is open, but over the course of a day it may help ensure that there is always a backup available.
Add the following code to the Timer event of a form. In this example, the data is backed up to the same file every time the event fires. Depending on your requirements, you might extend this to write data to a different file each time. This code creates a backup of data in a table called Customers. To run this code, you need to have a table with this name or replace the name of the table in the code. You also need to set the TimerInterval property of the form to an acceptable value in milliseconds. To back up data every hour, set the TimerInterval property to 60000.
(Visual Basic for Applications)
Private Sub Form_Timer()
' do a periodic backup of records
Dim stFile As String
Dim rs As DAO.Recordset2
Dim fld As DAO.Field2
Dim stData As String
' show status
Me.lblWait.Visible = True
DoEvents
' open the file name - latest data always available
stFile = CurrentProject.Path & “\CustomerBackup.txt”
Open stFile For Output As #1
' get the data
Set rs = CurrentDb().OpenRecordset(“Customers”)
While (Not rs.EOF)
' loop through fields and build the data string
For Each fld In rs.Fields
If (Not fld.IsComplex) Then
stData = stData & fld.Value & “|”
End If
Next
' print
Print #1, Left(stData, Len(stData) - 1)
rs.MoveNext
DoEvents
Wend
' cleanup
rs.Close
Set rs = Nothing
Set fld = Nothing
Close #1
' hide status
Me.lblWait.Visible = False
End Sub
Note |
---|
When we first started using the Timer event, we ran into a rather interesting problem. Every time we were writing code, IntelliSense would appear and then suddenly go away. Each line of code we typed would turn red as if there were a syntax error. It turned out that there was a Timer event running on an open form. The running timer would close any open windows such as the IntelliSense window in the code module. |
Moving a Form Without a Border
Depending on the type of look that you're after for your application, you may choose to create borderless forms. We use borderless forms for About dialog boxes described later in this chapter, but they are also useful for popup types of items, such as calendars and calculators. Without a border, however, it can be difficult to move the form! Fortunately for us, however, Access provides mouse events for forms and controls that can be used to move a form on the screen.
Start by creating a new form and set the Border Style property of the form to None. Next, add the following line of code to the declarations section of the form's module.
(Visual Basic for Applications)
Private fMoving As Boolean
Then, add the following code to the MouseDown event of the Detail section to set the fMoving flag. This begins the move operation.
(Visual Basic for Applications)
Private Sub Detail_MouseMove(Button As Integer, _
Shift As Integer, X As Single, Y As Single)
fMoving = True
End Sub
Most of the work happens on the MouseMove event of the Detail section. Here, you're using the window dimensions of the form to determine where to move it. The calculation shown here is designed to prevent the mouse from jumping to the top-left hand corner of the form. Once you've calculated the new position, move the form by calling its Move method.
(Visual Basic for Applications)
Private Sub Detail_MouseMove(Button As Integer, _
Shift As Integer, X As Single, Y As Single)
Dim sngNewX As Single
Dim sngNewY As Single
If (fMoving) Then
' calculate using window dimensions so the mouse doesn't jump
sngNewX = (Me.WindowLeft - Me.WindowWidth / 2) + X
sngNewY = (Me.WindowTop - Me.WindowHeight / 2) + Y
' move the form
Me.Move sngNewX, sngNewY
End If
End Sub
Last, you need to stop the move operation when the mouse is released. To do this, add the following code to the MouseUp event of the Detail section. This resets the flag so that when the MouseMove event fires again, you don't move the form unless you're in a move operation.
(Visual Basic for Applications)
Private Sub Detail_MouseMove(Button As Integer, _
Shift As Integer, X As Single, Y As Single)
fMoving = False
End Sub
Run the form and click in the Detail section to begin moving the form.
Customizing ControlTipText
Control tips, or tooltips are used to provide informational text when you hover over an object, such as a control. In many cases, this property contains a static value, but wouldn't it be cool if it could contain actual data? We think it would, so let's take a look.
Because the Current event of the form fires for every record, use this event to bind the ControlTipText property of a control based on values in the form.
(Visual Basic for Applications)
Private Sub Form_Current()
' add controltips
If (Not Me.NewRecord) Then
Me.[Last Name].ControlTipText = Me.[First Name] & “ “ & _
Me.[Last Name] & vbCrLf & _
“Business: “ & Nz(Me.[Business Phone]) & vbCrLf & _
“Home: “ & Nz(Me.[Home Phone]) & vbCrLf & _
“Mobile: “ & Nz(Me.[Mobile Phone])
End If
End Sub
Figure 8-1. This might look something like the form pictured here
Working with Controls
We've taken a look at scenarios using form events, so it's time to move our attention to controls. In this section, we take a look at ways to validate data at the control level, as well as how to use list boxes in advanced scenarios that provide user interfaces that are intuitive. We also discuss creating custom progress bars and the new attachment control in Access 2007.
Validating Control Data
Early in this chapter, we looked at how you could use the BeforeUpdate event of a form to validate all the controls on a form. For some forms, this technique is good but it can make complex forms more difficult for the user. If you want to notify the user that there's a problem in a control without having to validate everything, you use the BeforeUpdate event of a control instead.
The following code shows you how to cancel the BeforeUpdate event based on some criteria.
(Visual Basic for Applications)
Private Sub Taxes_BeforeUpdate(Cancel As Integer)
' no taxes for the following states:
Const LIST_NO_SALESTAX As String = “AK|DE|MT|NH|OR”
' get the state for the customer
Dim stState As String
stState = DLookup(“State/Province”, “Customers”, “ID=” & Me.[Customer ID])
' verify tax is not applied for the defined states
If (Me.Taxes < 0) Then
Cancel = True
MsgBox “Taxes cannot be negative”, vbExclamation
Exit Sub
ElseIf (Me.Taxes > 0 And InStr(LIST_NO_SALESTAX, stState) > 0) Then
Cancel = True
MsgBox “Cannot apply tax for customer located in: “ & stState, _
vbExclamation
Exit Sub
End If
End Sub
In this case, we're checking two things. First, we check that the amount of the tax is greater than 0 (this should probably be a validation rule in the table). Second, we're retrieving the state for the customer placing the order. If the state where the customer resides does not have a sales tax, then we cancel the event and alert the user.
Disabling all Controls
Rather frequently, we perform an action on all controls on a form. Typically, this is to apply common formatting, such as the BackColor property that you saw earlier, or to disable or enable controls on a form.
The following routine locks or unlocks all bound controls on a form. Notice that this routine includes error handling for the control types that don't support the ControlSource property.
(Visual Basic for Applications)
Private Sub LockControls(fLock As Boolean)
Dim c As Control
For Each c In Me.Controls
On Error Resume Next
' lock all bound controls
If (Len(c.ControlSource) > 0) Then
c.Locked = fLock
End If
On Error GoTo 0
Next
End Sub
Reusing a Subform Control
Subform controls are really powerful. They are used to show related data on a form or report, but can take up a fair amount of space depending on what you put in them. For that reason, as well as for performance reasons, we like to reuse subform controls to display one subform at a time. Using subforms also enables you to create what we call a frame-based application where the main form is a frame used for navigation or dashboard and the content for the application is switched in a subform.
Figure 8-2. Example of a frame-based application
In this application, selecting one of the buttons in the Microsoft Office Fluent user interface Ribbon changes the subform shown in the bottom of the screen. To reuse a subform control, simply set the SourceObject property of the subform control to the name of a form in the database.
Extended List Box Functionality
List boxes are a staple of Access forms. As with combo boxes, they display data from a table or query, a list of values, or the field list from a table or query. In this section, we take a look at how you can extend them to create highly functional user interfaces.
Moving Items Up and Down in a List Box
In Chapter 7, we discuss using a field that defines a custom sort order for items in a list. A list box is a natural approach for displaying these items, but editing the sort order might be difficult. To help make this easier, we'll add up/down buttons to a form, enabling users to move items up and down using these buttons.
Create the Table
Start with a simple categories table, as shown in Figure 8-3. Notice that this table contains a field called SortOrder that defines the order in which items should appear in the list. Save the table as tblCategories.
Figure 8-3. Start with a simple categories table
Create the Form
Next, create a new form named frmCategories and set the Pop Up property of the form to Yes. Add a list box, a text box, and two command buttons to the form. Set the properties of the controls, as shown in the table that follows.
Table 1. Form Controls Properties
Control Name |
Property Name |
Property Value |
List0 |
Column Count |
3 |
Column Widths |
0”;1”;0” |
|
Row Source |
SELECT CategoryID, Category, SortOrder FROM tblCategories ORDER BY SortOrder; |
|
Name |
lstCategories |
|
ControlTipText |
Press {delete} to delete a category |
|
Text2 |
Name |
txtNewCategory |
Command4 |
Caption |
Up |
Name |
cmdMovep |
|
Command5 |
Caption |
Down |
Name |
cmdMoveDown |
Arrange the form so that it resembles the one shown in Figure 8-4.
Let's start by adding code to the Click events of the two buttons. These event handlers call other routines that we define in a moment.
(Visual Basic for Applications)
Private Sub cmdMoveDown_Click()
MoveCategoryDown
End Sub
Private Sub cmdMoveUp_Click()
MoveCategoryUp
End Sub
Figure 8-4. Arrange the form to resemble this one
Because you're handling up and down behavior in the list, you should enable or disable the buttons depending on where you are in the list. Add the following code to the AfterUpdate event of the list box. This code disables the Up button when you're at the top of the list or the Down button when you're at the bottom of the list.
(Visual Basic for Applications)
Private Sub lstCategories_AfterUpdate()
DoEvents
' enable the move buttons if the first or last item has been set
If (Me.lstCategories.ListIndex = Me.lstCategories.ListCount - 1) Then
Me.cmdMoveUp.Enabled = True
Me.cmdMoveUp.SetFocus
Me.cmdMoveDown.Enabled = False
Else
Me.cmdMoveDown.Enabled = True
End If
If (Me.lstCategories.ListIndex = 0) Then
Me.cmdMoveDown.Enabled = True
Me.cmdMoveDown.SetFocus
Me.cmdMoveUp.Enabled = False
Else
Me.cmdMoveUp.Enabled = True
End If
Me.lstCategories.SetFocus
End Sub
When you click the Up button, items move up in the list. This is accomplished by swapping the sort order for the current item and the item above it. Start by adding the MoveCategoryUp routine that is called from the cmdMoveUp button.
(Visual Basic for Applications)
Private Sub MoveCategoryUp()
Dim lOldOrder As Long
Dim lNewOrder As Long
Dim lSelRow As Long
If there is a category selected, first determine the selected row.
' move the selected item up
If (Not (IsNull(Me.lstCategories))) Then
' Get the current selected row
lSelRow = Me.lstCategories.ListIndex
Remember that the sort order is part of the row source for the list box so use the Column property to get the old sort order. The new sort order is stored in the same column but for the previous row.
If (Not IsNull(Me.lstCategories.Column(2)) And _
Not IsNull(Me.lstCategories.Column(2, lSelRow + 1))) Then
' get the sortorder values for the current row and the next row
lOldOrder = Me.lstCategories.Column(2)
lNewOrder = Me.lstCategories.Column(2, lSelRow - 1)
To swap the two sort orders, call a routine called SwapSortOrders that you'll define in a moment.
' swap the sort order for the selected item and the one above it
SwapSortOrders lOldOrder, lNewOrder
Last, requery the list box so it has the current sort orders and refresh the button states by calling the AfterUpdate event handler for the list box.
' requery the listbox
Me.lstCategories.Requery
' update the buttons enabled state
lstCategories_AfterUpdate
End If
End If
End Sub
The MoveCategoryDown routine is very similar to MoveCategoryUp except that you want to get the sort order for the next item instead of the previous item.
(Visual Basic for Applications)
Private Sub MoveCategoryDown()
Dim lOldOrder As Long
Dim lNewOrder As Long
Dim lSelRow As Long
' move the selected item down
If (Not (IsNull(Me.lstCategories))) Then
' Get the current selected row
lSelRow = Me.lstCategories.ListIndex
If (Not IsNull(Me.lstCategories.Column(2)) And _
Not IsNull(Me.lstCategories.Column(2, lSelRow + 1))) Then
' get the sortorder values for the current row and the next row
lOldOrder = Me.lstCategories.Column(2)
lNewOrder = Me.lstCategories.Column(2, lSelRow + 1)
' swap the sort order for the selected item and the one above it
SwapSortOrders lOldOrder, lNewOrder
' requery the listbox
Me.lstCategories.Requery
' update the buttons enabled state
lstCategories_AfterUpdate
End If
End If
End Sub
To swap the sort orders, you need to update the tblCategories table. Start by defining the SwapSortOrders routine as follows.
(Visual Basic for Applications)
Private Sub SwapSortOrders(lOldOrder As Long, lNewOrder As Long)
Dim stSQL As String
Dim lTemp As Long
This is a pretty straightforward swap operation. You first need to save the old sort order.
' cache item1
lTemp = lOldOrder
Next, set the old sort order to a dummy value.
' set the item1 sort order to -1 temporarily
stSQL = “UPDATE tblCategories SET SortOrder = -1 “ & _
“WHERE SortOrder = “ & lNewOrder
CurrentProject.Connection.Execute stSQL
Then update the old sort order to the new sort order.
' set the new sort order to the old sort order
stSQL = “UPDATE tblCategories SET SortOrder = “ & lNewOrder & _
“ WHERE SortOrder = “ & lOldOrder
CurrentProject.Connection.Execute stSQL
And last, update the dummy value of -1 to the old sort order.
' final swap
stSQL = “UPDATE tblCategories SET SortOrder = “ & lTemp & _
“ WHERE SortOrder = -1”
CurrentProject.Connection.Execute stSQL
End Sub
The last thing you want to do is to add a category to the list box using the txtNewCategory text box. If a category already exists, you don't want to add it again so use the BeforeUpdate event of the text box to make this determination.
(Visual Basic for Applications)
Private Sub txtNewCategory_BeforeUpdate(Cancel As Integer)
' make sure the new category does not exist
Dim i As Integer
Dim stCat As String
' replace text
If (Not IsNull(Me.txtNewCategory)) Then
stCat = Replace(Replace(Me.txtNewCategory, “'“, “''“), _
Chr(34), Chr(34) & Chr(34))
i = Nz(DCount(“*“, “tblCategories”, “Category='“ & stCat & “'“), 0)
If (i > 0) Then
MsgBox “The category '“ & Me.txtNewCategory & “' already exists. “ & _
“Please specify a new category name.”, vbCritical
Cancel = True
End If
End If
End Sub
Notice that we've used the DCount function to determine whether the specified category already exists. If it does, cancel the event and alert the user.
If the category does not exist, add it to the underlying table with a sort order. To do this, use the AfterUpdate event of the control. Start by adding the event handler.
(Visual Basic for Applications)
Private Sub txtNewCategory_AfterUpdate()
' add a new category
Dim stSQL As String
Dim stCat As String
Dim lNewSortOrder As L
Next, do some cleanup of the text.
If (Not IsNull(Me.txtNewCategory)) Then
' replace text
stCat = Replace(Replace(Me.txtNewCategory, “'“, “''“), _
Chr(34), Chr(34) & Chr(34))
We need to determine the new sort order based on the maximum sort order in the table and add one to that.
' get the new sort order (put the new item at the end)
lNewSortOrder = Nz(DMax(“SortOrder”, “tblCategories”), 0) + 1
Next, run a SQL statement to add the new category and refresh the controls.
' build and run the SQL
stSQL = “INSERT INTO tblCategories (Category, SortOrder) VALUES ('“ & _
stCat & “',” & lNewSortOrder & “)“
CurrentDb.Execute stSQL
' update the controls
Me.lstCategories.Requery
Me.txtNewCategory = Null
Me.txtNewCategory.SetFocus
End If
End Sub
Deleting Items in a List Box with the Delete Key
In the list box created in the previous example, we defined a tooltip that told the user you would handle deleting items in the list using the delete key, so let's do that now. Add the following code to the KeyUp event of the lstCategories list box to handle the delete key.
(Visual Basic for Applications)
Private Sub lstCategories_KeyUp(KeyCode As Integer, Shift As Integer)
' trap deletes
Select Case KeyCode
Case vbKeyDelete
DeleteCategory
End Select
End Sub
Next, define the DeleteCategory routine.
(Visual Basic for Applications)
Private Sub DeleteCategory()
' handle deletes
Dim stCat As String
Dim stSQL As String
Dim stMsg As String
Dim stSelected As String
Dim lID As Long
Dim lSelOrder As Long
Start by retrieving the category ID and name from the list box.
' get the id and selected item
lID = CLng(Me.lstCategories.ItemData(Me.lstCategories.ListIndex))
stSelected = Me.lstCategories.Column(1, Me.lstCategories.ListIndex)
' message
stMsg = “Are you sure you want to delete the category: “ & stSelected
Deleting a category affects sort order. Get the sort order from the list box.
' existing sort order
lSelOrder = Me.lstCategories.ListIndex + 1
Prompt to delete, and if the user selects yes, then delete the category from the tblCategories table.
If (MsgBox(stMsg, vbExclamation + vbYesNo, “Delete Category”) = vbYes) Then
' delete the category
stSQL = “DELETE * FROM tblCategories WHERE CategoryID = “ & lID
CurrentDb.Execute stSQL
Then update all of the subsequent sort orders to the current order minus one.
' update the sort orders that follow
stSQL = “UPDATE tblCategories SET SortOrder = SortOrder - 1 “ & _
“WHERE SortOrder > “ & lSelOrder
CurrentDb.Execute stSQL
' requery
Me.lstCategories.Requery
lstCategories_AfterUpdate
End If
End Sub
Using a Multi-Select List Box for Filter Criteria
Because list boxes provide multiple values, they make a pretty nice choice for a query form. However, the Value property for a multi-select list box is Null so you need to build the criteria dynamically.
Using the Northwind 2007 sample database, start by creating a new form and adding a list box and a subform control. The list box stores the customers in the Customers table and the subform stores the results of the filter. Set the properties of the list box as shown in the table that follows.
Property Name |
Property Value |
Column Count |
2 |
Column Widths |
0" |
Row Source |
SELECT ID, Company FROM Customers ORDER BY Company; |
Name |
lstCustomers |
Multi Select |
Extended |
Set the properties of the subform control as follows:
Property Name |
Property Value |
Source Object |
Table.Orders |
Name |
sfrmResults |
Next, add two command buttons to the form named cmdFilter and cmdClearFilter. Add the following code to the cmdFilter button to create the filter for the subform.
(Visual Basic for Applications)
Private Sub cmdFilter_Click()
Dim stFilter As String
Dim vItem As Variant
' Build the filter string
For Each vItem In Me.lstCustomers.ItemsSelected
stFilter = stFilter & “[Customer ID] = “ & _
Me.lstCustomers.ItemData(vItem) & “ OR “
Next
'remove the last ' OR '
stFilter = Left(stFilter, Len(stFilter) - 4)
Me.sfrmResults.Form.Filter = stFilter
Me.sfrmResults.Form.FilterOn = True
End Sub
Add the following code to the cmdClearFilter button to remove the filter.
(Visual Basic for Applications)
Private Sub cmdClearFilter_Click()
' clear the filter
Me.sfrmResults.Form.Filter = “”
Me.sfrmResults.Form.FilterOn = False
End Sub
Selecting All Items in a Multi-Select List Box Using the Keyboard
If we continue with the multi-select list box from the previous example, let's say that you want to use the CTRL+A keyboard shortcut to select all items in a list box. Add the following code to the KeyDown event of the lstCustomers list box from the previous example to do this.
(Visual Basic for Applications)
Private Sub lstCustomers_KeyDown(KeyCode As Integer, Shift As Integer)
Dim lItem As Long
If (KeyCode = vbKeyA And ((Shift And acCtrlMask) = acCtrlMask)) Then
' select all items in the list
For lItem = 0 To Me.lstCustomers.ListCount - 1
Me.lstCustomers.Selected(lItem) = True
Next
End If
End Sub
This code walks through each item in the list box and sets its Selected property to True. When you select the list box, you should be able to press CTRL+A to select all items in the list box.
Custom Progress Bars
Way back when, the Office Developer Edition and Microsoft Office Developer released ActiveX versions of a progress bar control that you could use to provide feedback on a long running progress. ActiveX controls can simplify programming tasks but have deployment issues. As an alternative to an ActiveX control, the SysCmd function in Access can be used to display a progress bar in the status bar space at the bottom of the screen. This is pretty cool but what if the user chooses not to show his status bar or if you want to use that real estate? Unfortunately, the progress bar at the bottom of the screen sometimes goes unnoticed by users as well.
To solve these problems, we create lightweight progress bars using rectangle controls in Access. Visually we tend to prefer these controls for popup or modal forms that don't take up the entire screen and lean toward using the SysCmd function for full screen forms.
Creating this type of progress bar is pretty straight forward. It requires two rectangle controls that we call boxOutside and boxInside. As you can imagine from the names, boxInside is placed inside boxOutside. To update progress, the width of boxInside is adjusted as a percentage of the width of boxOutside. As with the progress bar created by the SysCmd function, this technique also requires that you know the number of items that you're measuring. Let's take a look at this in action.
Create the Form
The form will be fairly simplistic as we want to focus on the progress bar itself. Create a new form with two rectangles and a command button. Name the rectangles boxInside and boxOutside as mentioned earlier. Set the width of boxOutside to 4˝ so you can see the progress. Set the width of boxInside to 0˝ to start. Make sure that boxInside is not transparent or you won't be able to see anything! Name the command button cmdGo.
Run the Process
Because file operations may take some time, we write to a file by hand as a long running process. The file only contains the values from the loop as an example. Add the following code to the Click event of the button.
(Visual Basic for Applications)
Private Sub cmdGo_Click()
' create a file in the current directory
Dim i As Integer
Dim stFile As String
Dim sngOffset As Single
Dim n As Long
stFile = CurrentProject.Path & “\NewFile.txt”
' open the file
Open stFile For Output As #1
' number of items
n = 5000
For i = 1 To n
' print to the file
Print #1, i
' update the progress - includes an offset calculation for aesthetics
sngOffset = Me.boxInside.Left - Me.boxOutside.Left
Me.boxInside.Width = i * ((Me.boxOutside.Width - (sngOffset * 2)) / n)
Me.Repaint
DoEvents
Next
' close
Close #1
MsgBox “Done!”
End Sub
The algorithm shown here is one we use quite a bit and it includes an offset that bears some digging into. The rectangle boxOutside is formatted to have a border, whereas boxInside is not. To prevent boxInside from completely covering boxOutside, we tend to position the Left property of boxInside just barely to the right of boxOutside as shown in Figure 8-5, which has been zoomed to show the offset.
Figure 8-5. Position the Left property of boxInside just barely to the right of boxOutside
The offset calculation then is simply the difference in the Left property between boxInside and boxOutside. To keep this offset on both the left and right inside edges of boxOutside, multiply this by 2 when subtracting it from the width of boxOutside. Once the aesthetics are accounted for, divide the width of boxOutside by the number of items (n), and multiply by the current location in the loop (i). This dynamically determines the width of boxInside.
Custom Up/Down Buttons
Speaking of ActiveX controls, the toolkits mentioned in the previous section also had a pretty cool up/down control that users can click and hold a button to set values. This is pretty useful but it turns out that command buttons in Access let you do this without an ActiveX control.
Create a form with a text box and two command buttons named cmdDown and cmdUp, and a text box named txtInterval. Set the Auto Repeat property of the two command buttons to Yes. When this property is set, the Click event of a button fires repeatedly while the user is holding down the button.
Add the following code to the two buttons. This code adjusts the value of the text box in increments of 1000. When scrolling up, it scrolls without a limit. When scrolling down, it scrolls until it reaches 0.
(Visual Basic for Applications)
Private Sub cmdDown_Click()
' init
Me.txtInterval = Nz(Me.txtInterval, 1000)
' decrement to 0
If (CLng(Me.txtInterval) >= 1000) Then
Me.txtInterval = CLng(Me.txtInterval) - 1000
End If
DoEvents
End Sub
Private Sub cmdUp_Click()
' init
Me.txtInterval = Nz(Me.txtInterval, 1000)
' increment (unbounded)
Me.txtInterval = CLng(Me.txtInterval) + 1000
DoEvents
End Sub
Displaying Multiple Attachments Onscreen
The Attachment control in Access 2007 is used to display data in the Attachment data type. This is a multivalued field where multiple attachments can be stored in a single record. This data type is extremely useful and a big improvement over the OLE Object data type. Because it stores multiple files, however, navigation of attachments tends to happen within a single attachment control. For this example, let's say that you're developing a real estate application and want to display multiple attachments for a given property at once using a thumbnail. This is common in Web applications, such as www.realtor.com.
For this example, create a table called tblRealEstate with the fields shown in the following table.
Field Name |
Data Type (Size) |
ID |
Autonumber |
Address |
Text (255) |
City |
Text (255) |
State |
Text (255) |
HousePictures |
Attachment |
Note |
---|
Sample images that you can use for the attachment field are included with the sample available with this book on www.wrox.com. |
Fill the table with address information and images in the attachment field, as shown in Figure 8-6.
Next, create a form bound to this table. Set the Visible property of the HousePictures attachment control to No to hide the control. Add three new unbound attachment controls named Attachment1, Attachment2, and Attachment3 respectively. Set their height and width to 1.5” each to give them the appearance of being thumbnails.
Figure 8-6. Fill the table with address information and images in the attachment field
Time for the code, which is surprisingly simple. You'll remember that the HousePictures field stores the actual attachment files for each property in the tblRealEstate table. We know that we want to update the unbound attachment controls for each record so we need to use the Current event of the form. The AttachmentCount property of the attachment control returns the number of attachments in the control. The control also includes a property called DefaultPicture that is used to display an image if there are no attachments in the control. In our case, because we are using unbound controls, we can simply loop through the attachments in the HousePictures control to set the DefaultPicture property of the unbound controls, as shown in the following code. Navigation through the HousePictures field is done using the Forward method of the control.
(Visual Basic for Applications)
Private Sub Form_Current()
Dim i As Integer
Dim att As Attachment
' bind the attachments
For i = 0 To Me.HousePictures.AttachmentCount - 1
Set att = Me.Controls(“Attachment” & i + 1)
att.DefaultPicture = Me.HousePictures.FileName(i)
Me.HousePictures.Forward
Next
End Sub
Note |
---|
The graphics used in this sample are also used in the section “Kiosk Forms” later in this chapter. We originally created the sample kiosk form with these same images using unbound OLE Object controls as the result of a copy/paste from another application. After pasting all nine images, the size of the database was approximately 28MB. After adding them to the database as PNG files in an attachment field, the database shrunk to 1.75MB after compact! |
Next Part: Chapter 8:Using Code to Add Advanced Functionality to Forms (2 of 2)