Sometimes I like to use the listbox control in Access to display information. Partially because I like the look of it and partially because it offers up a better user experience. You will note that the title references Access 2010 where my screenshots see were taken in 2013. I use 2010 on my work computer and 2013 on personal development PC. This works on both.
For this example, I created a simple three column database table (id, name, phone) and added three names with phone numbers.
Create a blank form and set it in design view
- Save your form. Mine is called “main”.
- Right-click on the form tab and select “Design View”
- Select your listbox control and drop it on your form
- You should see a few prompts asking you what type of listbox you want. Select the option that says “I want to enter in my own values”.
- If you are unsure if this has been selected. Right-click on your list control and select “Properties”
- In the properties menu, find “Row Source Type”. Select “Value List”.
- Under the properties menu again find “name” and name your list. I used “lstPhone” for this demo
Start writing VBA automation for your List Box Control
In this demo, you will be referencing your Visual Basic IDE a lot. If you don’t have a quick access button, I suggest adding one. If you got it, skip this step. For the rest of you:
- left-click the little icon on your quick access bar and select “More Commands…”
- From your drop-down select “All-Commands”, scroll down to Visual Basic, and Click “Add”
- Click “Ok” at the bottom right and you should have a new icon on your upper left bar called “Visual Basic”
Add the code
- Right-click on your ListBox Control and Select “Build Event”
- When Prompted, select “Code Builder”
- I used a public sub in a module for this exercise. This can be called to load list data on form load or after adding, deleting or editing objects.
- Select “Insert” Menu and select module. Save your module.
- Browse/use this code:
Public Sub showPhone() Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String Dim listHead As String, listItem As String 'set database Set db = CurrentDb 'define SQL query strSQL = "SELECT ID, Name, Phone FROM names" 'open query Set rs = db.OpenRecordset(strSQL) 'clear list data before refresh Form_main.lstPhone.RowSource = "" 'define the amount of columns Form_main.lstPhone.ColumnCount = "3" 'enable headers Form_main.lstPhone.ColumnHeads = True 'set column width Form_main.lstPhone.ColumnWidths = "0;2500;1500" 'define headers with semi-colon seperator listHead = "id;Name;Phone Number" Form_main.lstPhone.AddItem listHead 'display items from query Do While Not rs.EOF 'define variable for listitems listItem = rs.Fields(0) & ";" & rs.Fields(1) & ";" & rs.Fields(2) Form_main.lstPhone.AddItem listItem 'loop through records until EOF rs.MoveNext Loop 'garbage collection db.Close Set db = Nothing Set rs = Nothing End Sub
Go Back to Access and select your form
- Right click on your form and select “Build Event”. Select Code Builder.
- On the left-top dropdown, select your form. On the right-top dropdown, select Load
- Add this code:
Private Sub Form_Load() 'call phone sub routine Call showPhone End Sub
Select Form View
- When browsing back to your form, right-click on the tab and select “form” view. If everything worked the way it should, you should see your list in action with your data.
- In the form you only see two columns, but in my code example, I queried and set to display three columns. The first column contains the ID for each row of data displayed. This is important for referencing items in the list to open an edit box, delete an item, or reference more information about each list item.
- Please comment with questions, suggestions, improvements.