Chapter 3: Selecting the Right Controls for Forms
In the previous chapter, in the course of creating forms for the Toy Workshop application, we also created controls to display and edit data on the forms. Most of the controls you will use (and almost all the ones automatically created when you drag fields to a form) will be of a few standard types: Textboxes, labels, comboboxes, and checkboxes are the most common. However, there are other standard control types that are useful for working with specific types of data, and some extra ActiveX controls that you can use, if you have the Developer Edition of Office (or another Microsoft application that includes these controls, such as Visual Basic). This chapter will describe the use of both standard and ActiveX controls for working with data in different ways.
Standard Controls
When you open an Access form in Design view, you have a special toolbar (called the Toolbox) available for placing controls on the form. If you don’t see the Toolbox, you can make it appear by clicking the Toolbox button on the toolbar (this is a toggle button, so clicking it again turns the Toolbox off). I’ll discuss the use of standard controls, with examples from various sample databases, and also tell you which ones aren’t really useful. Figure 3.1 shows the Toolbox with the control tools labeled.
Figure 3.1
Useful Standard Controls
The controls described in the following sections are most useful on Access forms.
| Important | When looking at Microsoft documentation and Help, sometimes you’ll see Text Box, sometimes Textbox, and similarly for Listbox/List Box, Combobox/Combo Box, and Checkbox/Check Box. For the sake of consistency, I’ll use the one-word variants of these control names throughout, but you may occasionally see the two-word variants in screen shots. |
Label
Labels are useful for displaying text for descriptive purposes. By default, when you place a textbox on a form, it has an attached label, but you can also place standalone labels on a form, for example to label groups of controls on a form. The Information on selected book label on the frmTreeViewEBookNotes form in the EBook Companion sample database (shown in Figure 3.2) serves this purpose.
Figure 3.2
Textbox
Textboxes are most commonly used to enter and edit data in tables. However, they can also be used to display the results of calculations (calculated fields) or to display other form-related information. The txtTotalCost field on the fsubToyMaterials subform embedded on frmToys (shown in Figure 3.3) displays the result of this calculated expression:
=[Quantity]*[UnitCost]
Figure 3.3
If you want to display the form’s record source on the form, you can use the expression =[RecordSource] in a textbox’s control source. Several other form properties can also be displayed in textboxes in this manner, as shown in Figure 3.4.
Figure 3.4
Option Group
An option group is a frame containing two or more option buttons; only one of the option buttons can be selected at any time. (This is why they are sometimes called radio buttons, because they work like buttons on a car radio). Although you can use option buttons, checkboxes, or even toggle buttons as buttons in an option group, it’s best to stick to option buttons, as they are most intuitive as a set of mutually exclusive choices. The frmOrders form in the Toy Workshop database (shown in Figure 3.5) uses an option group for selecting a shipping method.
Figure 3.5
Each option button in an option group has an Integer value, so option groups are well suited for selecting a value for Long or Integer fields, such as ID fields. The ShippingMethodID field in tblOrders is such a field, so the option group (fraShippingMethodID) can be bound directly to this field. If you need to store a text string in a field, depending on the user’s choice in an option group, you need to convert the Integer value to a String value, using the Switch function or a Select Case statement, such as the ones listed in the following example:
Private Sub fraShippingMethodID_AfterUpdate() On Error GoTo ErrorHandler Dim intShipMethod As Integer Dim strShipMethod As String intShipMethod = Nz(Me![fraShippingMethodID], 1) strShipMethod = Switch(intShipMethod = 1, "UPS", intShipMethod = 2, "FedEx", intShipMethod = 3, "USPS", intShipMethod = 4, "Air Express", intShipMethod = 5, "Speedy Messenger Service") MsgBox "Selected method: " & strShipMethod ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
The Switch function is more compact than a Select Case statement, but it can only handle up to eight alternates, so if you need to handle more than eight options, or perhaps run code that depends on the chosen option, a Select Case statement is needed.
Private Sub fraShippingMethodID_AfterUpdate() On Error GoTo ErrorHandler Dim intShipMethod As Integer Dim strShipMethod As String intShipMethod = Nz(Me![fraShippingMethodID], 1) Select Case intShipMethod Case 1 strShipMethod = "UPS" Case 2 strShipMethod = "FedEx" Case 3 strShipMethod = "USPS" Case 4 strShipMethod = "Air Express" Case 5 strShipMethod = "Speedy Messenger Service" End Select MsgBox "Selected method: " & strShipMethod ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
Option Button
An option button is a little circle that is dark when selected and clear when not selected. Option buttons can be used as standalone controls, but they are best suited for use in option groups where only one option can be selected at a time. The interface convention is that option buttons are used for mutually exclusive choices (as part of an option group), while checkboxes are used when you need to allow multiple selections from a group of options.
Checkbox
A checkbox is a little square that is checked to indicate selection. Although you can use checkboxes inside an option group, this is not advisable; they are best used to indicate a True/False (Yes/No) choice for a single field. If you need a group of options where more than one can be selected, you can’t use an option group (only one option can be selected at a time). Instead, place several standalone checkboxes on the form, and surround them with a rectangle. Using checkboxes instead of option buttons suggests to users that they can select several items from a group of choices, not just one.
Combobox
A combobox is used to make a single selection from a drop-down list of choices. The values in the list can be derived from a table, query, or SQL statement; a value list; or a field list. A very simple combobox (for example, to select a zip code) has a one-column list, and the selected item is stored in the combobox’s bound field. However, comboboxes can be quite complex, with several fields showing in the drop-down list (to aid in selection), and possibly storing a value to the bound field that doesn’t even appear in the drop-down list.
Table/Query Row Source
As an example of a more complex combobox, cboShipAddressID on frmOrders in the sample Toy Workshop database has a SQL statement row source that includes three fields from a union query, with a criterion on one field that filters the row source by the CustomerID of the customer selected on the form, as shown in Figure 3.6.
Figure 3.6
In the combobox properties sheet (shown in Figure 3.7), Table/Query is selected for the Row Source Type property.
Figure 3.7
The column count is 3, for the three columns selected in the query designer. These three columns are used in different ways in the combobox. The first column (ShipAddressID) contains the value that is written to the combobox’s bound field, ShipAddressID (shown in the Control Source property). The second column (CustomerID) has a criterion that limits the SQL statement results to records that have the same CustomerID as the customer selected in another combobox on the form. The third column (AddressIdentifier) contains the descriptive phrase that identifies the shipping address. Only the third column needs to show in the drop-down list, so the Column Widths are 0”; 0”; 2” to make columns 1 and 2 invisible, and column 3 visible and 2 inches wide.
Value List Row Source
As an alternative to deriving values from a table, query, or SQL statement, you can select Value List for the Row Source Type property and enter values into the Row Source property, as in the following list:
“UPS”;”FedEx”;”USPS”;”Air Express”;”Speedy Messenger Service”
For obvious reasons, value list row sources are only sensible if the list of choices is both limited and fixed; otherwise, you are better off using a lookup table as the combobox’s row source.
Field List Row Source
The third type of row source available for comboboxes is a field list. This choice is useful when you need to select a field for filtering or sorting. A field list combobox is used on frmExportToWordDatasheet in the Word Data Exchange sample database for Chapter 11, Working with Word (and similar forms in the other export sample databases), to allow the selection of a field for filtering data. cboFilterField has Value List selected as the Row Source Type, and qryContactsAlpha as the Row Source.
| Important | Fields displayed in a combobox with a field list row source are displayed in the order they occur in the table or query, so if you want the fields to be displayed alphabetically in the drop-down list, you have to create a special query for this purpose, with the fields arranged alphabetically. qryContactsAlpha is the alphabetized version of qryContacts. (These queries are located in the various Data Exchange sample databases.) |
Simply selecting a field isn’t generally useful; after selecting a field, you next need to select a value for that field, in order to do sorting or filtering. The second combobox, cboFilterValue, has a blank Row Source property; its row source is created from the AfterUpdate event of cboFilterField, and assigned to cboFilterValue. The VBA code for these two comboboxes follows:
Private Sub cboFilterField_AfterUpdate() On Error GoTo ErrorHandler pstrFilterField = Nz(Me![cboFilterField].Value) If pstrFilterField = "" Then strTitle = "No field selected" strPrompt = "Please select a field for filtering" MsgBox strPrompt, vbCritical + vbOKOnly, strTitle Me![cboFilterField].SetFocus GoTo ErrorHandlerExit End If If pstrDataType = "C" Then strQuery = "qryContactsAlpha" ElseIf pstrDataType = "E" Then strQuery = "qryEBookCatalogAlpha" End If strSQL = "SELECT DISTINCT " & strQuery & ".[" & pstrFilterField & "] FROM " & strQuery & " WHERE [" & pstrFilterField & "] Is Not Null;" Debug.Print "SQL string: " & strSQL With Me![cboFilterValue] .Value = Null .RowSource = strSQL .Requery .Enabled = True .SetFocus .Dropdown End With Me![txtFilterString].Value = Null ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub Private Sub cboFilterValue_AfterUpdate() On Error GoTo ErrorHandler Dim intDataType As Integer Dim fld As DAO.Field Dim qdf As DAO.QueryDef Dim strTotalsQuery As String Dim strLinkedQuery As String Dim strFilter As String pvarFilterValue = Me![cboFilterValue].Value ‘Determine data type of selected field Set dbs = CurrentDb Set rst = dbs.OpenRecordset(pstrQuery, dbOpenDynaset) Set fld = rst.Fields(pstrFilterField) intDataType = fld.Type Debug.Print "Field data type: " & intDataType Select Case intDataType Case 1 ‘Boolean strFilter = "[" & pstrFilterField & "] = " & pvarFilterValue Case 2, 3, 4, 6, 7 ‘Various numeric strFilter = "[" & pstrFilterField & "] = " & pvarFilterValue Case 5 ‘Currency strFilter = "[" & pstrFilterField & "] = " & CCur(pvarFilterValue) Case 8 ‘Date strFilter = "[" & pstrFilterField & "] = " & Chr$(35) & pvarFilterValue & Chr$(35) Case 10 ‘Text strFilter = "[" & pstrFilterField & "] = " & Chr$(34) & pvarFilterValue & Chr$(34) Case 11, 12, 15 ‘OLE object, Memo, Replication ID strPrompt = "Can’t filter by this field; please select another field" MsgBox strPrompt, vbCritical + vbOKOnly Me![cboFilterValue].SetFocus Me![cboFilterValue].Dropdown GoTo ErrorHandlerExit End Select Debug.Print "Filter string: " & strFilter ‘Apply filter to record source and make a table from it. Me![txtFilterString] = strFilter strQuery = "qmakMatchingRecords" strSQL = "SELECT " & pstrQuery & ".* INTO tmakMatchingRecords " & "FROM " & pstrQuery & " WHERE " & strFilter & ";" Debug.Print "SQL Statement: " & strSQL Set qdf = dbs.CreateQueryDef(strQuery, strSQL) qdf.Execute Me![cboFilterField].Value = Null Me![cboFilterValue].Value = Null If pstrDataType = "C" Then Me![subContacts].SourceObject = "fsubContactsFiltered" Debug.Print "subContacts source object: " & Me![subContacts].SourceObject ElseIf pstrDataType = "E" Then Me![subEBookCatalog].SourceObject = "fsubEBookCatalogFiltered" Debug.Print "subEBookCatalog source object: " & Me![subEBookCatalog].SourceObject End If ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
cboFilterField’s AfterUpdate event first checks that a field has been selected, and then (depending on the value of a previously set public variable) sets strQuery to one of two values. Next, a SQL string is created that displays all the values for the selected field in the query represented by strQuery. cboFilterValue is then set to Null, and its row source is set to the SQL statement. cboFilterValue is then requeried and enabled, focus is set to it, and its list is dropped down.
After a value is selected from cboFilterValue, the data type of the selected field is picked up from the field’s Type property, and a Select Case statement is set up to create a filter string differently according to the field data type. For Boolean and most numeric fields, the field value is used alone. For currency fields, CCur is used to convert the field type to Currency (I have noticed that sometimes Currency fields lose their data type when picked up from code). Date field values are wrapped in the number sign character (Chr$(35)), text field values are wrapped in quotes (Chr$(34)), and several fields that can’t be used for filtering get an appropriate message.
Next, the filter string is applied to the record source, and a table is created from it. Finally, depending on the value of a public variable, the appropriate source object is assigned to a subform (the table just created is the record source of the source object subform).
Add-to Combobox
The default settings for a combobox don’t allow users to enter a value that isn’t a selection in the list. You can set the Limit To List property to No to allow users to enter a value that isn’t in the list, or you can write code on the combobox’s Not In List event to automate the addition of new entries into a lookup table used as a combobox’s row source (I call this an add-to combobox). I’ll convert the cboCategoryID combobox on frmToys in the Toy Workshop sample database into an add-to combobox as an example. This combobox has tblCategories as its row source, and this table has only two fields: CategoryID (an AutoNumber field) and CategoryName. Since the CategoryID field’s value is filled in automatically when a new record is created, the only information needed to create a new record is the category name.
To convert this combobox to an add-to combobox, check that its Limit To List property is Yes (it probably is because that is the default value), and create an event procedure stub for the Not in List property by clicking on the Build button next to this event.
| Important | Some event procedures are displayed in the Events page of the properties sheet starting with an “On,” and others are not (for example, On Not in List, After Update). In VBAcode, “On” is never used as part of an event procedure name. I will not use the “On,” which is unnecessary and confusing. |
I make so many add-to comboboxes that I created a standard boilerplate procedure that I can just plug into a new NotInList event procedure stub, and make a few changes to customize it for the specific control, form, and table names. The Not in List event procedure with the boilerplate filler is listed below; the underscores represent the parts that need to be customized.
Private Sub cboCategoryID_NotInList(NewData As String, Response As Integer) ‘Set Limit to List to Yes On Error GoTo ErrorHandler Dim intResult As Integer Dim strTitle As String Dim intMsgDialog As Integer Dim strMsg1 As String Dim strMsg2 As String Dim strMsg As String Dim cbo As Access.ComboBox Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim strTable As String Dim strEntry As String Dim strFieldName As String ‘The name of the lookup table strTable = "_______" ‘The type of item to add to the table strEntry = "_________" ‘The field in the lookup table in which the new entry is stored strFieldName = "_____________" ‘The add-to combobox Set cbo = Me![_____________] ‘Display a message box asking whether the user wants to add ‘a new entry. strTitle = strEntry & " not in list" intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1 strMsg1 = "Do you want to add " strMsg2 = " as a new " & strEntry & " entry?" strMsg = strMsg1 + strNewData + strMsg2 intResult = MsgBox(strMsg, intMsgDialog, strTitle) If intResult = vbNo Then ‘Cancel adding the new entry to the lookup table. intResponse = acDataErrContinue cbo.Undo GoTo ErrorHandlerExit ElseIf intResult = vbYes Then ‘Add a new record to the lookup table. Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strTable) rst.AddNew rst(strFieldName) = strNewData rst.Update rst.Close ‘Continue without displaying default error message. intResponse = acDataErrAdded End If ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
The first step is to put the appropriate LNC variable tags in the event procedure’s arguments (the tags are italicized):
Private Sub cboCategoryID_NotInList(strNewData As String, intResponse As Integer)
Next, the underscores in the boilerplate code need to be replaced with appropriate text, as follows:
-
The name of the lookup table: tblCategories
-
The type of item to add to the table: category
-
The field in the lookup table in which the new entry is stored: CategoryName
-
The add-to combobox: cboCategoryID
The edited procedure is listed below:
Private Sub cboCategoryID_NotInList(strNewData As String, intResponse As Integer) ‘Set Limit to List to Yes. On Error GoTo ErrorHandler Dim intResult As Integer Dim strTitle As String Dim intMsgDialog As Integer Dim strMsg1 As String Dim strMsg2 As String Dim strMsg As String Dim cbo As Access.ComboBox Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim strTable As String Dim strEntry As String Dim strFieldName As String ‘The name of the lookup table strTable = "tblCategories" ‘The type of item to add to the table strEntry = "category" ‘The field in the lookup table in which the new entry is stored strFieldName = "CategoryName" ‘The add-to combobox Set cbo = Me![cboCategoryID] ‘Display a message box asking whether the user wants to add ‘a new entry. strTitle = strEntry & " not in list" intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1 strMsg1 = "Do you want to add " strMsg2 = " as a new " & strEntry & " entry?" strMsg = strMsg1 + strNewData + strMsg2 intResult = MsgBox(strMsg, intMsgDialog, strTitle) If intResult = vbNo Then ‘Cancel adding the new entry to the lookup table. intResponse = acDataErrContinue cbo.Undo GoTo ErrorHandlerExit ElseIf intResult = vbYes Then ‘Add a new record to the lookup table. Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strTable) rst.AddNew rst(strFieldName) = strNewData rst.Update rst.Close ‘Continue without displaying default error message. intResponse = acDataErrAdded End If ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
When the user types a new category name into the combobox, the Not in List procedure puts up a message box asking if the user wants to add it as a new category entry; several named constants are used to handle the user’s response. If the response is negative, intResponse is set to acDataErrContinue, and the value is removed from the combobox, leaving you ready to select a value from the list. If the response is positive, the a DAO recordset is created, based on the lookup table, the new value is written to the appropriate field in the table, and the record is updated. intResponse is then set to the named variable acDataErrAdded. Figure 3.8 shows the message box that appears when Construction kits is entered into cboCategoryID.
Figure 3.8
The result is that you have not just selected a category for the current record in tblToys, but added it as a category selection that will be available for selection in frmToys in future (or any other form where you can select a toy category).
Listbox
Listboxes are similar to comboboxes in many ways: they can also take a table/query, value list, or field list as a row source, and they display a list of items for the user to choose from. However, listboxes don’t offer the possibility of entering data manually, and they do offer a special possibility not available for comboboxes: Listboxes have a MultiSelect property that lets users select more than one item for processing, and a handy ItemsSelected collection for working with selected items in VBA code.
I use MultiSelect listboxes for selecting multiple records from an Access table for export to Word, Outlook, or Excel. One of these listboxes is lstSelectMultiple on frmExportToWordListBox in the Word Data Exchange sample database for Chapter 11, Working with Word. Many of the properties of listboxes are the same as those of comboboxes; the MultiSelect property is unique, however. This property has three choices: None, Simple, and Extended, explained in the following list:
None. Only one choice can be made.
Simple. Click or press the spacebar to select or deselect items.
Extended. Multiple records can be selected, using the standard Ctrl-Click and Shift-Click keys to extend the selection (Ctrl-Click adds a single record to the previously selected items; Shift-Click extends the selection from the currently selected record through the clicked record). This is generally the best choice, since it is much easier to select large numbers of records using Shift-Click.
As with comboboxes, you can make a listbox column invisible by setting its width to zero in the Column Widths property, and you can reference data in invisible columns. Figure 3.9 shows lstSelectMultiple, with several items selected.
Figure 3.9
Four columns are displayed in the listbox, but if you look at the properties sheet (shown in Figure 3.10), you will see that there are actually 12 columns.
Figure 3.10
You can reference data in any of the columns in the listbox, using the following syntax:
strName = Nz(lst.Column(7, varItem))
where the lst variable references the listbox, the first Column argument references the column number (the first column is numbered 0), and the varItem variable represents the number of the selected item in the ItemsSelected collection. The Nz function is used to convert a blank value into either a zero (for numeric values) or a zero-length string (for text values), to prevent problems with storing Nulls to fields or variables that can’t accept them.
The complete event procedure that creates letters for the records selected in the above listbox is listed below.
Private Sub cmdCreateDocuments_Click() On Error GoTo ErrorHandler Dim blnSomeSkipped As Boolean Dim cbo As Access.ComboBox Dim dbs As DAO.Database Dim i As String Dim intMergeType As Integer Dim intSaveNameFail As String Dim lngContactID As Long Dim prps As Object Dim rst As DAO.Recordset Dim strAddress As String Dim strCompanyName As String Dim strCountry As String Dim strDocName As String Dim strDocsPath As String Dim strDocType As String Dim strEnvelopeAddress As String Dim strEnvelopeName As String Dim strFile As String Dim strJobTitle As String Dim strLetterText As String Dim strLongDate As String Dim strName As String Dim strNameAndJob As String Dim strPrompt As String Dim strSalutation As String Dim strSaveName As String Dim strSaveNamePath As String Dim strShortDate As String Dim strSQL As String Dim strTable As String Dim strTemplatePath As String Dim strTest As String Dim strTestFile As String Dim strTextFile As String Dim strTitle As String Dim strWordTemplate As String Dim varItem As Variant ‘Check that a letter has been selected. Set cbo = Me![cboSelectTemplate] Set lst = Me![lstSelectMultiple] strWordTemplate = Nz(cbo.Column(1)) Debug.Print "Selected template: " & strWordTemplate If strWordTemplate = "" Then MsgBox "Please select a document." cbo.SetFocus cbo.Dropdown GoTo ErrorHandlerExit Else intMergeType = cbo.Column(3) Debug.Print "Merge type: " & intMergeType End If ‘Check that at least one contact has been selected. If lst.ItemsSelected.Count = 0 Then MsgBox "Please select at least one contact." lst.SetFocus GoTo ErrorHandlerExit Else intColumns = lst.ColumnCount intRows = lst.ItemsSelected.Count End If ‘Set global Word application variable; if Word is not running, ‘the error handler defaults to CreateObject Set appWord = GetObject(, "Word.Application") ‘Set date and folder reference variables. strLongDate = Format(Date, "mmmm d, yyyy") strShortDate = Format(Date, "m-d-yyyy") strDocsPath = GetDocsDir & "Access Merge\" strTemplatePath = TemplateDir strWordTemplate = strTemplatePath & strWordTemplate strLetterText = Nz(Me![LetterText]) ‘Check for existence of template in template folder, ‘and exit if not found strTestFile = Nz(Dir(strWordTemplate)) Debug.Print "Test file: " & strTestFile If strTestFile = "" Then MsgBox strWordTemplate & " template not found; can’t create document" GoTo ErrorHandlerExit End If ‘Open text file for writing information about skipped records strFile = strDocsPath & "Skipped Records.txt" Open strFile For Output As #1 Print #1, "These records were skipped when creating documents" Print #1, Select Case intMergeType Case 1 ‘Bookmarks blnSomeSkipped = False For Each varItem In lst.ItemsSelected ‘Get Contact ID for reference lngContactID = Nz(lst.Column(0, varItem)) Debug.Print "Contact ID: " & lngContactID ‘Check for required address information. strTest = Nz(lst.Column(2, varItem)) Debug.Print "Street address: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No street address for Contact " & lngContactID GoTo NextItem1 End If strTest = Nz(lst.Column(3, varItem)) Debug.Print "City: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No city for Contact " & lngContactID GoTo NextItem1 End If strTest = Nz(lst.Column(5, varItem)) Debug.Print "Postal code: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No postal code for Contact " & lngContactID GoTo NextItem1 End If strName = Nz(lst.Column(7, varItem)) strJobTitle = Nz(lst.Column(10, varItem)) If strJobTitle <> "" Then strNameAndJob = strName & vbCrLf & strJobTitle End If strAddress = Nz(lst.Column(8, varItem)) Debug.Print "Address: " & strAddress strCountry = Nz(lst.Column(6, varItem)) If strCountry <> "USA" Then strAddress = strAddress & vbCrLf & strCountry End If strCompanyName = Nz(lst.Column(9, varItem)) strSalutation = Nz(lst.Column(11, varItem)) ‘Open a new document based on the selected template. appWord.Documents.Add strWordTemplate ‘Write information to Word bookmarks (excluding some for One-up ‘Label docs). On Error Resume Next With appWord.Selection .GoTo What:=wdGoToBookmark, Name:="Name" .TypeText Text:=strName .GoTo What:=wdGoToBookmark, Name:="CompanyName" If Left(cbo.Value, 12) <> "One-up Label" Then .TypeText Text:=strCompanyName End If .GoTo What:=wdGoToBookmark, Name:="Address" .TypeText Text:=strAddress .GoTo What:=wdGoToBookmark, Name:="Salutation" If Left(cbo.Value, 12) <> "One-up Label" Then .TypeText Text:=strSalutation End If .GoTo What:=wdGoToBookmark, Name:="TodayDate" If Left(cbo.Value, 12) <> "One-up Label" Then .TypeText Text:=strLongDate End If .GoTo What:=wdGoToBookmark, Name:="EnvelopeName" If Left(cbo.Value, 12) <> "One-up Label" Then .TypeText Text:=strName End If .GoTo What:=wdGoToBookmark, Name:="EnvelopeCompany" If Left(cbo.Value, 12) <> "One-up Label" Then .TypeText Text:=strCompanyName End If .GoTo What:=wdGoToBookmark, Name:="EnvelopeAddress" If Left(cbo.Value, 12) <> "One-up Label" Then .TypeText Text:=strAddress End If .GoTo What:=wdGoToBookmark, Name:="LetterText" If Left(cbo.Column(1), 9) = "Freestyle" Then .TypeText Text:=strLetterText End If End With On Error GoTo ErrorHandler ‘Check for existence of previously saved letter in documents folder, ‘and append an incremented number to save name if found strDocType = appWord.ActiveDocument.BuiltInDocumentProperties(wdPropertySubject) strSaveName = strDocType & " to " & strName strSaveName = strSaveName & " on " & strShortDate & ".doc" i = 2 intSaveNameFail = True Do While intSaveNameFail strSaveNamePath = strDocsPath & strSaveName Debug.Print "Proposed save name and path: " & vbCrLf & strSaveNamePath strTestFile = Nz(Dir(strSaveNamePath)) Debug.Print "Test file: " & strTestFile If strTestFile = strSaveName Then Debug.Print "Save name already used: " & strSaveName ‘Create new save name with incremented number intSaveNameFail = True strSaveName = strDocType & " " & CStr(i) & " to " & strName strSaveName = strSaveName & " on " & strShortDate & ".doc" strSaveNamePath = strDocsPath & strSaveName Debug.Print "New save name and path: " & vbCrLf & strSaveNamePath i = i + 1 Else Debug.Print "Save name not used: " & strSaveName intSaveNameFail = False End If Loop ‘Update fields in Word document and save it With appWord .Selection.WholeStory .Selection.Fields.Update .Selection.HomeKey Unit:=wdStory .ActiveDocument.SaveAs strSaveNamePath .Visible = True .ActiveWindow.WindowState = wdWindowStateNormal .Activate End With NextItem1: Next varItem strTitle = "Merge done" If blnSomeSkipped = True Then strPrompt = "All documents created; some records skipped because " & "of missing information." & vbCrLf & "See " & strDocsPath & "Skipped Records.txt for details." Else strPrompt = "All documents created!" End If MsgBox strPrompt, vbOKOnly + vbInformation, strTitle Case 2 ‘Doc Properties blnSomeSkipped = False For Each varItem In lst.ItemsSelected ‘Get Contact ID for reference lngContactID = Nz(lst.Column(0, varItem)) Debug.Print "Contact ID: " & lngContactID ‘Check for required address information. strTest = Nz(lst.Column(2, varItem)) Debug.Print "Street address: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No street address for Contact " & lngContactID GoTo NextItem2 End If strTest = Nz(lst.Column(3, varItem)) Debug.Print "City: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No city for Contact " & lngContactID GoTo NextItem2 End If strTest = Nz(lst.Column(5, varItem)) Debug.Print "Postal code: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No postal code for Contact " & lngContactID GoTo NextItem2 End If strName = Nz(lst.Column(7, varItem)) strJobTitle = Nz(lst.Column(10, varItem)) If strJobTitle <> "" Then strNameAndJob = strName & vbCrLf & strJobTitle End If strAddress = Nz(lst.Column(8, varItem)) Debug.Print "Address: " & strAddress strCountry = Nz(lst.Column(6, varItem)) If strCountry <> "USA" Then strAddress = strAddress & vbCrLf & strCountry End If strCompanyName = Nz(lst.Column(9, varItem)) strSalutation = Nz(lst.Column(11, varItem)) ‘Open a new letter based on the selected template. appWord.Documents.Add strWordTemplate ‘Write information to Word custom document properties Set prps = appWord.ActiveDocument.CustomDocumentProperties prps.Item("Name").Value = strName On Error Resume Next With prps .Item("Salutation").Value = strSalutation .Item("CompanyName").Value = strCompanyName .Item("Address").Value = strAddress .Item("TodayDate").Value = strLongDate .Item("LetterText").Value = strLetterText End With On Error GoTo ErrorHandler ‘Check for existence of previously saved document in documents folder, ‘and append an incremented number to save name if found strDocType = appWord.ActiveDocument.BuiltInDocumentProperties(wdPropertySubject) strSaveName = strDocType & " to " & strName strSaveName = strSaveName & " on " & strShortDate & ".doc" i = 2 intSaveNameFail = True Do While intSaveNameFail strSaveNamePath = strDocsPath & strSaveName Debug.Print "Proposed save name and path: " & vbCrLf & strSaveNamePath strTestFile = Nz(Dir(strSaveNamePath)) Debug.Print "Test file: " & strTestFile If strTestFile = strSaveName Then Debug.Print "Save name already used: " & strSaveName ‘Create new save name with incremented number intSaveNameFail = True strSaveName = strDocType & " " & CStr(i) & " to " & strName strSaveName = strSaveName & " on " & strShortDate & ".doc" strSaveNamePath = strDocsPath & strSaveName Debug.Print "New save name and path: " & vbCrLf & strSaveNamePath i = i + 1 Else Debug.Print "Save name not used: " & strSaveName intSaveNameFail = False End If Loop ‘Update fields in Word document and save it With appWord .Selection.WholeStory .Selection.Fields.Update .Selection.HomeKey Unit:=wdStory .ActiveDocument.SaveAs strSaveNamePath .Visible = True .ActiveWindow.WindowState = wdWindowStateNormal .Activate End With NextItem2: Next varItem strTitle = "Merge done" If blnSomeSkipped = True Then strPrompt = "All documents created; some records skipped because " & "of missing information." & vbCrLf & "See " & strDocsPath & "Skipped Records.txt for details." Else strPrompt = "All documents created!" End If MsgBox strPrompt, vbOKOnly + vbInformation, strTitle Case 3 ‘Mail Merge blnSomeSkipped = False ‘Clear tblMergeList and set up recordset based on it strTable = "tblMailMergeList" strSQL = "DELETE tblMailMergeList.* FROM tblMailMergeList;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL Set dbs = CurrentDb Debug.Print "Opening recordset based on " & strTable Set rst = dbs.OpenRecordset(strTable, dbOpenTable) For Each varItem In lst.ItemsSelected ‘Get Contact ID for reference lngContactID = Nz(lst.Column(0, varItem)) Debug.Print "Contact ID: " & lngContactID ‘Check for required address information. strTest = Nz(lst.Column(2, varItem)) Debug.Print "Street address: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No street address for Contact " & lngContactID GoTo NextItem3 End If strTest = Nz(lst.Column(3, varItem)) Debug.Print "City: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No city for Contact " & lngContactID GoTo NextItem3 End If strTest = Nz(lst.Column(5, varItem)) Debug.Print "Postal code: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No postal code for Contact " & lngContactID GoTo NextItem3 End If strName = Nz(lst.Column(7, varItem)) strJobTitle = Nz(lst.Column(10, varItem)) strAddress = Nz(lst.Column(8, varItem)) Debug.Print "Address: " & strAddress strCountry = Nz(lst.Column(6, varItem)) If strCountry <> "USA" Then strAddress = strAddress & vbCrLf & strCountry End If strCompanyName = Nz(lst.Column(9, varItem)) strSalutation = Nz(lst.Column(11, varItem)) ‘Write data from variables to a new record in table With rst .AddNew !Name = strName !JobTitle = strJobTitle !CompanyName = strCompanyName !Address = strAddress !Salutation = strSalutation !TodayDate = strLongDate .Update End With NextItem3: Next varItem rst.Close ‘Export merge list to a text file ‘strDBPath = Application.CurrentProject.Path & "\" strDocsPath = GetDocsDir & "Access Merge\" Debug.Print "Docs path: " & strDocsPath strTextFile = strDocsPath & "Mail Merge Data.txt" Debug.Print "Text file for merge: " & strTextFile DoCmd.TransferText transfertype:=acExportDelim, TableName:=strTable, FileName:=strTextFile, HasFieldNames:=True ‘Open a new merge document based on the selected template. appWord.Documents.Add strWordTemplate strDocName = appWord.ActiveDocument Debug.Print "Initial doc name: " & strDocName ‘Check for existence of previously saved letter in documents folder, ‘and append an incremented number to save name if found strDocType = appWord.ActiveDocument.BuiltInDocumentProperties(wdPropertySubject) strSaveName = strDocType & " on " & strShortDate & ".doc" i = 2 intSaveNameFail = True Do While intSaveNameFail strSaveNamePath = strDocsPath & strSaveName Debug.Print "Proposed save name and path: " & vbCrLf & strSaveNamePath strTestFile = Nz(Dir(strSaveNamePath)) ‘Debug.Print "Test file: " & strTestFile If strTestFile = strSaveName Then ‘Debug.Print "Save name already used: " & strSaveName ‘Create new save name with incremented number intSaveNameFail = True strSaveName = strDocType & " " & CStr(i) & " on " & strShortDate & ".doc" strSaveNamePath = strDocsPath & strSaveName ‘Debug.Print "New save name and path: " & vbCrLf & strSaveNamePath i = i + 1 Else ‘Debug.Print "Save name not used: " & strSaveName intSaveNameFail = False End If Loop ‘Set the merge data source to the text file just created, ‘and do the merge. With appWord .ActiveDocument.MailMerge.OpenDataSource Name:=strTextFile, Format:=wdOpenFormatText .ActiveDocument.MailMerge.Destination = wdSendToNewDocument .ActiveDocument.MailMerge.Execute .ActiveDocument.SaveAs strSaveNamePath .Documents(strDocName).Close SaveChanges:=wdDoNotSaveChanges .Visible = True .ActiveWindow.WindowState = wdWindowStateNormal .Activate End With strTitle = "Merge done" If blnSomeSkipped = True Then strPrompt = "Merge document created; some records skipped because " & "of missing information." & vbCrLf & "See " & strDocsPath & "Skipped Records.txt for details." Else strPrompt = "Merge document created!" End If MsgBox strPrompt, vbOKOnly + vbInformation, strTitle Case 4 ‘Catalog Merge blnSomeSkipped = False ‘Clear tblCatalogMergeList and set up recordset based on it strTable = "tblCatalogMergeList" strSQL = "DELETE tblCatalogMergeList.* FROM tblCatalogMergeList;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL Set dbs = CurrentDb Debug.Print "Opening recordset based on " & strTable Set rst = dbs.OpenRecordset(strTable, dbOpenTable) For Each varItem In lst.ItemsSelected ‘Write data from listbox to a new record in table With rst .AddNew ![AuthorName] = Nz(lst.Column(0, varItem)) ![BookTitle] = Nz(lst.Column(1, varItem)) ![Category] = Nz(lst.Column(2, varItem)) .Update End With NextItem4: Next varItem rst.Close ‘Export merge list to a text file ‘strDBPath = Application.CurrentProject.Path & "\" strDocsPath = GetDocsDir & "Access Merge\" Debug.Print "Docs path: " & strDocsPath strTextFile = strDocsPath & "Catalog Merge Data.txt" Debug.Print "Text file for merge: " & strTextFile DoCmd.TransferText transfertype:=acExportDelim, TableName:=strTable, FileName:=strTextFile, HasFieldNames:=True ‘Open a new merge document based on the selected template. appWord.Documents.Add strWordTemplate strDocName = appWord.ActiveDocument Debug.Print "Initial doc name: " & strDocName ‘Check for existence of previously saved letter in documents folder, ‘and append an incremented number to save name if found strDocType = appWord.ActiveDocument.BuiltInDocumentProperties(wdPropertySubject) strSaveName = strDocType & " on " & strShortDate & ".doc" i = 2 intSaveNameFail = True Do While intSaveNameFail strSaveNamePath = strDocsPath & strSaveName Debug.Print "Proposed save name and path: " & vbCrLf & strSaveNamePath strTestFile = Nz(Dir(strSaveNamePath)) ‘Debug.Print "Test file: " & strTestFile If strTestFile = strSaveName Then ‘Debug.Print "Save name already used: " & strSaveName ‘Create new save name with incremented number intSaveNameFail = True strSaveName = strDocType & " " & CStr(i) & " on " & strShortDate & ".doc" strSaveNamePath = strDocsPath & strSaveName ‘Debug.Print "New save name and path: " & vbCrLf & strSaveNamePath i = i + 1 Else ‘Debug.Print "Save name not used: " & strSaveName intSaveNameFail = False End If Loop ‘Set the merge data source to the text file just created, ‘and do the merge. With appWord .ActiveDocument.MailMerge.OpenDataSource Name:=strTextFile, Format:=wdOpenFormatText .ActiveDocument.MailMerge.Destination = wdSendToNewDocument .ActiveDocument.MailMerge.Execute .ActiveDocument.SaveAs strSaveNamePath .Documents(strDocName).Close SaveChanges:=wdDoNotSaveChanges .Visible = True .ActiveWindow.WindowState = wdWindowStateNormal .Activate End With strTitle = "Merge done" If blnSomeSkipped = True Then strPrompt = "Merge document created; some records skipped because " & "of missing information." & vbCrLf & "See " & strDocsPath & "Skipped Records.txt for details." Else strPrompt = "Merge document created!" End If MsgBox strPrompt, vbOKOnly + vbInformation, strTitle Case 5 ‘TypeText method blnSomeSkipped = False ‘Open a new document based on the selected template. appWord.Documents.Add strWordTemplate For Each varItem In lst.ItemsSelected ‘Write info from contact item to variables ‘Get Contact ID for reference lngContactID = Nz(lst.Column(0, varItem)) Debug.Print "Contact ID: " & lngContactID ‘Check for required address information. strTest = Nz(lst.Column(2, varItem)) Debug.Print "Street address: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No street address for Contact " & lngContactID GoTo NextItem5 End If strTest = Nz(lst.Column(3, varItem)) Debug.Print "City: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No city for Contact " & lngContactID GoTo NextItem5 End If strTest = Nz(lst.Column(5, varItem)) Debug.Print "Postal code: " & strTest If strTest = "" Then blnSomeSkipped = True Print #1, Print #1, "No postal code for Contact " & lngContactID GoTo NextItem5 End If strName = Nz(lst.Column(7, varItem)) strJobTitle = Nz(lst.Column(10, varItem)) If strJobTitle <> "" Then strName = strName & vbCrLf & strJobTitle End If strAddress = Nz(lst.Column(8, varItem)) Debug.Print "Address: " & strAddress strCountry = Nz(lst.Column(6, varItem)) If strCountry <> "USA" Then strAddress = strAddress & vbCrLf & strCountry End If strCompanyName = Nz(lst.Column(9, varItem)) strSalutation = Nz(lst.Column(11, varItem)) ‘Insert data into labels. With appWord .Selection.TypeText Text:=strName .Selection.TypeParagraph .Selection.TypeText Text:=strCompanyName .Selection.TypeParagraph .Selection.TypeText Text:=strAddress .Selection.TypeParagraph .Selection.MoveRight Unit:=wdCell End With NextItem5: Next varItem ‘Check for existence of previously saved document in documents folder, ‘and append an incremented number to save name if found strDocType = appWord.ActiveDocument.BuiltInDocumentProperties(wdPropertySubject) strSaveName = strDocType & " on " & strShortDate & ".doc" i = 2 intSaveNameFail = True Do While intSaveNameFail strSaveNamePath = strDocsPath & strSaveName Debug.Print "Proposed save name and path: " & vbCrLf & strSaveNamePath strTestFile = Nz(Dir(strSaveNamePath)) Debug.Print "Test file: " & strTestFile If strTestFile = strSaveName Then Debug.Print "Save name already used: " & strSaveName ‘Create new save name with incremented number intSaveNameFail = True strSaveName = strDocType & " " & CStr(i) & " on " & strShortDate & ".doc" strSaveNamePath = strDocsPath & strSaveName Debug.Print "New save name and path: " & vbCrLf & strSaveNamePath i = i + 1 Else Debug.Print "Save name not used: " & strSaveName intSaveNameFail = False End If Loop With appWord .Selection.HomeKey Unit:=wdStory .ActiveDocument.SaveAs strSaveNamePath .Visible = True .ActiveWindow.WindowState = wdWindowStateNormal .Activate End With strTitle = "Merge done" If blnSomeSkipped = True Then strPrompt = "Merge document created; some records skipped because " & "of missing information." & vbCrLf & "See " & strDocsPath & "Skipped Records.txt for details." Else strPrompt = "Merge document created!" End If MsgBox strPrompt, vbOKOnly + vbInformation, strTitle End Select ErrorHandlerExit: Close #1 Exit Sub ErrorHandler: If Err = 429 Then ‘Word is not running; open Word with CreateObject Set appWord = CreateObject("Word.Application") Resume Next Else MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End If End Sub
If you have worked with MultiSelect listboxes on Office UserForms, you will appreciate the ItemsSelected collection available in Access VBA, which lets you process just the selected items, instead of checking for the Selected property being True for each item in the listbox, as you have to do elsewhere.
Command Button
Command buttons are used to run code; they are often placed in form header or footer sections to run procedures such as closing the form and returning to the main menu, printing a filtered report, or opening another form filtered by the record selected on the main form. The command button event procedure listed below is the standard Close button event procedure I use in most of my databases; it closes the current form and opens a main menu called fmnuMain, if there is a main menu (otherwise, it just closes the form). The procedure uses the IsLoaded property of a form in the AllForms collection to check whether the main menu is loaded or not; if it is loaded, it is made visible; otherwise, it is opened. The error handler takes care of the case where the database doesn’t have a form called fmnuMain.
Private Sub cmdClose_Click() On Error GoTo ErrorHandler Dim prj As Object Set prj = Application.CurrentProject If prj.AllForms("fmnuMain").IsLoaded Then Forms![fmnuMain].Visible = True Else DoCmd.OpenForm "fmnuMain" End If ErrorHandlerExit: DoCmd.Close acForm, Me.Name Exit Sub ErrorHandler: If Err.Number = 2467 Then Resume ErrorHandlerExit Else MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End If End Sub
Image
The image control displays an image, which is selected for its Picture property using the Insert Picture dialog. You can assign an image from VBA code, as an alternative to displaying an embedded image with a bound object frame. If you need the same image (for example, a logo) for all records, an image control is perfect. If you need to display a different image for every record (say, a photo of a toy), you can save space in the database by using an image control and writing the file name to its Picture property from the form’s Current event.
Compared to using a bound object frame, this method saves space in the database, but it also introduces the possibility of problems with file references—if the image file has been moved, deleted, or renamed, you will get an error when trying to assign it to the Picture property. You will probably see this problem when you navigate from record to record on frmImageControls in the sample Toy Workshop database, unless you happen to have the images referenced in the table in exactly the same location as on my computer.
frmImageControls has an image control in the form header, and a bound object frame in the Detail section, which changes its image as you navigate from record to record. It also has a combobox for selecting a picture file name; after selection, the picture is loaded to the Picture property of another image control below the combobox. This form is shown in Figure 3.11.
Figure 3.11
The code for loading the second image control with the selected picture follows.
Private Sub cboImageName_AfterUpdate() On Error GoTo ErrorHandler Dim strImageFile As String strImageFile = Nz(Me![cboImageName].Column(1)) If strImageFile = "" Then MsgBox "Please select an image" GoTo ErrorHandlerExit Else Me![imgSelected].Picture = strImageFile End If ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
Unbound Object Frame
An unbound object frame holds an OLE object—not just an image, but any type of object that supports embedding, such as an Excel worksheet or Word document. Since it is not bound, the same object is displayed on any record of a form.
Bound Object Frame
A bound object frame displays an OLE object (such as an image, an Excel worksheet, or a Word document) that has been embedded into an OLE Object field in a table. The OLE image control on frmImageControls is of this type; you can see the image change as you navigate through the records on this form. I recommend using this control just to store images; if you need to work with a Word document or an Excel worksheet, it’s best to just open it in its own window, using Automation code. (See Chapters 11 through 13, which deal with Word, Outlook, and Excel data exchange, for more information on working with Office documents using Automation code.)
Subform/Subreport
The subform/subreport control is used to display data from one form (the subform) on another form (the main form). Frequently, a main form displays data from the “one” side of a one-to-many relationship, and a subform displays data from the “many” side of the relationship. The “Creating and Embedding Datasheet and Single Form Subforms” section of Chapter 2, Using Forms to Work with Data, has several examples of subforms used to display “many” data. Subforms can also be used to display unlinked data for reference on the form. As an example of this technique, the invisible subform subMaxEmployeeID on frmEmployees in the Toy Workshop sample database has a single textbox that contains the value of the highest EmployeeID used so far, this value is used to create an EmployeeID with the next highest number when a new employee record is created.
Line
The line control is just a line you can place on a form to separate groups of controls—frmImageControls has a line between the sections of the Detail section with a bound image frame and an image control.
Rectangle
The rectangle control is useful to indicate that a group of controls belong together, such as a group of checkbox controls.
Not-So-Useful Standard Controls
Not all of the controls on the Toolbox are useful, at least in Access 2000 or later. One should have been dropped a few versions ago, and another never was very useful to begin with.
Page Break
The page break control was once useful—in Access 1.0, this was the only way you could divide a form into pages for displaying large amounts of data. You’ll still see forms with page breaks in the sample Northwind application that comes with Access (presumably carried over from version to version since Access 1.0). However, since the addition of the tab control as a standard control in Access 97, it is no longer necessary to use page breaks to convert a long form into pages; the tab control offers a much more intuitive and easy-to-use method of dividing up large amounts of data on a form.
There are some special cases in which a page break control may be useful for reports, however.
Toggle Button
The toggle button can be used either as a standalone control or as a toggle button in an option group. A toggle button has two states: raised (indicating True or Yes) and depressed (indicating False or No). The fact that I had to explain these states is part of the problem with this control: it is not, by any means, apparent to users what it means for a toggle button to be raised or depressed. Since toggle buttons look just like command buttons, most likely a user will click a toggle button and expect something to happen, assuming that it is a command button.
For a standalone control that indicates True/False or Yes/No, I recommend using a checkbox; everybody understands that a check in the checkbox means Yes (or True), and the lack of a check means No (or False). For option groups, the option button is best, since again it is intuitively obvious that the dark option button is the one that is selected. Both the checkbox and option button have analogs in paper-and-pencil procedures: checking a box on a form or darkening a circle on a multiple-choice test. The toggle button has no such analog, so I would advise not using it at all.
EAN: 2147483647
Pages: 124