<

Synchronize List Box in Access using SQL and VBA

Posted on
6,388 Points
388 Views
Last Modified:
Experience Level: Intermediate
10:51
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
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 code or looked at an SQL statement before, no problem! ...  give it a chance. Unleash a great new potential.

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.  

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;

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, and copy the SQL statement, 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.  

When the customer changes, VBA reads the customer to construct a Where clause. Both list boxes have a source with CustomerID (Long Integer).  For the customer products, CustomerID is in the Orders table. For the payments, CustomerID comes from the PayGroups table.
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.

The code shows how to use the control (With) to read the Tag (saved by developer) to get the SQL statment without criteria, add criteria, replace the Row Source with the new SQL statment, then build the list to show the latest data.

See how to create code behind the form, and how to call one procedure from another. Learn line-by-line what is happening.  This knowledge will propel you to a new level, and raise the bar on database applications that you build.

have an awesome day,
crystal

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

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

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

Video Steps

1. download the database ACCDB example:


 Synchronize-List-Box.zip

2. go to the design view of the form

3. set the RowSource of a list box to an SQL statement that shows all records

4. copy the Row Source SQL statement to the Tag property

5. write a procedure in the VBA code behind the form to synchronize the list boxes


  •      read SQL statement without Where clause from the Tag
  •      replace ORDER BY with the Where clause and then ORDER BY in the SQL
  •      set the Row Source to the modified SQL statement
  •      requery the list box

6. call the synchronize code in the form Current event

7. Mainform + Subforms video:


https://www.experts-exchange.com/videos/9818/Mainform-Subforms-designed-for-efficiency-and-flexibility.html      

8. Subform to show Calculations video:


  https://www.experts-exchange.com/videos/9937/Subform-to-show-Calculations-in-Microsoft-Access.html
0
0 Comments

Featured Post

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Best Disaster Recovery Plans for Exchange 2016, 2013, 2010 environment. Step by Step guide for Exchange Recovery of Server, Database and Mailbox Level after Disaster (Corruption and accidental deletion) recovery using PowerShell Commands and automat…
Do you need a date time picker, how about just a time picker?  Would you like to use something other than the basic Access date picker which is available when you have a textbox which contains data formatted as a date? Give this date time picker a t…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month