Synchronize List Box in Microsoft Access using VBA to change SQL

Sign in to queue

Description

See how to to change the SQL for the RowSource of a list box using VBA (Visual Basic for Applications). Raise the bar on database applications that you build with Microsoft Access.

With just a little bit of SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form. If you have never written VBA code or looked at an SQL statement before, no problem! ... give it a chance. Unleash the power of Access.

This technique of basing the Row Source of one control on the value in another is called "cascading", and you will probably hear that term more in reference to combo boxes.

A list box has many similarities to a combo box such as Row Source, Column Count, Column Widths, and Column Heads. The Width of a list in a combo box is List Width; in a list box, it is the same as the control Width.

As the customer changes, VBA runs to add criteria to the SQL statement for the list box. SQL is what a query stores to know what data to get, where it comes from, and how to sort. SQL is Structured Query Language. Don't let the acronym intimidate you.

SQL statement

An SQL statement is simply a standardized way to get information from database tables. It specifies what to show (Select), and where data comes from (From). Optional clauses include criteria (Where), and how to sort (Order By). The basic syntax for an SQL statement is:

SELECT fieldlist
FROM tablename
WHERE criteria
ORDER BY fieldlist;

Row Source

To get an SQL statement into the Row Source of a combo box or list box, you can: (1) Make a query to show what you want, switch to SQL view, copy the SQL statement, paste into the Row Source or (2) click in a control's Row Source property, then on the Builder button (...), specify what you want in the query builder, then save, and close the builder, or (3) write the SQL yourself.

To make the demonstrated code work, copy the resulting SQL statement from the Row Source to the control's Tag property (bottom of Other tab on property sheet). Tag is not used by Access; it is a place where you can put whatever you want. In this case, it will store the SQL statement with no Where clause for each respective control. The code will read the basic SQL from the control's Tag property and then modify it to add criteria (Where clause).

When the focus moves to a record, it becomes the current record, and the form Current event happens. On the property sheet, this is called On Current and can be set to a macro name, a function name, or [Event Procedure].

The shortcut key to launch the Builder is Ctrl-F2.

VBA code

Look at the code behind the form (class module). See how to call one procedure from another (so code can be encapsulated). Use the the control Tag (for whatever you want) to get the SQL statement without criteria, then add a Where clause (criteria), replace the Row Source with the new SQL statement, and build the list to show the latest data (requery).

Learn line-by-line what is happening in the VBA code -- and see how to efficiently modify an SQL statement to limit the rows it returns. This knowledge will propel you to a new level.

~~~

related videos:

Mainform + Subforms for data from mutiple tables with a Microsoft Access User Interface 
channel9.msdn.com/Blogs/MVP-Office-Dev/Mainform--Subforms-for-data-from-mutiple-tables-with-a-Microsoft-Access-User-Interface

Subform to show Calculations in Microsoft Access 
channel9.msdn.com/Blogs/MVP-Office-Dev/Mainform--Subforms-for-data-from-mutiple-tables-with-a-Microsoft-Access-User-Interface

have an awesome day, 
crystal

Access Basics (free book) www.AccessMVP.com/strive4peace Free 100-page book that covers essentials in Access

Learn VBA (free book) www.AccessMVP.com/strive4peace/VBA.htm

connect to me, let's build it together www.MsAccessGurus.com

Embed

Download

Download this episode

Download captions

The Discussion

Add Your 2 Cents