Access Cookbook - Ken Getz [293]
Each text box on the form has a FilterLookup property that has three settings:
Never
A combo box list will contain only two items: Is Null and Is Not Null.
Always
A combo box with a full list of values will be created for that text box.
Database Default
Access will populate the list either with all the values or with only Is Null/Is Not Null, depending on the settings in the Edit/Find tab of the Tools → Options dialog shown in Figure 14-9.
Figure 14-9. Database options that affect ServerFilterByForm
Follow these steps to change the database defaults to always show a list of available items when using ServerFilterByForm:
Open the database whose options you want to change.
Choose Tools → Options from the menu.
Check the "Records at server" option shown in Figure 14-9.
If you don't want to change this option globally, followthese steps to set the list of values on a form-by-form basis:
In design view, open the form in which you want to enable a full list of values for ServerFilterByForm. Select all the text boxes you want to enable.
Set the FilterLookup property on the Data tab in the properties sheet to Always, as shown in Figure 14-10.
Figure 14-10. Set the FilterLookup property for the control on a form
When you open the form to filter records now, you'll see some real data in the combo boxes, as shown in Figure 14-11.
Figure 14-11. The combo boxes now display real data
Discussion
The data to populate the combo boxes must, of course, come from the server. Setting the FilterLookup property for the controls or setting the "Records at server" option for the entire project runs additional queries that populate each combo box with a domain of real values from which the user can choose.
TIP
When you set the ServerFilter property on a form, regular filtering with the form's Filter property is unavailable.
Because populating combo boxes with real values entails extra round trips to the server to retrieve the data for the lists, it defeats the purpose of using the ServerFilter property in the first place, so don't overuse this feature. The benefit is that the interface is more user-friendly when the user can select from actual values instead of guessing.
You'll have to evaluate your own applications to determine whether or not the extra data filtering is worth the extra load on the server. If the form would otherwise load a lot of records, and if the lists you are loading aren't too big, you would probably improve performance by using ServerFilterByForm. To be safe, adjust the ServerFilterByForm setting at the control level rather than by setting the database default for the entire project.
14.5. Pass Parameters to Stored Procedures from Pass-Through Queries in an MDB
Problem
You are calling stored procedures that require parameters by using pass-through queries. How can you pass parameters to the pass-through query from your form? If you include a reference to the form in the pass-through query, you get an error message from SQL Server.
Solution
Pass-through queries are not processed in the same way as regular Access queries against linked tables. The SQL syntax you type in a pass-through query is passed directly to SQL Server. Any references to forms or controls on forms in a pass-through query are meaningless to SQL Server, so you must pass the actual values for your parameters.
A pass-through query has three important properties:
SQL
The SQL property contains the textual content of the pass-through query. This must be a valid Transact-SQL statement.
ODBCConnectStr
The connection string contains information that the query uses to connect to SQL Server. You can specify a DSN, or use a string containing all the requisite connection information, as shown in the Solution in Recipe 14.1.
ReturnsRecords
The ReturnsRecords property specifies whether or not the query returns records. An action query that just modifies data without retrieving anything would have this property set to No or False.