Synchronize List Box in Access using SQL and VBA

Armed with a little knowledge 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. hi, this is crystal If you have never written VBA code or looked at an SQL statement before, no problem; it makes sense — so give it a try. This is a form with a customer. There are also two list boxes that display information. The top listbox shows all the products that the customer has purchased. the bottom listbox shows the customer’s payment history. What displays in a list box is specified by a property called the Row Source. A Row Source can contain a table name, query name, or an SQL Statement. Additional important properties are Column Count, which specify how many columns there are, Column Widths, which is a string specifying how wide each column is, and List Width, which is how wide the list is. A list box has many similarities to a combo box. Each time the customer changes, the list boxes change to show rows relating to that customer. One way to do this is to modify each list box’s SQL statement to add a Where clause to limit the rows to a particular customer. SQL is Structured Query Language, and is what Access stores when you make a query. 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 or tablenames with join information 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 (…), and specify what you want in the query builder, then save, and close the builder, or (3) write the SQL yourself. Copy the resulting SQL statement from the Row Source to the control’s Tag property, which is at the bottom of Other tab on the 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, which is a 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]. An Event Procedure is code that is stored behind the form. Here, it is used to change what is displayed in list boxes. To define, click in the form On current property, and then on the Builder Button (…) or press Ctrl-F2. When prompted, choose the Code Builder and then OK. Access supplies the procedure declaration statement, “Private Sub Form_Current()” and the ending statement, “End Sub”, so you can put whatever you want to happen in between. Because I just deleted [Event Procedure] from the property sheet, code was not hooked up so it would not have run. By selecting the Code builder, Access will make a new procedure, unless, like in this case, it is already there, in which case, it will put you where the code is. Because synchronizing the customer lists is something that will also run when a customer is picked or changed, it is defined in its own procedure and run when needed. The resulting VBA code for the form current event is: Private Sub Form_Current() ‘call procedure to synchronize customer listboxes Call SynchronizeCustomerLists End Sub SynchronizeCustomerLists is a procedure that is also in the code behind the form, and is what actually changes things. For each control, the SQL statement (without a Where clause in the Tag) is modified to add the Where clause, and then the Row Source is replaced, and the list is rebuilt. The SQL statements have Select, From, and Order By clauses, but do not have a Where clause. In the syntax, Where comes before Order By, so the Replace function can be used to add the Where clause just before Order By. Comments are prefaced with a single quote mark (‘), and are colored green. The first line of the procedure is the declaration. Private means its scope, or what can see it, is limited to the module it is in. This module is a class module and is stored behind the form. It is also called “code behind form”. When there is a single quote mark, the rest of the line is interpreted as a comment and the line is colored green. Comments will not be interpreted by the compiler, so you can say anything you want. Th name of this procedure is SynchronizeCustomerLists and it is a subroutine, which means that it does not have a return value like a function. It can be called by any other procedure in this module. The first couple lines are comments. This is a brief description of what the procedure does, and this is the date/time and who wrote it. At the top of the module is “Option Explicit”. This means that all variables must be declared. That is, specify names for what you intend to use. This procedure will construct SQL statements, so there is a string variable called sSql. It will also construct Where clauses for different situations. In this case, the Where clause for CustomerID using the PayGroups table is constructed, as well as the Where clause for CustomerID using the Orders table. The variables are then initalized to a starting value. With the customer control, if the value is filled out, IsNull will not be true, so nothing in the IF block will execute. Null means that there is no value. If there is a customer, the Where clause variables are modified. Now we look at the first listbox, customer products. The SQL statement without any Where clause is copied from the Tag. A Search and Replace is done. Access finds the Order By keyword, and replaces it with the Where clause and then the Order By keyword again. Now that the SQL statement is modified, it is written to the RowSource for the list box. Then the list box is requeried to rebuild the rows according to the latest data. The second listbox shows payments and follows the same process. If you had another control that depended on customer, code to calculate or requery could be added here as well. In this lesson, you learned how to modify the Row Source of a list box by using VBA to change the SQL statement. Thanks for joining me. Through sharing, we will all get better.

Tags: , , , , , , , , , , , , , , , , , , , , , , , , ,


Leave a Reply

Your email address will not be published. Required fields are marked *