Access Cookbook | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Access queries—the six types that can be created on the easy-to-use query by example (QBE) grid, plus the three SQL-specific queries—give you a tremendous amount of power and flexibility in selecting, sorting, summarizing, modifying, and formatting the data stored in your tables before presenting it to the user on forms or printing it on reports. Access queries can be intimidating at first, but mastering queries will give you complete control over the appearance and functionality of your forms and reports. And Access queries are flexible—once you learn how to control them, you can use them in places where you might have written less efficient program code.

In this chapter you'll learn to create parameter queries, which allow you to control selected rows of a report at runtime rather than at design time. You'll use this same technique to control the available values in one combo box based on the choice in another. You'll study the ways to control the output of crosstab queries and will learn a handy technique for mailing labels that lets you group labels by residence to avoid sending duplicate mailings to family members. You'll learn to take advantage of update queries to alter the values in one table based on the values from another, and you'll learn a trick that can be used to filter a query based on the value of a Visual Basic for Applications (VBA) variable. In case you need to pull random sets of data from a data source, you'll see how to use a query to create a random set of rows. And you'll examine a query that uses a Partition function to perform an aging analysis.

You'll also find solutions dealing with more advanced uses of queries. You'll learn how to create a join that's based on a non-equality comparison, how to use union queries to horizontally splice together the data from two tables, and how to take advantage of union queries to add an extra choice to a combo box. You'll find out how to create self-join queries to model powerful recursive relationships, how to perform case-sensitive searches using a query, and how to use data definition language (DDL) queries to create or alter the structure of a table. You'll also examine a suggested method for storing query information in a table, which can be protected and made invisible in applications, giving you complete control over which queries are run and when. Finally, you'll learn a technique for creating recordsets in VBA code based on parameter queries.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you design a query, you don't always know which subset of records you would like to see when you run the query. Instead of creating several queries with the same basic design but slightly different criteria, you'd like to be able to create one query that can be used to return the same fields, but a different set of records, each time it's run.

Use a parameter query with one or more replaceable parameters that it will request at runtime (when you run the query). This solution demonstrates how you can create and run parameter queries using the default parameter prompt.

Here are the steps to create a parameter query using default prompts:

Create any type of query in query design view.
Choose a field for which you wish to define a parameter. Create a parameter for that field by entering the prompt you would like to see when the query is executed surrounded by square brackets ([]) in the Criteria row for that field. For the example query qryAlbumsPrm1, you would create a parameter for the MusicType field by typing:
```
[Type of Music?]
```
in the Criteria row under MusicType.
Select Parameters from the Query menu to open the Query Parameters dialog, where you declare the parameter. For this example, enter:
```
Type of Music?
```
in the Parameter column of the Query Parameters dialog, and choose:
```
Text
```
from the data type combo box to tell Access that this is a text parameter. This step is optional in this query, but some queries require it (see Recipe 1.1.3), so make it a habit. Steps 2 and 3 are shown in Figure 1-1.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The default type of parameter query is useful but has several drawbacks:

You get one Enter Parameter Value dialog for each parameter. Since these are sequential, you can't return to a previous dialog to change an incorrect value.
You can't select the value from a combo box or use a format or input mask, which makes it likely that the user will enter invalid data or data not found in the database.
You can't write any VBA event procedures to run behind the Parameters dialog.

Use a form-based parameter query by creating a more user-friendly form that collects the parameters.

Here are the steps to create a parameter query using a form-based prompt:

Decide how many parameters you will define for the query, in what order you would like them to be presented to the user, and what type of form control you would like to use for each parameter. For the qryAlbumsPrm2 query shown later, in Figure 1-4, we defined three parameters, as shown in Table 1-1. (Don't worry about the last column in the table yet—we will discuss it soon.) Note that we included two parameters for the Year field so we could select rows based on a range of years, such as "between 1970 and 1975."

Table 1-1: Parameters for qryAlbumsPrm2
Query field	Data type	Control type	Parameter reference

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Sometimes in a form-based parameter query it would be nice to limit the values in one combo box based on the value selected in another combo box. For example, if a form has two combo boxes, one for the type of music and the other for artists, when you select the type of music in the first combo box, you'd like the list of artists in the second combo box to be limited to artists of the selected music type. But no matter which type of music you select, you always see all the artists in the second combo box. Is there any way to link the two combo boxes so you can filter the second combo box based on the selected item in the first?

When you place two combo boxes on a form, Access by default doesn't link them together. But you can link them by basing the second combo box on a parameter query whose criteria point to the value of the first combo box. This solution demonstrates how you can use a parameter query tied to one combo box on a form as the row source for a second combo box to limit the second combo box's drop-down list to items appropriate to the user's selection in the first combo box.

Follow these steps to create linked combo boxes:

Create a form bound to a table or query. Make it a continuous form by setting the DefaultView property of the form to Continuous Forms. This will be used as a subform, like fsubAlbumBrowse in the frmAlbumBrowse example.
Create a second form with two unbound combo boxes. In the frmAlbumBrowse example found in 01-03.MDB, we named the combo boxes cboMusicType and cboArtistID. Drag the subform from the Access Forms object list in the database window onto the main form. We dragged the icon for fsubAlbumBrowse onto frmAlbumBrowse, underneath the combo boxes.
Set the LinkChildFields and LinkMasterFields properties of the subform control to keep the subform in sync with the main form. We entered ArtistID as the LinkChildFields and cboArtistID as the LinkMasterFields.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you have a crosstab query that uses the built-in Format function to convert dates into text for column headings, Access sorts them alphabetically (Apr, Aug, and so on) rather than chronologically. For example, open 01-04.MDB and run the qryAlbumTypeByMonth1 crosstab query (see Figure 1-7). This query shows the cross-tabulation of the number of albums purchased by album type and the month the albums were purchased. The month columns are sorted alphabetically instead of chronologically.

Figure 1-7: The months in qryAlbumTypeByMonth1 sort alphabetically

When the purpose of using the month in a crosstab query is to examine chronological variation by month, this makes the crosstab query all but useless. Is there some way to tell Access to sort the columns by date rather than alphabetically?

The query properties sheet allows you to specify fixed column headings for a crosstab query. This solution illustrates how to use the ColumnHeadings property to specify column headings so that formatted dates sort chronologically.

Follow these steps to create a crosstab query with correctly sorted formatted-date columns:

Create a select query. Select Query → Crosstab to convert the query into a crosstab query.
Add the columns you want to the crosstab query. Use a calculation for the Column Heading field. This calculation should use the built-in Format function to convert a normal date into an alphabetic string for cross-tabulation purposes. This might be the day of week or the month of year—in the example shown in Figure 1-7, we took the date field, DateAcquired, and formatted it as a three-letter month string. Add the remaining fields to qryAlbumTypeByMonth2, as shown in Table 1-3.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You need to print mailing labels intended for the general public. If your mailing list contains multiple occurrences of the same last name at the same address, you want to print only one label (addressed to the entire family). Otherwise, you need to print one label for each person in the table.

To avoid sending duplicate mailings to multiple members of a family, you can use a totals query to group label data so that people with the same last name who live at the same address will make up only one row in the output query. In addition, if you count the number of occurrences of combinations of last name, address, and zip code, you can create the mailing-label text with different text for mailings to a family based on that count.

To create this grouping in your own data, follow these steps:

Create a new query (qryCountNames, in this example) based on your table. Turn this query into a totals query by choosing View → Totals or by clicking on the Sigma button on the toolbar. This query will group the data using one row for each unique combination of the grouping fields.
Add a column to the query grid for each column in your table on which you want to group rows. Our example uses [LastName], [Address], and [Zip]. For each column, set the Total field to Group By. If you want to specify column names, place those names, followed by a colon, before the field names, as shown in Figure 1-10.

Figure 1-10: The grouping query, qryCountNames, with new column aliases

Add a column to the query grid in which Access will count the number of rows that it groups together to make a single row in the output. Choose any field that won't have null values (i.e., a required field), place it in the query grid, and set its Total row to Count. (This field is called [Residents] in this example.) This instructs Access to count the number of rows in the same grouping, as shown in Figure 1-10. You can also use the expression

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You've imported a table that contains updated prices for some of the records in a table in your database. The data in all the other fields in the existing table is still correct. Is there any way—short of using a complex VBA procedure—to update the price data in the existing table based on the updated prices from the imported table without overwriting any of the other fields in the existing table?

You probably already know that you can use an Update query to update the values of fields in a table, but did you know that you can use an Update query to update the values in one table with the values from another? This solution will show you how to do just that. If you can join the two tables on some common field or combination of fields, you can use an Update query to update a field in one table based on the values found in a second table.

Here are the steps to create an Update query that updates values across tables:

Create a standard Select query. Add the two tables to the query and join them on the common field or fields. In the sample database, we added the tblAlbums and tblAlbumsUpdated tables to the query. We will refer to tblAlbumsUpdated as the source table because it will supply the values to be used to update the other table; tblAlbums is the target table because it will be the target of the updates. Access has automatically joined the two tables on AlbumID. If the name of the common field is not the same, you will have to join the two tables by dragging the common field from one table to the other.
Select Query → Update to change the type of query to an update action query.
Drag the field to be updated in the target table to the query grid. In the Update To cell for the field that will be updated, specify the fully qualified name of the field in the source table that will be the source of the updated values. This field name should include the name of the table surrounded by square brackets, a period, and the name of the field surrounded by square brackets. For qryUpdateAlbumPrices, drag the PurchasePrice field from tblAlbums to the query grid. The field settings for PurchasePrice are shown in Table 1-4.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You'd like to be able to return rows in a query that have a test score greater than a specified value, which is stored in a VBA variable. When you try to use the variable in the query design grid, Access thinks it's a literal value. Is there some way to get queries to understand VBA variables?

To use a VBA variable in a query, you need to write a VBA function that returns the value of the variable as its return value and then reference the VBA function either as part of a calculation or in the criteria of a field. The only way to work with VBA in queries is to call a function. This solution shows you how to do that.

In the sample database 01-07.MDB you'll find tblScores, a table of names and test scores. The goal of the sample is to allow you to specify a cutoff value and list everyone whose scores are greater than that value.

Open the frmScores form. This form allows you to choose between a randomly selected cutoff value and a user-specified cutoff value. If you choose the user-specified cutoff value, a text box is made visible to allow you to enter the cutoff value. When you click on the "Show the results" command button, an event procedure runs that saves the cutoff value—either the randomly chosen cutoff or the user-specified cutoff—to a private variable and then runs the qryScores query.

The qryScores query references the private variable using the GetCutoff function and then returns the rows in tblScores in which the score is greater than the cutoff value (see Figure 1-19).

Figure 1-19: The sample form, frmScores, and its output, qryScores

Follow these steps to use a VBA variable in a query:

Create a select query, adding the tables and fields you wish to include in the query. The sample query, qryScores, is based on the tblScores table and contains two fields, Name and Score.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You need to be able to retrieve a random set of rows from a table or a query so you can identify a random sample for a research study. You can't find a way to make this happen in the normal query design grid. What's the trick to getting a random sample of a certain number of rows?

The solution to this problem is not quite as simple as it might first appear, because of the way Access attempts to optimize the use of function calls in queries. You can call a VBA function to generate a random value for each row, but to ensure that your function runs for each row, and not just once, you need to feed it a value from the row. Once you've generated the random numbers, you can sort by that random column and use a Top Values query to select a random group.

In 01-08.MDB, open tblRandom. This table includes 50 rows of data. Your goal is to pull five randomly selected rows for this set of data. To do this, follow these steps:

Import the module basRandom from 01-08.MDB or create your own, including this single function:

Public Function acbGetRandom(varFld As Variant)
   
   ' Though varFld isn't used, it's the only way to force the query
   ' to call this function for each and every row.

   Randomize
   acbGetRandom = Rnd
End Function

Create a new select query or use an existing one. Add any fields you're interested in.
Add an extra column, with the following expression replacing the reference to the State field with a single field in your query's underlying table or query (this query won't run correctly unless you pass one of your field names to the function):
```
acbGetRandom([State])
```
You can clear this field's Show checkbox, because there's not much point in viewing a continually changing random number as part of your query output. Set the Sort value for the newly calculated field to Ascending (see Figure 1-21).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Using a crosstab query, you need to age transactions, grouped by Account ID, into ranges of 1-30 days, 31-60 days, 61-90 days, and greater than 120 days. You know that you can group transactions by month using the standard query tools, but you can't find a way to group them by 30-day increments.

Access provides the seldom-used Partition function, which is perfect for this task. It allows you to take a range of values and partition it into even-sized chunks. By specifying a 30-day partition size, you can create a crosstab query that will give you the information you need.

To create a query in your own application, follow these steps:

Create a new query based on a table or query containing the appropriate account, date, and amount information.
Convert this query to a crosstab query by choosing the Query → Crosstab menu item or by clicking on the Crosstab button on the Query Design toolbar.
As when you create any crosstab query, specify at least three columns in the query grid: one for the column headings, one for the row headings, and one for the values that make up the crosstab. In this case, choose the account number (or account name, depending on your data) as the Row Heading and the amount (summed) as the Value. Figure 1-22 shows the sample query in design mode, and Figure 1-23 shows the sample data that will be used in this example.

Figure 1-22: The sample query, qryAging, in design mode

For the column headings, group the dates in 30-day increments, using the built-in Partition function. For this specific example, use the following value:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You need to join together two tables in a query on the Between operator. For example, you have a table of students and their grades, and a table of grade ranges and the matching letter grade. Though there are lots of ways to solve this problem with complex expressions and VBA, you know there must be a solution involving just queries. You need a way to join these two tables, finding matches when a value in the first table is between two values in the second table.

In Access, relationships between tables are normally based on equality, matching values in one table with those in another. Two tables in an Access query are normally joined in the upper half of the query design screen—the table pane—by dragging the join field from one table or query to the other. You can join tables this way for joins based on equality ("equijoins") that can be inner or outer in nature.

Sometimes, though, you need to join two tables on some other relationship. However, Access doesn't graphically support joins between tables that are based on an operator other than =. To perform these types of joins, you must specify the join in the criteria of the linking field.

From 01-10.MDB, open the tblGrades and tblLookup tables, both shown in Figure 1-27. The first table, tblGrades, includes a row for each student and the student's numeric grade. The lookup table, tblLookup, contains two columns for the ranges of numeric grades and a third for the corresponding letter grade.

Figure 1-27: The two sample tables, tblGrades and tblLookup

Your goal is to create a query listing each student along with his letter grade. To accomplish this goal, follow these steps:

Create a new query including both the sample tables. Don't attempt to use the standard Access methods to create a join between the tables, because there's no mechanism for creating the kind of join you need.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have two tables of addresses, one for clients and one for leads. Generally you send different mailings to these two groups, but sometimes you need to send the same letter to both. You can always create a third table and append to it the data from each of the two tables, but there must be an easier way that doesn't involve the use of temporary tables. Is there a way to combine the data from these two tables into a single recordset, including only the U.S. addresses and sorted by zip code?

Access provides a special type of query that you can use to vertically splice together the data from two or more tables. The tables don't even need to have the same fields or fields of exactly the same data types. This is the union query, which can be constructed only by using the SQL View pane in the query designer.

The following steps show you how to construct a union query to combine data from two tables into a single recordset, limited to addresses in the U.S. and sorted by zip code:

Open 01-11.MDB. Open the two tables (tblClients and tblLeads) and examine their structure and data.
Create a new select query. Click on Close when you are prompted to add a table.
Select Query → SQL Specific → Union. Access will present a blank SQL view.
If you'd like, open tblClients in design view so you can see the field names while typing. Then type in the first part of the query:
```
SELECT Company, Address1, Address2, Address3, City, StateProvince, ZipPostalCode,
Country
FROM tblClients
WHERE Country = "U.S.A."
```
Yes, you must type it—there is no query by example equivalent to a union query. However, you could create this select query first using the query grid and then copy and paste the SQL into your new union query.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You'd like to be able to create a combo box that looks up items in a table and is limited to this list of items, but with the additional choice of <N/A>, which can be used to enter a null value for the field. You don't want your users to be able to enter any invalid entries, just <N/A> (or some other special code).

You can set the LimitToList property for the combo box to Yes to limit entries to those that your combo box provides and use a sorted union query to add an additional <N/A> row to the row source for the combo box. We suggest using <N/A> rather than simply N/A to force the entry to sort to the top of the combo box list. To make this work right, you'll need to make the combo box unbound and use a bit of VBA code to move values between the underlying table and the combo box.

To create a combo box with an <N/A> entry on a form of your own, follow these steps:

Create an unbound combo box that draws its records from a table. In the sample database, we created a combo box called cboArtistID on the form frmAlbums. To duplicate the combo box in the sample database, create a combo box with the properties shown in Table 1-6.

The other properties for this control don't matter. We purposely left RowSource blank; you will fill this in after you create the union query. The ColumnWidths entries of "0 in;2 in" will make the first column, which will hold the ArtistID, hidden from the user. Only the second column, with the ArtistName (or <N/A>), will show.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have a table that includes information on every employee in the company, including management. You'd like to be able to store information on who supervises each employee in this same table and then be able to create a query to show this hierarchical relationship.

You can display an employee-supervisor hierarchical relationship, also known as a recursive relationship, in Access with a select query that uses a self-join to join another copy of a table to itself. This solution shows how to create the table that will store the necessary recursive information and then how to create the self-join query to list each employee and his or her supervisor.

To create the employee table and a query that displays the recursive employee-supervisor relationship, follow these steps:

Create the employee table. This table should contain both an EmployeeID field and a SupervisorID field. These fields must have the same field size. In the sample database, tblEmployees contains the EmployeeID and SupervisorID fields. Because EmployeeID is an AutoNumber field with the FieldSize property set to Long Integer, SupervisorID must be a Number field with a FieldSize of Long Integer.
Enter data into the employee table, making sure that the SupervisorID field is equal to the EmployeeID field of that employee's immediate supervisor.
Create a new select query. Add two copies of the employee table. The second copy of the table will automatically have a "_1" appended to the end of the table name to differentiate it from the first one. Now join the two tables together by dragging the SupervisorID field from the first copy of the table (the one without the _1 suffix) to the EmployeeID field in the second copy of the table (the one with the _1 suffix).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have a table of words, some of which appear multiple times. Each instance of these words is spelled using a different combination of upper- and lowercase. You'd like to create a query that finds exact matches using case-sensitive criteria, but no matter what you type into the criteria for the query, Access always returns all instances of the same word, disregarding each instance's case. Is there any way to create a query that can select records based on case-sensitive criteria?

Access normally performs case-insensitive string comparisons. You can use the Option Compare Binary statement in the declarations section of a module to force VBA to make string comparisons that are case-sensitive within the bounds of that module, but this affects only string comparisons made in a VBA module, not comparisons made by the Jet engine. Thus, even when you run the query from a VBA Option Compare Binary procedure, any comparisons made in the query are case-insensitive. The problem is that the Jet engine doesn't know how to make case-sensitive string comparisons using any of the standard query operators. Fortunately, you can create your own case-sensitive string-comparison function in an Option Compare Binary module and call this function from the query. This solution shows you how to create the VBA function and how to use it to perform case-sensitive searches.

To use this technique in your own database, follow these steps:

Import the basExactMatch module from 01-14.MDB into your database.
Create a query for which you wish to perform a case-sensitive search. Add all the desired fields in the query grid.
Create a computed field in the query grid that references the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You know how to create a table from a make-table query, but when you create a table in this way it has no primary key or any other indexes. Furthermore, you can only create a new table with a structure based on that of an existing table. You'd like a way to create a table on the fly with the data types and field sizes you want and with appropriate indexes.

Access provides the data definition language (DDL) query, which is used to programmatically create or modify tables. It is one of the SQL-specific queries, which can be created only using SQL view. This solution shows you how to create and modify table definitions using DDL queries.

Follow these steps to create a table using a DDL query:

Design your table, preferably on paper, deciding which fields and indexes you wish to create. For example, before creating qryCreateClients, we came up with the design for tblClients shown in Table 1-8.

Table 1-8: Design for tblClients
FieldName	DataType	FieldSize	Index
ClientID	AutoNumber	Long Integer/Increment	Yes, primary key
FirstName	Text

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Your application uses a lot of queries, and you don't want these queries available or even visible to the users of your application. Also, you call your queries from VBA code. How can you hide the queries from users and make them easier to retrieve, modify, and execute?

You can create a query-management table that stores the SQL string of your queries in a memo field. Each query is named and includes a description. This technique allows you to store your queries in a table rather than in the Access collection of queries. You can also create a simple VBA function that you can use to quickly retrieve the SQL string of any of your saved queries.

Open and run frmSavedQueries from 01-16.MDB. After a few moments of processing, the form shown in Figure 1-44 should appear. This form is based on the tblQueryDefs table, which stores a record for each query you save. To add a new query to the table, add a new record and enter the SQL statement in the SQL Text control. You may find it easier to copy the SQL from an existing query (see Step 2 for more details). Type in a name and description. Notice that creation and modification times are automatically updated.

Figure 1-44: The saved queries form, frmSavedQueries

To use a saved query in your code, search the tblQueryDefs table for the name of a query and get the value from the SQLText field. To use this technique in your application, follow these steps:

Import the tblQueryDefs table, the frmSavedQueries form, and the basSavedQueries module from 01-16.MDB into your database.
To add a query to the tblQueryDefs table using the frmSavedQueries form, design and test the query using the Access query designer. Then, from query design view, select View → SQL. When the query's SQL string is displayed, highlight it and copy it to the clipboard. Next, add a new record in the frmSavedQueries form and paste the SQL string into the SQLText text box. Type in a name and description.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You have a parameter query that is linked to a form by three parameters. When you open the form, enter the information into the form's controls to satisfy the parameters, and then run the query interactively, everything is fine. But when you open the form, satisfy the parameters, and create a recordset from VBA code based on the same query, you get an error message complaining that no parameters were supplied. This doesn't make sense, since you've already supplied the parameters on the form. Is there any way to create a recordset from VBA based on a parameter query?

When you run a parameter query from the user interface, Access can find the parameters if they have already been satisfied using a form and run the query. When you create a recordset from VBA, however, the Jet engine isn't able to locate the parameter references. Fortunately, you can help the Jet engine find the parameters by opening the QueryDef prior to creating the recordset and telling Jet where to look for the parameters.

Open the frmAlbumsPrm form found in 01-17.MDB. This form, which is similar to a form used in the Solution in Recipe 1.1, is used to collect parameters for a query, qryAlbumsPrm. Select a music type from the combo box, enter a range of years in the text boxes, and click on OK. An event procedure attached to the cmdOK command button will run, making the form invisible but leaving it open. Now run qryAlbumsPrm from the database container. This query, which has three parameters linked to the now-hidden frmAlbumsPrm, will produce a datasheet limited to the records you specified on the form.

Now open the basCreateRst module from 01-17.MDB. Select the function CreatePrmRst1 from the Proc drop-down list. Its source code is shown here:

Public Sub CreatePrmRst1( )
    
    ' Example of creating a recordset based on a parameter query.
    ' This example fails!

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb( )
    
    ' Open the form to collect the parameters.
    DoCmd.OpenForm "frmAlbumsPrm", , , , , acDialog
    
    ' OK was pressed, so create the recordset.
    If IsFormOpen("frmAlbumsPrm") Then
        
        ' Attempt to create the recordset.
        Set rst = db.OpenRecordset("qryAlbumsPrm")
        
        rst.MoveLast
        
        MsgBox "Recordset created with " & rst.RecordCount & _
         " records.", vbOKOnly + vbInformation, "CreatePrmRst"
        
        rst.Close
    Else
        ' Cancel was pressed.
        MsgBox "Query canceled!", vbOKOnly + vbCritical, _
         "CreatePrmRst"
    End If
    
    DoCmd.Close acForm, "frmAlbumsPrm"
    Set rst = Nothing
    Set db = Nothing
End Sub

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As far as users of your applications are concerned, your forms are the application. The forms are the windows into the data that makes Access applications work. Access forms are incredibly flexible and can take on as many different personalities as there are Access developers. The tricks and techniques covered in this chapter are not as complex as ones you might find in other chapters of this book, but they will help form the foundation of your entire application. You'll want to use these tips to help give a consistent look to your forms and to help users find exactly which control currently has the focus. You'll also use them to control where users go on your forms by restricting their movement so they can't move to a new row until you allow them to and by giving your forms custom navigation controls. Your understanding of controls will grow as you learn to use option groups to collect and display non-numeric information and to control the display of multipage forms. You'll also learn how to resize the controls inside your forms to match the size of the form. You'll see how to combine controls to create new "hybrid" controls by linking a text box and a list box to form a combination that works like a permanently opened combo box, and you'll find out how to create your own pop-up forms, such as a replacement for Access's InputBox function. You'll learn how to save and restore program settings or application variables to the system registry and how to save and restore the size of your forms from one session to another. Finally, you'll learn how to control multiple instances of a form, allowing you to view multiple rows simultaneously.

When you make a new blank form, the form properties and the properties of any control placed on it use the Access defaults. You've decided upon a standard look for your forms and reports that is significantly different from these defaults, and you spend too much time changing control properties on every new form you create to make them match your look. You'd like some way to change the standard default values.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you make a new blank form, the form properties and the properties of any control placed on it use the Access defaults. You've decided upon a standard look for your forms and reports that is significantly different from these defaults, and you spend too much time changing control properties on every new form you create to make them match your look. You'd like some way to change the standard default values.

Access allows you to specify a particular form or report to use as a template for new forms or reports that you create. This solution lists the steps you'll need to take to create your own template for form design. The technique is the same for form templates and report templates.

To see the advantages of using a template to define a new form's settings, load 02-01.MDB and create a new form. Add controls of various types to the form. Notice that some of them look different from the normal Access defaults. To see where the properties are coming from, load the form named Normal from 02-01.MDB in design mode. Each of the controls on this form will act as a template for any new controls on any forms you create in this database. In addition, any new form you create will inherit its own properties from this template form.

To create your own template form, follow these steps:

Create a new blank form.
Make any general changes you want in the form properties, such as changing the GridX and GridY properties to different settings—many users may prefer 24 24, the smallest grid that will show dots. To do this, first display the properties sheet: click on the gray in the upper-left corner of the form or select the Edit → Select Form menu item. If you don't want a record selector, navigation buttons, minimize or maximize buttons, a control box, and/or scrollbars on your form template, turn them off in the layout section of the form's properties sheet. In addition, you can choose to center the form automatically when it is opened by changing the AutoCenter property to Yes.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The text cursor is too small in Access, and you can't always tell which text box on a form has the focus. You need some way to really highlight the current field.

There are many visual cues you can use to tell the user which text box contains the cursor. You can change the color of the text or the background, change the appearance of the text box, or change the appearance of the text box's label.

The simplest solution, which works quite well, is to change the BackColor and SpecialEffect properties of the active control. This solution uses some simple VBA code, which is attached to each control's Enter and Exit events, to do the work. Figure 2-3 shows the sample form, frmEffects, in use (with the City field currently selected).

Figure 2-3: frmEffects in use, showing the active field

Open 02-02.MDB and load frmEffects. As you move from field to field on the form, note that the special effect and the background color of each control change when you enter and again when you leave the control.

Follow these steps to create a form with this same sort of functionality:

Create a new module and name it basSpecialEffects. In the declaration section, create the following constants, which will represent the controls' SpecialEffect and BackColor property settings:
```
Option Compare Database
Option Explicit

Private Const conWhite = 16777215
Private Const conGray = -12632256
Private Const conIndent = 2
Private Const conFlat = 0
```
Create two functions named SpecialEffectEnter and SpecialEffectExit that will toggle the values of the BackColor and SpecialEffects properties for the text boxes. The completed module is shown in Figure 2-4.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you press Tab or Shift-Tab, you can't keep Access from moving the cursor to the next or previous row of data if you happen to be on the first or last control in a form's tab order. The same thing happens when you press the PgUp or PgDn key. Often, however, you want the cursor to stay on the same row, and you want complete control over when the user moves to a different row. Is there some way to keep Access from moving the cursor to the next or previous row when these keys are pressed?

To gain complete control over row movement, you'll need to incorporate two different techniques. You can use your form's Cycle property to decide whether leaving the first or last control on the row moves you to a different row. If you want to ensure that PgUp and PgDn don't move the cursor to a different row, you'll need to write a bit of code that will trap these particular keystrokes in the KeyDown event for the form and disregard them. This solution uses both techniques to limit row movement.

Follow these steps to add this functionality to your own form:

Create your form. Set its Cycle property (on the Other properties page) to Current Record. This causes the Tab and Shift-Tab keys to work correctly.
Set the form's KeyPreview property (on the Event properties page) to Yes. This causes the form to intercept keystrokes before any controls on the form can react to them.

Enter the following code for the form's KeyDown event (see the Preface for information on creating event procedures).

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
   Select Case KeyCode
      Case vbKeyPageUp, vbKeyPageDown
         KeyCode = 0
      Case Else
         ' Do nothing.
   End Select
End Sub

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Option groups are great for collecting and displaying numeric values, but sometimes you need to use an option group bound to a column of values that isn't numeric. For instance, in each row you have a field that contains just one of four different alphabetic codes. You want some way to let the user choose from those four codes on a form.

When you want a control on a form bound to a column in a table that contains a few alphabetic items, you usually can use a list or combo box to display and collect the information. Sometimes, though, you want to be able to use an option group, where you can have option buttons or even toggle buttons containing pictures. But option groups, as Access implements them, can be bound only to numeric columns.

The solution is to use an unbound option group. Rather than moving the data directly from the form to the underlying data, you'll make a pit stop along the way.

Open and run frmOptionExample in 02-04.MDB. This form, shown in Figure 2-7, pulls in two columns from the underlying table, tblShipments. Each row contains a Contents field and a Shipper field. The Shipper field can be just one of four values: UPS, Fed Ex, US Mail, or Airborne. The form displays the Contents field in a text box and the Shipper field in an option group. It also shows another text-box control: the pit stop mentioned earlier. This (normally hidden) text box is the bound control, not the option group.

Figure 2-7: Example form using an option group to store character data

To create a minimal sample form that works with the same data, follow these steps:

In 02-04.MDB, create a new form. Choose tblShipments for the form's RecordSource property.
Create controls on your new form, as shown in Table 2-2. Make sure that you've created the option group before you attempt to place any option buttons inside it. The option group should turn dark when you attempt to place an option button in it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·

Table of Contents