How to show multiple lines of information about listbox items.
Access Archon #164
Introduction
Listboxes are a convenient way to select one or more items for action, such as creating Word letters or printing a filtered Access report. But sometimes you need to see more than a single line of information about an item, and there is no way to make listbox rows taller. This article describes how to show multiple lines of information about the selected items in a listbox, and to use the selected items to print either an Access Labels report or a set of Word letters.
The Contact Mailings Form
The sample database for this article, ListBox with More Info.mdb, has a form, frmContactMailings, with a listbox for selecting contacts, a textbox that displays the full name and address for the selected list item, and several buttons for selecting and deselecting listbox items, editing the main Contact record on another form, and creating either an Access report or a set of Word letters to the selected contacts.
The listbox (lstSelectContacts) has its MultiSelect property set to Extended, so that multiple items in the list can be selected using Ctrl-Click and Shift-Click. You can also select all the items in the list using the Select All button in the form footer, or clear all the selections using the Deselect All button.
Since the listbox only shows the Contact ID and contact name, you might want to see the full name and address as it would appear on a labels report or a Word letter; this information (from the concatenated NameAndAddress field) is shown in the txtNameAndAddress textbox. As you select items in the list, the name and address information for the last clicked item appears in this textbox, as shown in Figure A.
Figure A. The Contact Mailings form with the selected contact’s name and address displayed in a textbox
When you see the name and address, you may realize that it needs to be updated, so there is also an Edit Contact button that takes you to the matching record of the main Contacts form (frmContacts), so you can edit the contact record as desired. Figure B shows frmContacts opened to Dennis Alston’s record:
Figure B. The main Contacts form, filtered for a record selected in the listbox
When frmContacts is closed, frmContactMailings reopens, with refreshed data.
VBA Code
The code on the cmdAccessLabels button is simple: it calls the MarkSelected procedure and then opens the report in print preview:
Private Sub cmdAccessLabels_Click()
On Error GoTo ErrorHandler
Call MarkSelected
DoCmd.OpenReport “rptSelectedLabels5160”, acViewPreview
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Sub
The MarkSelected procedure iterates through the ItemsSelected collection of the listbox, and for each item, it sets the value of the Selected field in tblContacts to True for the matching record.
Private Sub MarkSelected()
On Error GoTo ErrorHandler
Dim strSearch As String
Set lst = Me![lstSelectContacts]
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblContacts”, dbOpenDynaset)
For Each varItem In lst.ItemsSelected
lngContactID = CLng(lst.ItemData(varItem))
strSearch = “[ContactID] = ” & lngContactID
Debug.Print “Search string: ” & strSearch
rst.FindFirst strSearch
If rst.NoMatch = False Then
rst.Edit
rst![Selected] = True
rst.Update
End If
Next varItem
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Sub
The cmdEditContact Click event procedure picks up the ContactID from Column 0 (the first column) of the listbox, creates a filter string, and opens frmContacts to the record with that ContactID.
Private Sub cmdEditContact_Click()
On Error GoTo ErrorHandler
Dim strFilter As String
Set lst = Me![lstSelectContacts]
lngContactID = Nz(lst.Column(0))
If lngContactID > 0 Then
strFilter = “[ContactID] = ” & lngContactID
DoCmd.OpenForm FormName:=”frmContacts”, _
View:=acNormal, _
wherecondition:=strFilter
DoCmd.Close acForm, Me.Name
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Sub
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch164.zip, which is the last entry in the table of Access Archon columns for Access Watch.
Document Name |
Document Type |
Place in |
More Info ListBox (AA 164).mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
White Paper — Adding Custom Doc Properties to a Word Template.doc |
Word document |
Wherever you want |
Test Letter.dot |
Word 97-2003 template |
User templates folder |