How to Load Items from Table to Listbox




How to Load Items to Listbox

A Listbox is also well known like Combo box in the MS Access form. How to create a Listbox is similar to creating a Combobox. You can see more details on Working with Combo box on my website.  In this How To, I will show how to load the data from table into a list box when form is loaded.

Step 1. Add List Box

  • Under the Design Menu, click a List Box icon then place it on top of form
  • It will open the List Box Wizard below
  • Name list box as lstCustomer

 

Step 2. Cancel List Box wizard

–          Click Cancel button

 

Step 3. Add VBA to Form On Load Event

  • Click a black square on form property
  • Select Event tab
  • Select the [Event Procedure] of Form On Load Event

  • Enter VBA code below
Private Sub Form_Load()
Dim strCust As String
strCust = "select customer_id, customerName, address from [tbl customer] where Customer_type_id = 1"
Me.lstCustomer.RowSource = strCust
End Sub

Note: For this example, I want to load the CustomerID, Customer name, and address only for the customer type = Individual (or customer type id 1) into the listbox when form is loaded.

Step 4. Format Listbox

  • Click listbox
  • Select Format tab of listbox property sheet
  • Enter 3 for Column Count (Customer_ID, CustomerName, and Address)
  • Update Column Widths as 0;2;1 since I don’t to display the CustomerID on the list then I set the Column Width =0
  • Select Yes for Column Heads to show the title of each column on the list box

 

Step 5. View form

When this form is loaded, the list box will display the customer name with address. The column head is showing as CustomerName and Address from the column field name. The Customer_ID column does not display in the listbox because the width of Customer_ID column is 0.