Skip to content

Displaying More Information about ListBox Items, Part 1

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

About this author