What happens when you save this query is interesting. Access evaluates what you have entered in the query grid and automatically saves it in the recordsource as an SQL query.
This brings up several other possible uses for Access SQL. The first is that you can type the SQL directly into the recordsource instead of going to the query builder. Sometimes this is a far faster way of entering the recordsource. Second, if you have a form that already has the recordsource that you want to use for your current form, copy the SQL code from the first form and paste it into the second form. This is often much quicker than building a query from scratch.
Sidebar: The concept of viewing the query as an SQL statement also gives the designer a great degree of flexibility that does not exist in the query grid. If you wish to take a recordsource from one form and copy it to another form but have the additional complication of having the recordsource being a different but similar table, you can copy the SQL string into a text editor such as Microsoft Word. You can then use search and replace to change the initial table to the new table using the global search and replace feature. This is extremely useful if you have a very complex query and don’t wish to recreate everything. A quick search and replace followed by cutting and pasting the result back into Access completes the operation. Unfortunately, Access does not have a convenient way of doing this. The Access text editing capabilities are more primitive than even those of Notepad.
Filtered Recordsets for Forms
Now that we have shown that the recordsource of a form is just an SQL statement, we can make the leap to dynamically set up the query as needed. Dynamic queries are extremely useful when working with a filtered recordset in a form or report.
Just like with every other task in Access, there are many ways to filter a recordset. First, you can enter the filter when opening a form. We will demonstrate this with the frmCustomers form in the sample database (see Figure 14-4). Selecting a record on the Customers 原文请找腾讯752018766优,文-论~文;网http://www.youerw.com for that customer. If there is only one transaction, frmTransactions will only have one record to display. If there is more than one transaction, the user is able to move forward and backward through the records.
So much for the overview — now we will get into the fun stuff! The first button, Open with filter, is the equivalent of setting the filter parameter of the recordset to the entered value. Take a look at the code behind this button:
In effect, this code opens the frmTransactions form and sets the Filter property of the form. In the example above, when the user selects the customer with the ID of 1, the filter string “customerID =1” is placed into the Filter property
The one problem with this approach is that you generally want to empower the user to perform additional filters with the data. If the user enters a new filter via the Filter button or through filter by form, the new filter will overwrite the one you had carefully built and will change the list of records displayed with no obvious way to get back to the initial filter set. For example, selecting “filter by selection” when the date sold of 2/8/2003 is selected produces the filter shown in Figure 14-6. You get the records that you want but you have to tread on shaky ground
If the user removes the filter to try to go back to the full set of pertinent records, the results are not what is expected. The resulting display will have all the records, not just the ones that meet your original customer filter, as shown in
Let’s take a step back for a moment and look at this problem in a bit more detail. You really don’t want the user to be able to view the full set of records, no matter what filters he chooses to set up. You want the user to only have access to the records you want him to see. This is best accomplished by setting the recordsource of the form to a recordset that only has the values that you want.
Looking at the properties of our form, notice that the Recordset property is either a table or a query expressed as an SQL statement. We have the ability to change this property and when doing so we can change the collection of records that the form uses. We do this by opening the form, then setting the Recordsource property to a filtered query. Since the form is not filtered by the use of the Filter property, clearing the filter will not affect our dataset. The user is limited to the records we give him permission to view in the recordsource.
上一页 [1] [2] [3] [4] [5] [6] [7] 下一页