How to Access a Query List Box

Learn how to use a list box control in Access using VBA.

Knowing how to set the results of a query to a list box control and use its values in another query can make your database applications more dynamic. Microsoft Office Access allows you to create a graphical user interface (GUI), add a list box control and use it to display query data. Access is a relational database management system commonly used for smaller databases. A query is used to retrieve data from a database table. Visual Basic for Applications (VBA) is a computer programming language used to automate routine tasks.

Instructions

1. Open the Northwind Microsoft Office Access database. The Northwind database is included in the Microsoft Office suite.

2. Click the "Create" tab and select "Form Design" to create a new form. Click "List Box" on the "Controls" pane, then click on your form, hold the mouse button and drag your mouse to create a new list box control. Click "Button" on the "Controls" pane to add a new button to your form the same way you added the list box.

3. Right-click "Command0" and select "Properties." Click the "Event" tab and select "[Event Procedure]" next to "On Click." Click the ellipsis (...) to open the "Microsoft Visual Basic" window.

4. Copy and paste the following code under "Private Sub Command0_Click()":

   Dim strSQL As String

   strSQL = "SELECT Orders.[Employee ID], "

   strSQL = strSQL & "Orders.[Customer ID], Orders.[Shipped Date], "

   strSQL = strSQL & "Orders.[Ship Name], Orders.[Ship Address] "

   strSQL = strSQL & "FROM Orders "

   strSQL = strSQL & "WHERE (((Orders.[Ship Address])='123 8th Street'));"

   Me.List0.RowSource = strSQL

   This code will query the "Orders" table and set the "RowSource" property of the list box control.

5. Switch back to the Microsoft Access window and right-click "List0," then select "Properties." Click the "Event" tab and select "[Event Procedure]" next to "After Update." Click the ellipsis (...) to open the "Microsoft Visual Basic" window.

6. Copy and paste the following code under "Private Sub List0_Click()":

   Dim dbs As Database

   Dim rst As Recordset

   Dim SQLstr As String

   Dim I As Integer

   Set dbs = CurrentDb

   For I = 0 To Me!List0.ListCount

   If Me!List0.Selected(I) Then

   SQLstr = "SELECT Orders.[Shipped Date] FROM Orders "

   SQLstr = SQLstr & "WHERE Orders.[Employee ID] = " & (Me!List0.ItemData(I)) & ";"

   Set rst = dbs.OpenRecordset(SQLstr)

   rst.MoveLast

   rst.MoveFirst

   Debug.Print "Shipped Date for item selected: " & rst.Fields(0).Value

   rst.Close

   End If

   Next I

   dbs.Close

   This code will display the "Shipped Date" for items selected in the list box. The results will be displayed in the "Immediate" window.

7. Close the "Microsoft Visual Basic" window and right-click "Form1," then select "Form View." Click "Command0" to fill the list box with data. Select a value from the list box to execute the code for the list box control.

References

• Microsoft: ItemsSelected Property