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.
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 (').
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.
February 2018
