Software

Linking multiple list boxes in Access

Access' list boxes are handy for referencing particular records, but they can quickly get out of control. Here's how to link certain list boxes together, eliminating the need for time-consuming searching through too much information.

List boxes make it easier to find specific records in Access. For example, you can add a Client Name list box to your Client Records form that lets your salespersons search for their clients' records by name. If you have hundreds of clients, however, this list can get unwieldy. To limit the search list, you can link the Client Name list box to a Salesperson list box, so the salespersons only need to search through a listing of his or her clients. Follow these steps to link the list boxes:

  1. Open the Client Records form.
  2. Click the List Box tool in the Toolbox and click and drag to create the Salespersons list box in the Forms Header. Click Next.
  3. Click Table:Salespersons and then click Next.
  4. Click Salesperson ID and then click the > button.
  5. Click FirstName and then click the > button.
  6. Click LastName and then click the > button. Click Next.
  7. Click Next twice again.
  8. Enter Assigned Salesperson: in the Label text box and click Finish.
  9. Click the List Box tool in the Toolbox and click and drag to create the Client list box in the Forms Header.
  10. Click Next.
  11. Select Table:Client Records and then click Next.
  12. Select Client Number from the Field list and click the > button.
  13. Select Name from the Field list and click the > button.
  14. Select Salesperson ID from the field list and click the > button.
  15. Click Next twice.
  16. Enter Client: in the Label text box and click Finish.
  17. Right-click the Assigned Salesperson: list box and select Properties.
  18. Click the All tab.
  19. Click the Name box and enter cboSalesperson.
  20. Right-click the Client list box and select Properties.
  21. Click the All tab.
  22. Click the Name box and enter cboClient.
  23. Click the Build button in the Row source property and enter the following in the Criterion cell of the Salesperson ID field: Forms!ClientRecords![cboSalesperson]
  24. Click the Build button in the AfterUpdate event property and then select Code Builder.
  25. Enter the following code at the prompt:

    DIM strSearch As String

    strSearch="[ClientID]=" & Chr$(39) &Me![cboclient] &Chr$(39)

    'Find the record that matches the control

    Me.Requery

    Me.RecordsetClone.FindFirst strSearch

    Me.Bookmark = Me.RecordsetClone.Bookmark
  26. Press [Alt]Q.
  27. Right-click the Salesperson list box and select Properties.
  28. Click the Event tab.
  29. Click the Build button in the AfterUpdate event property and then select Code Builder.
  30. Enter the following code at the prompt:

    Me![cboClient] = Null

    Me![cboClient].Requery
  31. Press [Alt]Q.
  32. Close and save the form.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox