• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

How to Display SQL Query Data with Listbox in Access 2010 with VBA

February 4, 2014 by Ryan McCormick 3 Comments

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

  1. Save your form. Mine is called “main”.
  2. Right-click on the form tab and select “Design View”
    MS Access 2013 Form Design View
  3. Select your listbox control and drop it on your form
    Listbox Control in Access 2013
  4. 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”.
  5. If you are unsure if this has been selected. Right-click on your list control and select “Properties”
  6. In the properties menu, find “Row Source Type”. Select “Value List”.
  7. 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:

  1. left-click the little icon on your quick access bar and select “More Commands…”
    Quick Access Bar in MS Access 2013
  2. From your drop-down select “All-Commands”, scroll down to Visual Basic, and Click “Add”
  3. Click “Ok” at the bottom right and you should have a new icon on your upper left bar called “Visual Basic”

Add the code

  1. Right-click on your ListBox Control and Select “Build Event”
  2. When Prompted, select “Code Builder”
  3. 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.
  4. Select “Insert” Menu and select module. Save your module.
  5. 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

  1. Right click on your form and select “Build Event”. Select Code Builder.
  2. On the left-top dropdown, select your form. On the right-top dropdown, select Load
  3. Add this code:
Private Sub Form_Load()
    'call phone sub routine
    Call showPhone
End Sub

Select Form View

  1. 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.
    form-data

Key Takeaways

  1. 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.
  2. Please comment with questions, suggestions, improvements.

Related

Filed Under: Microsoft Access Tagged With: access 2010, access 2013, listbox control with SQL query, vba

Reader Interactions

Comments

  1. Brian Magaw says

    August 24, 2015 at 5:57 pm

    What does Form.main.1stPhone refer to?

    Reply
    • Ryan McCormick says

      September 20, 2015 at 9:38 pm

      Hello Brian, the code ‘lstPhone’ portion starts with lowercase ‘L’. I am revamping this blog and will be posting example files with all of my code updates. Did you get your situation figured out?

      Reply
  2. Craig Hillemann says

    December 21, 2016 at 1:03 pm

    Wonderful example. Thank you.
    I found with this example that if the table field contains “Smith, Bob” instead of “Bob Smith” then the listbox displays only “Smith” unless another column is added, which will then display “Bob”. The comma in the data is acting as a delimiter for listbox display. This may be operable for my purposes. However, can the code be modified so that “Smith, Bob” is displayed in a single column?

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in

 

Loading Comments...