|

Access: Change a List Box Using VBA

By Crystal Long
February 1, 2018
0 comments

One of the nice features of a list box control on a form is that its content can be changed dynamically depending on data in other controls on the form. With a form used to enter customer payments, for example, separate list box controls can be created to show a selected customer’s payments and the products that customer purchased.

 

The content of a list box is defined by its Row Source, and its data is displayed in rows and columns. Last month, we looked at using an SQL (Structured Query Language) statement. This month, we’ll look at how to use VBA to modify an SQL statement so that a WHERE clause is inserted based on a specific parameter—in this instance, the current customer.

 

VBA stands for Visual Basic for Applications, and it’s used to automate Microsoft Office products. VBA is the Visual Basic language supplemented with references for Application objects, such as tables and fields, queries, forms, reports, and controls in Access.

 

When something happens in Access, like a form opening (Form_Open) or data being loaded (Form_Load), it’s called an “event.” If your form has command buttons that a user might click to activate something, it’s called a Click event.

 

When the focus moves to a particular record on a form, the Form_Current event happens and that record becomes the current record. The Event tab of the property sheet includes a property called On Current. It can be set to a macro name, a function name, or [Event Procedure]. If something is specified in that property, that logic runs when a record becomes current.

 

Figure 1 shows the customer form, named f_PAYMENTS, in Design view. The form object is selected, which is indicated by the black square in the upper-left corner where the rulers intersect. The Property Sheet for the form shows that event procedures are defined for On Current, On Load, and After Update events.

 

02_2018_Access--figure 1

 

The form contains list boxes, including listCustomerProducts, which shows the products a customer has purchased, and fnd_PayDate_Customer, which shows payments and also finds a specific record. The customer is indicated in a control called CustomerID.

 

Open the Property Sheet and choose Form in the dropdown. Go to the Event tab and click the Builder Button. This will display the VBA code for the Form_Current event:

 

Private Sub Form_Current()

‘call procedure to synchronize

customer list boxes

Call SynchronizeCustomerLists

End Sub

 

Using “Private Sub” means that only objects in this class—the f_PAYMENTS form and its code—can see this particular procedure (i.e., function or subroutine). “Call” means execution will be passed to another procedure. In this case, it’s SynchronizeCustomerLists, which also is in the VBA code behind the form.

 

Because synchronizing the customer lists is something that will happen at other times as well, such as when a customer is changed, it’s defined in its own procedure that can be called whenever needed. The code can therefore be written more generically rather than specifically for the Form_Current event. The trick to making this work is that the SQL statement (which we discussed last month) is copied to the Tag property of the list box control. The Tag property is essentially just a place where developers can stick whatever they want or need.

 

The SQL statement we created doesn’t have a WHERE clause, but it does have an ORDER BY clause. The Synchronize–CustomerLists procedure uses the Replace function to insert a custom WHERE clause. See the procedure code below. Comments describing what’s going on are in green and prefaced with a straight apostrophe (‘).

 

Layout 1

 

Next month we will dive into user-defined functions (UDFs).

 

Download this month’s database: SF_1802_Using-VBA-change-ListBoxes.

 

 

SF SAYS

 

Use VBA to modify an SQL statement, such as for defining what appears in a list box when the Form_Current event occurs.

 

Crystal Long teaches and develops with Microsoft Access, Excel, and Office and specializes in remote training. She connects and helps as your project is built. Visit www.MSAccessGurus.com for information. Share your perspective! Add your comments about the article below.
0 No Comments

You may also like