Filtered Recordsets for Combo Boxes
The second place that dynamic recordsets are commonly used is in combo boxes. In many cases you may want the combo box to have varying data depending upon the value of an option group. Take a look at the next form from the sample database, frmSelectCustomer.
In this example, we want the contents of the combo box to be sorted by the selected option. We also want the combo box to reflect the choice of the option group. We do this by setting the Rowsource property of the combo box programmatically during the after update event based on the value of the option box. Depending upon the option selected, the program will set up the rowsource to sort by the selected field.
Examine the difference in the combo box when different options are selected. The differences in the values for the combo box are a direct result of the SQL that is placed behind the rowsource of the combo box.
Recordsets for Subforms
The third major use of dynamic SQL statements in code is the building of the recordsource for subforms. There are three major reasons for using SQL to change the recordsource of subforms. The most common reason is identical to that of filtered recordsets for forms — it gives you control of the recordset and eliminates the possibility of the user disturbing your filter through manual action.
The second reason for using SQL to change the recordsource of a subform parallels the idea of dynamically changing the rowsource of a combo box. Sometimes the programmer needs to have different data in the subform based on other decisions made on the form. If the visual appearance of the subform does not need to change to reflect the different data, it is often advantageous to save time and effort by using one subform designed to hold both types of data and just changing the recordsource of the subform. Look at the following form, frmPhoneNumbers.
Depending upon the user’s selection of customer phone numbers, employee phone numbers, or a combination of the two, the appropriate code is built and saved as the recordsource of
the subform. This is far simpler and easier to maintain than it would be if there were three subforms, one for each of the options. Using this approach, if you need to change the layout of the subform or add additional information to it, you will only have to do your changes in one spot rather than three.
Let’s examine the code behind this form. The key to this example is the code behind the after update event for the option box.
In this example, we have first defined a variable to hold the SQL string. We could just set the recordsource directly to the string, but adding the intermediate variable is a good idea, as it helps facilitate debugging mistakes in the SQL code. It is far easier to debug the SQL statement if we have it in a variable that can be pasted and analyzed in the debug window.
The Microsoft content pop-up generally is not big enough to hold the entire SQL string and it goes away before you can really tell what has happened. 原文请找腾讯752018766优,文-论~文;网http://www.youerw.com happening when the code executes, you will note that the number of records in the recordset changes to reflect the counts for the two groups of phone numbers and the combined list of phone numbers.
The real power to this approach is evident the moment you need to modify the layout of the subform. If you need to increase the width of the Lastname field, you can do it in one place. If you had three subforms, you would have to carefully resize each of the Lastname fields in each of the forms to ensure that they are the same size and carefully move the PhoneNumber fields so each is in the correct location. Otherwise, the form would “jump” on the screen as the different options were selected. It is far simpler to have a single form.
There is one other reason for not including the recordsource of a subform at form construction time but instead setting it during the running of the form — speed. If a form has multiple subforms on it, there will be a certain amount of processing time needed to fill each of the subforms. The more subforms on the main form and the more record searching that is required to calculate the form, the more time it takes for the form to be displayed. If instead of filling in all the recordsources of the subforms when the form is opened, the recordsource
上一页 [1] [2] [3] [4] [5] [6] [7] 下一页