Access Cookbook | O'Reilly Media

Content preview·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 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.

Access lets you create a 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 Section 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 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

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 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-02.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 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-03.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.

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?

Figure 1-7: The months in qryAlbumTypeByMonth1 sort 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-9, 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 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 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 AlbumID field from tblAlbums to the query grid. The field settings for AlbumID 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 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-06.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 public variable and then runs the qryScores query.

The qryScores query references the public 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 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-07.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-07.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 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.

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 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-09.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 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-10.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 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 all sorts of "garbage" 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.

Table 1-6: Properties for the cboArtistID combo box
Property	Value
Name	cboArtistID
ControlSource

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 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 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-13.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 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

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 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-15.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-15.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

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 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-16.MDB. This form, which is similar to a form used in Section 1.1.2 , 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-16.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 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 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 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 = -2147483633
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 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 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.

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 reprint rights for this chapter

You have a large number of fields that you need to display on a form. If you place them all on the form at once, it looks too complicated. You need some way to group them by category and display only the ones that correspond to each category as the user works through all the groups.

Access 97 introduced the native Tab control, which is useful for organizing information into multiple pages. Simply organize your fields into categories, creating one page on the Tab control for each category.

Load 02-05.MDB and open frmMain. This sample form (shown in Figure 2-8) contains a Tab control. By clicking on a tab, you cause one of the four possible pages of the form to be displayed in the Tab control section.

Figure 2-8: The sample form, frmMain

To create your own version of a multipage form, follow these steps:

Create the table and/or query on which you want to base your form (tblSample in 02-05.MDB). Make sure your data includes a primary key (ID in tblSample).
Open your form (frmMain in 02-05.MDB) in design view. Insert a Tab control on the form.

Set at least the properties shown in Table 2-3 for the form itself.

Table 2-3: Form property values for the main form, frmMain
Property	Value
RecordSource

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 reprint rights for this chapter

You'd like to provide some mechanism for allowing users to move from row to row on a form, but you think the navigation buttons Access provides are too small and unattractive. Also, you can't control when the user can or can't move to another row. Can you replace these with your own buttons?

Access provides navigation buttons for you to use on forms, allowing you to move easily from row to row. However, you can neither move nor resize these buttons, and you can't change anything about their appearance.

You can create your own buttons, place them on a form, and have each button use the GoToRecord macro action. Unfortunately, this has two drawbacks:

If you attempt to move to the previous or next row and you're already at the end of the recordset, the macro will fail. The GoToRecord macro action just isn't smart enough to work in this case.
Your buttons will always be available, giving no indication of when you can use them.

To avoid errors, you must use VBA. This solution demonstrates the steps you can take to add the appropriate code to your application so that navigation buttons will move you safely from row to row and shows how to disable the navigation buttons when they are unavailable. The form frmNav in 02-06.MDB (see Figure 2-11) works this way. You can load it and give it a try before attempting to build your own. Use the navigation buttons to move from row to row (there are only a few rows in the table so far). Note that, as you move around in the table, the appropriate buttons become enabled and disabled. Also try using the PgUp and PgDn keys. You'll see that the appropriate buttons still become disabled as necessary. Try entering a row number into the text box in the navigation controls; when you leave the text box, you will move to the selected row number.

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 reprint rights for this chapter

Windows users have become accustomed to resizing forms on their screens. A professional-looking application will proportionally resize the controls on a form when you stretch or shrink that form. You'd like to be able to resize your forms while the application is running and have the controls on the form react appropriately. For example, the Database Explorer window's list box expands when you expand the window. How can you do this on your own forms?

Because Access can notify your application when the user resizes a form, you can attach code to the Resize form event and react to the change in size. Access also triggers this event when it first draws the form, so you can place your controls correctly then, too. Base your calculations on the form's InsideWidth and InsideHeight properties.

Load and run the form frmExpando in 02-07.MDB. Resize the form and watch the size of the large text box. Also notice the positions of the two command buttons. Figure 2-13 shows the form in design mode, and Figure 2-14 shows two copies of the form sized to different proportions. Though it's perfectly reasonable to change the size of all the controls, this form does not. It uses three different techniques:

Do nothing: The label above the text box doesn't change at all as you resize the form.
Change position only: The two command buttons move with the right edge of the form, but they don't change size.
Change size: The large text box changes its size to match the size of the form.

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 reprint rights for this chapter

You'd like to create a text box/list box combination like the one in Windows Help. As you type in the text box portion of the control, you want the list box to scroll to match whatever's been typed so far. You know you could use a combo box for this, but the combo box keeps closing up. You want something that's permanently open.

Entering a portion of the value they're looking for and seeing the matches displayed as users type is an excellent way to find specific values in a list. You get the best of both worlds: the functionality of a combo box and the "permanently open" look of a list box.

The key to implementing this functionality is the text box's Change event. Every time the text in the text box changes, the code you'll use will automatically find the matching value in the associated list box. You'll be able to call a function that will handle all the work for you. In addition, because searching through indexed tables is so much faster than walking through dynasets (the results of running a query or a SQL expression), this solution offers two solutions to this problem: one for list boxes that are bound to tables and another for list boxes that are bound to queries or SQL expressions. Figure 2-15 shows frmSearchFind in action.

The methods you'll find in this solution apply only to bound list boxes.

Figure 2-15: Using Incremental Search on frmSearchFind

To test out the functionality, open the database 02-08.MDB and then open either frmSearchFind or frmSearchSeek. As you type in the text box, you'll see the associated list box scroll to match what you've typed. If you backspace to delete some characters, the list box will still match the characters that remain in the text box. When you leave the text box or click on an item in the list box, you'll see the full text of the chosen item in the text box. The functionality is the same no matter which form you use. frmSearchSeek will look up items faster, though, because it's guaranteed to use an index to do its work.

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 reprint rights for this chapter

You'd like to be able to use Access's InputBox function in your applications, but it's so ugly! There doesn't appear to be any way to modify the way it looks, so you'd like to replace it with a standardized input form of your own. You'd also like to be able to call into your help file with a Help button on the input box.

The dialog you see when you run Access's InputBox function is just a form, like any other form, except that it's built into Access. You can create your own form, open it as a dialog form, and have it look any way you like. This solution demonstrates a technique you can use in many situations: creating a pop-up form that waits for input and, once it's done, allows the caller to retrieve the information gathered on the form. In this case, you'll call the acbInputBox function instead of InputBox, but the results will be the same.

Load and run frmTestInputBox from 02-09.MDB. This sample form gathers information and then calls the acbInputBox function to display the replacement input form. Once you're done with the input form, choose OK (to return the text you've entered) or Cancel (to discard it). The sample form will pop up a message box with the text you entered. Figure 2-16 shows the two forms at work.

Figure 2-16: Use frmTestInputBox to test the replacement input box

Follow these steps to include this functionality in your own applications:

Import frmInputBox from 02-09.MDB into your database. Modify its appearance any way you like: change its size, colors, fonts, or any other layout properties. Because the form includes a module that handles its setup, you'll want to use the form we've supplied rather than creating your own.
Import the module basInputBox from 02-09.MDB

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 reprint rights for this chapter

Your application uses a number of forms that you can move around the screen. You'd like to store the last location away somewhere so that the forms will appear in the same location the next time you start the application.

Some Windows applications are "smart" and can save the locations of their windows when they exit. Your application can do this, too, using the system registry. You can store settings when you close a form and read them back the next time you open it.

Open and run the form frmSavePos in 02-10.MDB. Move it around the screen, and perhaps resize it. When you close the form, code attached to the Close event will save its coordinates in the system registry database. When you reopen the form, if the form can find the keys in the registry, it'll reload the last set of coordinates and resize/position itself accordingly.

To use this technique with your own forms, follow these steps:

Import the module basSaveSize from 02-10.MDB into your own application. This module contains the functions necessary to save and restore a form's size and location in the registry.
Add the following code to your form's Load event procedure. This will restore the form's size and location when you load the form:
```
Private Sub Form_Load (  )
   acbRestoreSize Me
End Sub
```
Add the following code to your form's Unload event procedure. This will save the size and location when you close the form:
```
Private Sub Form_Unload (Cancel As Integer)
   acbSaveSize Me
End Sub
```

Most of the work involved in saving and restoring the form size and location happens in the imported module, basSaveSize. The two event procedures, called from the form's Load and Unload events, simply call procedures in the imported module, passing a reference to the current form.

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 reprint rights for this chapter

In an application, you have a form showing information about a customer. You would like to be able to open another copy of the form so you could move to a different row, compare values, perhaps copy from one row to another, or just look at more than one customer's record at once. As far as you can tell, you can have only one open copy of a form at a time.

In older versions of Access, you were limited to having only a single copy of a form open at any time. Starting with Access 95, you can open multiple instances of a form, under complete program control. There's no user interface for this functionality, however, so you must write code to make it happen. This solution demonstrates how to create, handle, and delete multiple instances of a form using the New keyword and user-defined collections.

Follow these steps to convert your own forms to allow for multiple instances:

Add two buttons to your form, with captions like Create New Instance (named cmdViewAnother in the example) and Delete All Extra Instances (named cmdCloseAll in the example).
Add the following code to the Click event procedure of the Create New Instance button:
```
Private Sub cmdViewAnother_Click(  )
   Call acbAddForm
End Sub
```
Add the following code to the Click event procedure of the Delete All Extra Instances button:
```
Private Sub cmdCloseAll_Click(  )
   Call acbRemoveAllForms
End Sub
```
Add the following code to the Close event procedure for the form:
```
Private Sub Form_Close(  )
   Call acbRemoveForm(Me)
End Sub
```
Import the module basMultiInstance from 02-11.MDB.

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 reprint rights for this chapter

You may devote days, weeks, or even months of work to designing tables and queries and writing the macros and code to put an application together, but along with your application's forms, its reports are the application. Because of this, you'll want make them as clear and attractive as possible.

The first solution in this chapter shows you how to do something that should be (and is) easy: printing a report with line numbers. Next, you'll learn how to print the value of query parameters on a report based on a parameter query and how to create an attractive multiple-column report.

The next group of solutions will teach you how to use Visual Basic for Applications (VBA) code and macros to print a message on a report only if certain conditions are met, how to create telephone-book-style page-range indicators, how to print a bar graph on a report using rectangle controls, and how to calculate page totals.

Next, you'll employ more challenging VBA code to work around the limitations of the CanGrow/CanShrink properties and prevent blank rows on reports by combining an entire address into a single expression for a mailing-label report. You'll see how to suppress printing a report if there are no records to print. Using an event procedure run from the report's Format event, you'll learn how to print one set of headers and footers on odd pages and another (mirror-image) set on even pages. Then you will learn how to use the Line method to draw lines or rectangles on a report—in this case, to make a line the same height as a variable-height text box. Next, you'll learn how to alternate gray bars on every other row of the report.

The final three solutions in this chapter show you how to tie a report's recordset to the filtered recordset of a form, how to prevent your report from breaking at an inappropriate place (such as right after a group header), and finally, in the most complex solution in this chapter, how to modify a report's grouping and sorting fields on the fly.

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 reprint rights for this chapter

You have a legal report that has a list of items in the detail section. You're required to sequentially number each item in the list. You thought about using an AutoNumber field, but this won't work because you want the number to reset itself for each group and you often want to print the items in a different order from how you entered them. Is there an easy way to create on the fly report line numbers that pertain only to the data printed on the report?

Yes, there is an easy way to do this that makes use of an underused property of a text box, RunningSum. This solution shows you how to add line numbers to your report by creating an unbound text box based on a simple calculation and adjusting the RunningSum property of this control.

To create line numbers on your own reports, follow these steps:

Create a new report or open an existing report in design mode. Add an unbound text box control to the detail section with the following ControlSource setting:
```
=1
```
For the sample report, we named the control txtLineNo.
Change the RunningSum property for the control from the default of No to either Over Group or Over All. We chose Over Group for the sample report (see Figure 3-1).

Figure 3-1: The RunningSum property can be set to No, Over Group, or Over All
Save the report and preview it to confirm that it now includes sequential line numbers.

To see an example of this solution, open 03-01.MDB. Run the rptEvidenceByCase report in preview view (see Figure 3-2). This report prints out a list of all evidence items, grouped by CaseId. Notice the line number field on the left side of the report, which resets to zero at the start of each group.

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 reprint rights for this chapter

You've created a report based on a parameter query that prompts the user for one or more parameters when the query is run. The report works just fine, but you'd like to be able to document somewhere on the report what parameter values were entered by the user. That way you'll know, for example, which years' records are included in the report. Is there any way to do this with Access?

Although this capability is not well documented, you can print the values of query parameters on a report by referring to the parameters as if they were fields in the underlying query. This solution shows you how to create controls on a report that document the user-entered runtime parameters.

Load the 03-02.MDB database and open the qryAlbumsPrm query in design mode to verify that this query has three parameters (Figure 3-3). Now open the rptAlbumsPrm in preview view. Because this report is based on qryAlbumsPrm, you will be prompted for the three parameters.

Figure 3-3: The qryAlbumsPrm parameter query includes three parameters

Enter your values at the parameter prompt. If you enter the parameter values from Table 3-1, you should see a report that looks similar to the one shown in Figure 3-4.

Table 3-1: Parameters and sample values for qryAlbumsPrm
Parameter	Sample value
Type of music?	Rock
Starting year?	1960

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 reprint rights for this chapter

You want to print a two-column, phone-book-style report with large initial capital letters to set off each alphabetical grouping. There is no Report Wizard for creating such a report, and you don't see a Column property to set up the number of columns you want. How can you make a multiple-column report in Access?

There is a way to format a report for multiple columns, but it's not where you might look for it, on a report's properties sheet or the report design menu. Instead, you'll find it on the Columns tab of the Page Setup dialog. This solution guides you through setting up a multiple-column, phone-book-style report that includes a large drop cap for each letter of the alphabet.

Follow these steps to create your own multiple-column report:

Open in design view the report you want to format for multiple columns, and select File → Page Setup. The Page Setup dialog appears. Click on the Columns tab of the Page Setup dialog (see Figure 3-5).

Figure 3-5: The Columns tab of the Page Setup dialog

Enter the appropriate settings for your report. You'll find a brief description of these settings and the settings used for the sample report in Table 3-3. Click OK when you're done.

Table 3-3: The Page Setup dialog Layout settings
Setting	Purpose	Sample

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 reprint rights for this chapter

On a letter that you mail to all the customers on a mailing list, you want to print a message on only some customers' letters (depending on, for example, the customer's zip code, credit status, or past orders). How do you make a text box print only when certain conditions are met?

You can create an event procedure that's called from the Format event of a report section to make a single control—or an entire section—visible or invisible depending on a condition you specify. This solution shows you how to create a simple event procedure that checks each report record for a certain condition and then prints a message only if that condition is met.

Follow these steps to add an event procedure to your report that prints a message only for certain rows:

Create a new report or open an existing report in design view. Add to the page header section any controls that you wish to show for only selected records. In the rptMailingByZipWithCondition sample, we included three labels and a rectangle control in the page header section.
While the cursor is still located in the page header section, select View → Properties to view the section's properties sheet (if it's not already open).
Create a new event procedure for the section's Format event. (If you're unsure of how to do this, see the Section P.5.5 in the the preface of this book.)
Add to the Format event procedure an If...Then statement with the following basic structure:
```
If (some condition) Then
   Me.Section(acPageHeader).Visible = True
Else
   Me.Section(acPageHeader).Visible = False
End If
```
For example, in rptMailingByZipWithCondition, we added an event procedure that tests if the first two characters of the Zip Code field are equal to 98. The complete event procedure is shown here:

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 reprint rights for this chapter

You're creating a report that contains a large number of items. To make it easier to see the range of items on each page, you'd like to create a page-range indicator. This would show the first and last items on the page, as in a telephone book. Is there a way to do this?

The answer to your question is a qualified yes. You can create such a page-range indicator, but placing it anywhere but in the page footer is difficult. Although you can place it in the page header, the method to do so is quite complex and is the subject of a topic in the Microsoft Access Solutions database (SOLUTIONS.MDB), which shipped with Access 95 and Access 97. You can also download an Access 2000 version of this very useful sample database, called Solutions9.mdb. Search for that name at http://msdn.microsoft.com to find the download.

Because Access prints documents from top to bottom, by the time you know the last item on the page it's too late to print it at the top of the page. The Solutions database workaround involves forcing the report to format itself twice, capturing the page ranges for all the pages during the first pass and storing the values in an array. When it makes the second pass, you supply the values from the array. That solution requires VBA and is cumbersome. The solution we present here focuses on a simpler method, placing the information you need in the page footer. If you can live with that placement, this solution is straightforward.

To create a page-range indicator on your own reports, follow these steps:

Create a new report or open an existing one in design view. Make sure that the report includes page header and footer sections (if it doesn't, choose Format → Page Header/Footer to add them). In the page header section, add a text box and set its properties, as shown in Table 3-8. This text box will hold the first row's value when you print the page.

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 reprint rights for this chapter

You need to create a simple bar graph on a report. Microsoft Graph or the Office Web Components would probably work, but you're hoping for a simpler native Access solution. You need a bar for each row showing the relative score for each student. Can't you do this with the standard Access controls?

You can place a rectangle control in the detail section of your report and set its width during the Format event that occurs as Access lays out each row of data. This solution shows how you can create a simple bar graph, setting the width of the rectangle control to be based on a numeric value in your data.

Open and run the report rptGraph in 03-06.MDB (see Figure 3-12). This report shows a list of students and their scores, along with a bar whose width represents the value of the score.

Figure 3-12: The sample report, rptGraph

To create a bar graph like this one in your own applications, follow these steps:

Create your report, including the text data you'd like to show for each row. The sample report shows the Name and Score fields from tblScores, using controls named txtName and txtScore.
Add a rectangle control from the report toolbox and place it next to the data in the detail section. In the sample report, the rectangle's control name is rctBar. The control's width isn't important, because you'll be adjusting that programmatically (the example report sets the width of the rectangle to be the maximum width for the report, four inches). For appearance purposes, you'll probably want to set its height to be the same as the height of the text boxes you've already placed on the report. Figure 3-13 shows the report in design view.

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 reprint rights for this chapter

Access allows you to create a group total in the group footer on a report or a report total on the report footer, but you can't find a way to create a page total in the page footer. You understand that this problem doesn't come up too often, but for your report you could really use this element. Is there a way to sum up values over a single page?

It's true that Access allows aggregate calculations only in group or report footers. You can, however, easily create page totals using two simple macros. This solution demonstrates this technique and shows how to add this capability to any of your own reports.

To create page totals for your own reports, follow these steps:

Create your report, and sort and group the data as desired. In the report's page footer section, include a text box named txtPageTotal.

Create the following event procedure in the Format event of the page header and report header sections:

Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
    [txtPageTotal] = 0
End Sub

Private Sub ReportHeader0_Format(Cancel As Integer, FormatCount As Integer)
    [txtPageTotal] = 0
End Sub

Create an additional event procedure in the OnPrint event for the detail section:

Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
    [txtPageTotal] = [txtPageTotal] + [Freight]
End Sub

Save your report. When you run it, you will see the total of the field you set in the OnPrint event procedure.

Now load rptPageTotals from 03-07.MDB in preview view (see Figure 3-14). This report is used to track orders and their freight costs. The items are grouped by month, and each group has a total in the group footer. At the bottom of each page, you'll see the total for all items on the current page.

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 reprint rights for this chapter

When you print mailing labels, especially when you use a small font size and place the address text boxes close together, you sometimes get unwanted blank rows in the addresses when the labels print. You also can't seem to use lines or graphics on your labels without causing blank rows. How can you get your labels to print correctly—without blank rows—in these situations?

The CanGrow and CanShrink text box properties for reports allow text boxes to grow or shrink vertically as needed. These properties normally work well, but sometimes overlapping text boxes or graphics can interfere with text boxes' ability to shrink or grow. This solution shows how you can avoid these problems by combining the output of several fields into a single expression and using that expression as the row source of a single text box.

Open the tblCompanyAddresses table from 03-08.MDB in datasheet view. You can see that this table contains typical address data, with three address fields (Address1, Address2, and PO Box). Some of the sample records have blanks in at least one of these address fields.

Close the table and open the rptLabels report in preview mode. This is a typical mailing-label report, as might have been produced by the Mailing Label Report Wizard. Notice that there are no blank rows in the addresses. Now open the rptLabelsWithImageBroken report in preview view (see Figure 3-16). We added to the left side of each label an Image control that causes unwanted blank lines. Finally, open the rptLabelsWithImageFixed report in preview view (see Figure 3-17). Notice that this version of the report doesn't have any unwanted blank lines, even though the same image appears on the left side of each label.

Figure 3-16: rptLabelsWithImageBroken prints labels with unwanted blank rows

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 reprint rights for this chapter

You have a report that prints records you select from a criteria form. Sometimes there aren't any records that match the criteria and the report opens with #Error in the detail section, which is unattractive and confusing. Is there any way you can prevent the report from printing when it has no records to print?

Access includes an event, OnNoData, that fires when no records are present in the report's underlying recordset. This solution shows you how to use this new event to suppress printing of the report when no records match the specified criteria.

To create a report that suppresses printing when there are no records, follow these steps:

Create a new report or open an existing report in design view.
Create an event procedure attached to the report's OnNoData property. (If you're unsure of how to do this, see Section P.5.5 in the the preface of this book.) Enter the following VBA code in the event procedure:
```
Private Sub Report_NoData(Cancel As Integer)
      MsgBox "Sorry, no records match these criteria!", _
       vbExclamation, "No Records to Print"
      Cancel = True
End Sub
```
Save and run the report. If you enter criteria that do not match any records, you will get a message box telling you that no records meet the criteria (like the one shown in Figure 3-21).

The following example demonstrates this solution. Load the 03-09.MDB database. Open the frmCriteria1 pop-up criteria form. This form allows you to enter criteria for the rptSelect1 report (see Figure 3-19).

Figure 3-19: The frmCriteria1 pop-up criteria form with 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 reprint rights for this chapter

Some of your reports are printed double-sided, and you would like to have mirror-image headers and footers on odd and even pages. How do you do this in Access?

This technique makes use of two sets of header and footer controls, one for odd pages and one for even pages. An event procedure run from the section's Format event uses the Page property and the Mod operator to determine whether the page is odd or even and makes the appropriate controls visible or invisible.

The following steps show you how to create your own report that prints different headers and footers on odd and even pages:

Open the report you want to print double-sided (or even single-sided, with different odd and even headers and footers).
Make a copy of the header control, and place one of the copies of the control on the left of the header and the other on the right. Make the lefthand control left-aligned (to print on even-numbered pages) and the righthand control right-aligned (to print on odd-numbered pages).

Create an event procedure attached to the OnFormat property of the report's page header section. In the event procedure, enter code similar to the following:

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
    On Error GoTo PageHeader_FormatError
    
    Dim fIsEven As Boolean
    
    fIsEven = acbIsEven(Me.Page)
    
    Me![lblTitleLeft].Visible = Not fIsEven
    Me![lblTitleRight].Visible = fIsEven
    
End Sub

You'll need to replace the controls in the event procedure with the names of your controls.

Make copies of the footer controls as well, and make a similar event procedure for the footer's OnFormat event property, referencing its left and right controls. In the event procedure, enter code similar to the following:

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 reprint rights for this chapter

You have a control on a report that has its CanShrink and CanGrow properties set to Yes so it can grow or shrink to accommodate different amounts of text. You placed a vertical line to the left of the control, and you want it to be the same height as the control. Is there a way you can synchronize the height of the two controls?

If you place a line on a report using the Line tool, it will always be the same size. To make a line change its height to match the height of another control (or group of controls), you need to use the Line method in a procedure attached to the Print event of a report section. This solution uses the Line method to make a line whose height varies to accommodate the changing height of a text box that displays a memo field.

Follow these steps to add to your own report a vertical line that shrinks or grows to match one or more CanShrink/CanGrow controls in a section:

Create a report or open an existing report in design view. Don't use the Line control to create a vertical line in the report. If you've already created such a line, remove it now.

Create an event procedure for the Print event of the group footer section (or the section on your report where you'd like the line to appear). (For more information on creating event procedures, see this book's Preface.) In the event procedure, add code similar to this:

Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As Integer)
    
    Dim sngLineTop As Single
    Dim sngLineLeft As Single
    Dim sngLineWidth As Single
    Dim sngLineHeight As Single
    
    Const acbcSMTwips = 1
    Const acbcDSSolid = 0
    
    Me.ScaleMode = acbcSMTwips
    Me.DrawStyle = acbcDSSolid
    
    ' Set the coordinates for the line.
    sngLineTop = Me![lblConditions].Top
    sngLineLeft = 0
    sngLineWidth = 100
    With Me![txtConditions]
        sngLineHeight = .Top + .Height
    End With
    
    ' Draw the line.
    Me.Line (sngLineLeft, sngLineTop)-Step(sngLineWidth, sngLineHeight), , BF
    
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 reprint rights for this chapter

You have some reports on which you'd like to print alternate rows with gray bars in the background. Printing these bars makes the reports easier to read, especially when there's lots of data or the report is very wide. Is there a way to create these bars in Access?

There are a number of ways to print alternate rows with gray and white backgrounds. The simplest method is to alternate the background color of the detail section for each new record. This solution shows you how to use this method to achieve the desired effect on your reports.

To create your own reports with alternating gray bars in the detail section, follow these steps:

Create your report. Because this method will fill the entire detail section with gray shading, the effect will work best if your detail section is one line high. (It will work with taller detail sections, but it won't look as good.)
Make sure that every control in the detail section has its BackStyle property set to Transparent. You can quickly change this property for all the controls in the section by marquee-selecting all the controls and then changing the BackStyle property in the properties sheet, which will now have the title Multiple Selection (see Figure 3-28).

Figure 3-28: Changing all the controls' BackStyle properties in one operation
Edit the report's module (click on the Code button on the Report Design toolbar or choose the View → Code menu option) and enter the following lines of code in the module's declarations area:
```
' Shade this row or not?
Dim fShade As Boolean
```

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 reprint rights for this chapter

You have a form that you use to view and edit your collection of record and CD albums. On the form, you've placed a command button that you use to print the records contained in the form's recordset. This works fine, but you'd like to enhance the functionality of the form so that when you filter records on the form and then print the report, only the filtered records will print. Is there any way to do this in Access?

Access includes properties (Filter and FilterOn) of forms and reports that you can use to manipulate form and report filters programmatically. This solution shows you how to use these properties to print on a report only those records filtered by a form.

Load 03-13.MDB and open the frmAlbums form. When you press the Print Records button, you should see the preview of a report, rptAlbums, that includes all 65 records from qryAlbums. Close the report and go back to frmAlbums, which should still be open. Now create a filter of the form's records using one of the Filter toolbar buttons or the Records → Filter command. For example, you might create a filter by using the new Filter By Form facility (see Figure 3-30).

Figure 3-30: Filter By Form is used to filter records on frmAlbums

When you finish creating the filter, apply it. You should see a filtered subset of the records (Figure 3-31).

Figure 3-31: The records have been filtered, resulting in three records

Now press the Print Records button. You should see a preview of the same report, rptAlbums, this time filtered to match the records you filtered using frmAlbums. If you print the filtered report, you should see a report similar to the one shown in Figure 3-32.

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 reprint rights for this chapter

On some of your reports, you use the Keep Together property to keep a whole group together or to ensure that a group header won't print without at least one detail item. When detail items are long, you may not want to keep an entire detail item together; however, you do want to have a reasonable number of lines under the header so that the header won't be the last line on the report page. How do you make a report start a new page instead of printing the group header with just a single detail line at the bottom of a page?

You can use an event procedure called from a report's Format event to evaluate the length of a report page before it actually prints and take an action (in this case, activating a page break control) only if certain criteria are met. This technique uses the acbConditionalBreak function and a page break control. This solution demonstrates how to use acbConditionalBreak to force a page break if there is not enough room to print at least one line of text from the detail section under a group header.

Open 03-14.MDB and print the report rptBadBreaks. This typical business-address report, which has its detail section's KeepTogether property set to Yes, occasionally prints a page with the Category group header as the last line of the page, as shown in Figure 3-33.

Figure 3-33: Page 2 of rptBadBreaks shows an inappropriate break for New World Communications

Now print the rptConditionalBreaks report. Notice that it has avoided the bad break by moving the New World Communications record to the top of page 3 (see Figure 3-34).

Figure 3-34: rptConditionalBreaks moves New World Communications to the top of page 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 reprint rights for this chapter

You have a report that has several different grouping and sorting fields that you need to rearrange every time you run the report. To do this, you've created five or six different versions of the same report, changing only the order of the fields and which fields are sorted or grouped. This is a maintenance nightmare, especially when you want to change some aspect of the report, which means having to change all the variants of this same report. Is there any easier way to do this in Access?

You can manipulate most aspects of a report's design using VBA code. This solution shows you how to programmatically open a report in design mode and manipulate several properties of controls and groups. Using this technique and a driving form, you can create a single report that can be customized using different sorting and grouping fields every time it is run.

Load 03-15.MDB and open frm_rptCompaniesSetup, which is shown in Figure 3-36.

Figure 3-36: The frm_rptCompaniesSetup form is used to set up the rptCompanies report

Select a grouping field and zero, one, two, or three other fields for the report (any or all of which can be sorted). When you're done, press the Preview or Print button and a report matching the chosen sorting/grouping fields will be previewed or printed for you. A sample report using the settings from Figure 3-36 is shown in Figure 3-37.

Figure 3-37: The rptCompanies report is customized every time it is run

To create a customizable report of your own, follow these steps:

Identify the table or query on which the report will be based. In our example, the report is based on the tblCompanies table. Decide which of the fields in this table or query you wish to allow to be selected, grouped, or sorted. In the sample database, we decided to use all of the fields from tblCompanies.

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 reprint rights for this chapter

This chapter is a compendium of tips and suggestions for making your application development go more smoothly and your applications look more professional. You'll learn how to convert queries into embedded SQL strings providing data for forms or reports. You'll learn how to build an object inventory so you can document your applications better, how to ensure that properties for objects that should match up actually do, and how to disable screen output more effectively than the methods Access provides internally can. You'll find tips on discerning the current language version of Access and modifying text in error messages and on forms and reports to accommodate the current language. You'll see how to set and restore the Access caption and how to set startup options for your application. You'll also see how to use the Windows File Open/Save dialogs and how to clear out test data before shipping your application. The final topic explains how to implement user-level Access security.

Access's Query Builder makes it easy to create SQL statements as row sources for combo boxes or as record sources for forms and reports. You'd prefer to use SQL statements for row and record sources because they reduce the number of unnecessary objects in your databases. Is there an easy way to make these conversions? What's the trade-off of using embedded SQL statements instead of query objects to provide your data?

There is no automatic conversion utility to transform queries into SQL statements, but you can use the View SQL button on the Query Design toolbar to display a query's SQL statement, copy it to the Windows clipboard, and then paste it into the RecordSource or RowSource property of a form or combo box.

Open 04-01.MDB and look at the form frmCompanyInfoQuery. This form has a simple query as its record source; the combo box in its header also has a query as its row source. Neither of these queries is needed elsewhere, so they are prime candidates for conversion into SQL statements.

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 reprint rights for this chapter

Access's Query Builder makes it easy to create SQL statements as row sources for combo boxes or as record sources for forms and reports. You'd prefer to use SQL statements for row and record sources because they reduce the number of unnecessary objects in your databases. Is there an easy way to make these conversions? What's the trade-off of using embedded SQL statements instead of query objects to provide your data?

There is no automatic conversion utility to transform queries into SQL statements, but you can use the View SQL button on the Query Design toolbar to display a query's SQL statement, copy it to the Windows clipboard, and then paste it into the RecordSource or RowSource property of a form or combo box.

Open 04-01.MDB and look at the form frmCompanyInfoQuery. This form has a simple query as its record source; the combo box in its header also has a query as its row source. Neither of these queries is needed elsewhere, so they are prime candidates for conversion into SQL statements.

Take the following steps to convert a query, using the form's record source query as an example. These steps have already been taken for the form frmCompanyInfoSQL, both for the form's RecordSource property and for the combo box's RowSource property.

Open the form whose record source you want to convert to a single SQL statement in design view, and make sure that the properties sheet is open (Figure 4-1).

Figure 4-1: A form's properties sheet, with a query as its RecordSource property
Click on the Build button (...) next to the RecordSource property to open the Query Builder for the record source 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 reprint rights for this chapter

To document your application, you'd like to be able to create a list of all the objects in your databases, including their owners, date of creation, and date of last update. You're sure you can do it manually, but is there a better way to create a table containing all this information?

Access's Data Access Objects (DAO) can give you the information you need. By programmatically working your way through each of Access's container collections, you can add a row to an inventory table for each object in your application, storing information about that object. You should be able to use the techniques for this operation to write your own code for enumerating other collections in Access. There are a few tricks along the way, which this solution discusses, but in general this is a straightforward project.

To create an object inventory for your applications, take only two steps:

Import the form zsfrmInventory from 04-02.MDB into your own application.
Load and run the form. As it opens, it builds the object inventory, saving the data in zstblInventory. If you want to rebuild the inventory once the form's up, click the Rebuild Object Inventory button. This recreates the inventory table and fills it with information about all the objects in your database. Figure 4-4 shows the form once it's been run on a sample database.

Figure 4-4: The inventory-creating form once it's done its work on a sample database

This example form includes the Access system tables, which you may never have encountered. These tables are part of every Access database and are not cause for alarm. You can view them in the Database Explorer by choosing the Tools

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 reprint rights for this chapter

You've finished your application and you're ready to deliver it, but you notice that your use of color, fonts, alignment, and other layout properties isn't consistent across all your forms or reports. You know you can manually check the values of all the properties of all the controls on your forms and reports, but there's got to be a faster way. Is there some method you can use to compare similar properties for all the objects in your application?

Access doesn't provide a "cross-section" of your properties, which is really what you need—some way to look at properties not listed by item, but by property name, across all objects. Building on the technology introduced in Section 4.2.2 , this solution creates a group of tables containing information about all the properties on any forms or reports you select. Once it builds those tables, it constructs a query that will allow you, using the Quick Sort menu items, to view all the property settings for various objects, sorted any way you'd like. Once you've sorted the output by property name, for example, you'll quickly be able to see which objects have incorrect settings for that particular property.

The 04-03.MDB sample database includes a single form, zsfrmVerifySettings. Figure 4-5 shows the form after it has done its cataloging in Northwind.MDB, ready to present property information on three different forms. Figure 4-6 shows the output data, sorted by property name, showing that several controls have different background colors.

To use zsfrmVerifySettings to catalog properties in your own applications, follow these steps:

Import zsfrmVerifySettings from 04-03.MDB into your own database.
Load zsfrmVerifySettings in form view. As it loads, it will build the object property inventory, creating tables and queries as necessary.

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 reprint rights for this chapter

You can use a form's Painting property to disable updates to that form and the Application.Echo method to disable updates to the Access window, but some activities still seem to show through or cause flashing on the screen. Is there any way to really hide screen activity?

Sometimes you need more control over screen repainting than you get with either Application.Echo or Form.Painting. You've already exhausted the options that Access provides for controlling the screen display. But there is one more alternative: Windows itself, through calls to the Windows API, allows you to disable screen updates for a window and all its children. Because Access makes it easy to retrieve the handle for the main Access window, you can disable all updates to that (or any other) window. This solution demonstrates a method of truly shutting off screen updates to the Access window. (Before you try it, however, be sure to read the cautions in Section 4.4.3.)

Load and run frmLockScreen (Figure 4-9) from 04-04.MDB. This sample form simply opens three reports in design mode and then closes them. The form includes a checkbox that allows you to run the test with screen updates enabled or disabled. Try it both ways; you should see a clear difference between the two ways of running the test. With the checkbox set, the underlying code disables screen updates, so you shouldn't see the reports' icons pop up. Without the checkbox set, you will see the icons.

Figure 4-9: The sample form, frmLockScreen, ready to run its tests

To use the Windows API to disable screen updates in your own applications, follow these steps:

Import the module basLockScreen from 04-04.MDB. This module includes the API declarations and code to disable updates to the Access main window.

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 reprint rights for this chapter

You distribute your applications in several countries, and your users have different internationalized versions of Access 2002 installed. You'd like your applications to be able to make decisions based on the installed version of Access. How can you find out which language version of Access is currently running?

In older versions of Access, you had to use an API call to get this information. However, starting with Access 2000, it is possible to retrieve language information using the Microsoft Office Object Library. This solution demonstrates how you can gather the language information you need.

Load and run the form frmLanguage in 04-05.MDB. As it loads, it calls the necessary functions to determine the currently running language version of Access. Figure 4-10 shows the form after it's been loaded into a retail U.S. English version of Access.

Figure 4-10: frmLanguage indicates the language version of Access that's running

To include this functionality in your own applications, follow these steps:

Import the module basFileLanguage from 04-05.MDB into your own application. This module includes constants representing the seven most commonly used languages and their related intrinsic constants and values.
Declare a long integer variable, lngLanguage. When your application starts up, make a call to acbAccessLanguage, which will return a number representing the current running language version of Access. You can assign this return value to the lngLanguage variable, as follows:
```
lngLanguage = acbAccessLanguage(  )
```
You can then pass that variable to procedures in your application that make decisions based on the current language version of Access.

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 reprint rights for this chapter

You'd like to be able to pop up translated error messages in your applications, based on the currently running language version of Access. You'd also like other text on your forms and reports to adjust automatically based on the current language version. You know there are a number of ways to do this, but you can't decide which is best. How should you store and retrieve messages in multiple languages?

The translated version of Access handles its own error messages (in the German version, for example, the Access error messages appear in German). But you do need to translate your own messages if you want your application to run smoothly in other languages. Though there are several methods of handling text, the most generic solution uses a table of messages, which you can look up by ID number.

Load and run the form frmTestMessage from 04-06.MDB. This form, shown in Figure 4-12, allows you to choose from three different languages (English, French, and Spanish) in an option group. As you choose each language, code attached to the option group's AfterUpdate event changes accordingly the captions for labels on the form and the status-bar text for text boxes. To try a sample error message in the chosen language, click the Test Message button.

Figure 4-12: The sample form, frmTestMessage, showing the French test error message

In each case, the messages are coming from the table tblMessages. This table includes a column for the message identifier (the primary key) and one column for each of the languages your application supports. Figure 4-13 shows the table, filled in for the sample application.

Figure 4-13: The message table, tblMessages, filled in for the sample application 04-06.MDB

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 reprint rights for this chapter

You'd like to be able to change the caption of the main Access window as part of your application. Of course, you need to be able to reset it back to its original value when you're done. You've found the AppTitle property in Access, but you just can't get it to work. Is there some simple way to retrieve and set the Access caption, as you can with any of the windows within Access?

This is one situation where it's simpler to use the Windows API than it is to use the built-in functionality. Although Access does support a property of the current database, AppTitle, that you can use to set and retrieve the Access titlebar, it's clumsy to use because AppTitle is a user-defined property. If the property doesn't yet exist in a database, you must create it. With the Windows API, retrieving and setting the Access caption both require just a few predictable steps, and neither process is terribly difficult. This solution demonstrates the steps to set and retrieve the Access caption with the Windows API. The AppTitle property is discussed in Section 4.7.3.

To try changing the Access caption, load and run frmSetTitleBarCaptionAPI from 04-07.MDB. The form displays the current Access caption. By filling in a new value in the New Access Caption text box and pressing the Set New Caption button, you can change the caption on the main Access window. Figure 4-14 shows the form once it's already done its work. Press the Reset Caption button when you're done to reset the Access caption.

Figure 4-14: frmSetTitleBarCaptionAPI after it has set the new Access caption

To include this functionality in your own applications, follow these steps:

Import the module basCaption (which supplies the necessary Windows API declarations and the interface routines) from

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 reprint rights for this chapter

You need to allow users to choose filenames for opening and saving files. You know that Windows supports a common way to get these names. How can you use this mechanism from within Access?

Not only can you use the common File Open/Save dialogs, but you even have four ways to do it:

You can use the ActiveX control, COMMDLG.OCX, that ships with the Office Developer's Edition (ODE).
You can call a function in MSACCESS.EXE, which is how the wizards show the dialogs.
You can call the Windows API directly.
In Access 2002, you can use the new FileDialog object.

If you don't have the ODE, the first suggestion won't help. The wizard interface for using these dialogs is a bit simpler than doing the work from scratch, but it's also quite limiting (you get only a few options), and it isn't available in runtime versions of Access 2000. The new FileDialog object in Access 2002 is available in the runtime environment, but it also limits your options. Therefore, this solution shows how to call the Windows API directly and lists all the options you have when using these common dialogs.

Open and run the form frmTestOpenSave from 04-08.MDB. This sample form allows you to set various flags (described later in this solution) and to see the results. You can try both the File Save and File Open common dialogs. Try changing some of the settings and see what happens. Figure 4-16 shows the File Open dialog—with the Read Only checkbox hidden and allowing for multiple selections—displayed in explorer mode (as opposed to the older Program Manager look, which is what Windows will use if you specify the multiselect option by itself ).

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 reprint rights for this chapter

You're finished designing and building a database; it's ready to ship to your client. Before they can use it, you need to remove the artificial data you've entered, without destroying permanent lookup tables. Is there a simple way to do this without running into referential-integrity problems?

One solution is to open every data table in datasheet view, select all the records, press the Delete key, and confirm the deletion. However, there are three problems with this simple method:

You have to open tables in a particular order (i.e., tables on the many side of a many-to-one relationship before their related one-side tables).
You have to remember which tables contain test data and which ones contain production data.
The task is tedious and repetitive.

Instead of clearing out your test data by hand, you can write a general-purpose routine that uses a table of tables and a simple SQL statement to remove only the test data, in the correct order.

Open 04-09.MDB and view the tables in the database container. Open the tblFood table and try to delete some records. You'll get a referential-integrity error, because there are related records in txrefFoodRestaurant. Figure 4-17 shows the relationships set up for the sample database. Now open frmDemo and click on the Clear button to remove all the test data from the database without any manual intervention.

Figure 4-17: Relationships in the sample database

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

Import the table zstblDeleteOrder (structure only, without data) into your own database, or create a new table with the fields shown in Table 4-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 reprint rights for this chapter

Youve created an Access database that you'd like to secure. The database contains some sensitive data to which you wish to limit access. You'd like to be able to create different classes of users, so that some users have no access to this data, others can read the data but can't change it, and still others can modify the data. How can you accomplish this?

The Microsoft Jet database engine, which Access uses to store and retrieve its objects and data, employs a workgroup-based security model that allows you to secure your Access databases, assigning permissions to users and groups. Access supports two mechanisms for securing your database: the database password feature and user-level security. The database password feature is an all-or-nothing proposition—users who know the password aren't restricted in any way once they're in the database. If you want to assign varying permissions to different users, you'll need user-level security. User-level security is fairly complex—it doesn't work if you leave out a step. It consists of creating a new workgroup file (which holds user, group, and password information) and then using this new workgroup file to secure the database. There is a Security Wizard built into Access that will help you secure your database, but you can also manually perform the process, which will help you understand what's happening.

User-level security relies on a special database, called a workgroup file, to store users, the groups to which they belong, and their passwords. When you install Access, you are automatically hooked up to a default workgroup file called System.mdw. To secure your database, you will need to create your own unique workgroup file.

Every Access workgroup file includes two built-in groups: the Users group, which contains every user; and the Admins group, the members of which automatically get permission to administer security. There is also one built-in user, Admin. The Admin user starts out in the Admins group, but don't let the name Admin confuse you. You can remove Admin from the Admins group and take away all its administrative privileges, as long as some other user is left in Admins to act as the administrator. The Admin user has the same identity in every Access workgroup file, so any privileges that you give Admin will be available to anyone.

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 reprint rights for this chapter

Printing output is a major component of any database product, and Access gives you a great deal of control over the "look" of your forms and reports. Programmatic control over the printer itself, however, has always been somewhat complex in Access. Windows provides rich and intricate support for output devices, and Access attempts to shield you from most of that intricacy. Sometimes, however, you do need to take control of your output devices; for example, you may need to change a particular device or a setting pertaining to a particular device. Historically, Access made this possible but not easy. However, Access 2002 exposes a Printers collection with a Printer object that makes it relatively easy to accomplish the most common printing tasks. The sections in this chapter describe the details of handling your output devices using these new objects.

This chapter focuses on the Printers collection and the associated Printer object. We'll cover the properties of these objects in detail and show examples of their use. You'll be able to retrieve a list of all the installed printers and make a choice from that list, setting the new default Access printer. You'll learn how to modify margin settings in forms and reports, thereby avoiding the use of Access's File → Page Setup dialog in your applications. You'll get help on changing printer options, such as the number of copies to print, the page orientation, and the printer resolution. Then you'll learn how to programmatically print the first page of a document from one paper tray and the rest of the pages from a different paper tray. This allows you to print the first page on letterhead paper and the rest on normal paper.

Finally, you'll find out how to determine which device has been selected to print a report and whether it's the default device. If it is, you can change the destination from your application, provide users with a choice of output devices, and print the object to a particular device. You'll also find a development tool that will run through all your reports and let you know which aren't set up to print to the default printer. By ensuring that all your reports print to the default printer, you will be able to send them to any output device simply by changing what Access thinks is the default printer.

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 reprint rights for this chapter

You'd like to be able to present your users with a list of all the installed printers. Is there some way to fill a list box with the printer names?

Access makes it easy to fill a list or combo box with all the available printers. You can iterate through the members of the Application.Printers collection, retrieving the DeviceName and Port of each printer.

To create a list of installed printers, follow these steps:

Add a list box to a form. Set the Name property of the list box to lstPrinters.
Place the following code in the form's Load event procedure (see the Preface for more information on creating event procedures):
```
Private Sub Form_Load(  )
    Dim prt As Printer
    
    lstPrinters.RowSourceType = "Value List"
    For Each prt In Application.Printers
        lstPrinters.AddItem prt.DeviceName & " on " & prt.Port
    Next prt
End Sub
```
To see an example of an application that lists a system's installed printers, load and run the form frmPrinterList from 05-01.MDB. Figure 5-1 shows the form displaying the installed printers on a test machine. Section 5.1.3 describes in detail the techniques used in building the list in Figure 5-1.

Figure 5-1: The sample form, frmPrinterList, showing the list of installed devices

Access 2002 provides several new objects that make working with printers and printing easier than these activities have been in the past. First, Access provides a Printers collection as a property of its Application object. This collection provides one Printer object corresponding to each output device installed on your computer. Each Printer object provides many properties, including DeviceName and Port, as used in this example.

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 reprint rights for this chapter

Windows allows you to install a number of printer drivers, but one of them must always be denoted as the default printer. Although Windows provides its own concept of its default printer, Access maintains its own, independent default printer. You'd like to be able to control which printer Access thinks is the default printer, perhaps even choosing from a list of all the installed printers. Is there a way to do this from within Access?

Windows maintains its own list of available printers and stores information about the default printer. When Access starts up, it automatically uses Windows's default printer as its own default printer. Access's Application object provides a Printer property. Setting this property to refer to an item within the Printers collection allows you to control the default printer for all Access objects.

In Access, you always have the choice of printing an object to the default printer or to a specific printer. None of the techniques shown in this chapter that allow you to change the output destination will work if you set up your reports to print to a specific printer. In addition, printing to a specific printer will almost always lead to trouble if you distribute your applications to end users who may or may not have the same printer available. We suggest that, if possible, you set your reports so that they all print to the default printer.

To create a combo box in your own application that allows the user to choose a new default printer, follow these steps:

Add a combo box to your form and name it cboPrinters.

Add the following procedure to your form's module:

Private Sub FillPrinterList(ctl As Control)
    ' Fill the provided control (ctl) with a list of printers. This 
    ' will cause a runtime error if ctl isn't a list or combo box.
    Dim prt As Printer
    
    ctl.RowSourceType = "Value List"
    For Each prt In Application.Printers
        ctl.AddItem prt.DeviceName
    Next prt
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 reprint rights for this chapter

You'd like to give your applications' users some control over report layout, especially in designating column and margin settings. You could just let them loose in report design mode, but you'd like to maintain a little control over their actions. Is there some way to modify these layout settings from VBA?

Access 2002 provides each form and report object with a Printer property. Retrieving this property gets you an object with many properties, several of which deal with margin and column settings for reports.

You can use properties of a report's Printer object to retrieve and set layout properties. You'll find properties representing the left, top, bottom, and right margins; the number of columns; and the size, spacing, and item order of the columns. In addition, the Printer object contains the Data Only option in the File → Page Setup dialog. This solution demonstrates how to use the print layout properties provided by the Printer object.

Load and run the form frmPrintSettings from 05-03.MDB. Figure 5-3 shows the form (which emulates Access's File → Page Setup dialog) after the report Report1 has been selected from the list of reports. Choose a report from the drop-down list, and the form will load that report in preview mode. You can change the settings for the selected report by typing new values into the text boxes. To save the changes to the selected report, click on Save Settings. You'll see those changes in the preview window.

Figure 5-3: frmPrintSettings provides the same functionality as the Access File → Page Setup dialog

Some of the items on the form are available only if you've specified more than one column for the Items Across value, so you'll want to use a number greater than 1 in that field. (Because this example opens the report in preview mode, changes you make aren't saved with the report. If you want to permanently save the report with the new settings, you'll need to modify the code so that the report opens in design view; changes you make will then be saved with the report when you close and save it.) The following sections explain both how to use the sample form from

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 reprint rights for this chapter

You've tried using SendKeys to change printing options in the File → Setup Page dialog, but this really isn't satisfactory. Sometimes it works and sometimes it doesn't, depending on the circumstances and the printer driver that's loaded. Is there some way to modify printer options without using SendKeys?

Windows makes many of the printer driver settings available to applications, including the number of copies, page orientation, and page size. Access 2002 makes it easy to retrieve and modify these values, using the Printer property of forms and reports. This solution focuses on the print settings features of the Printer object and demonstrates how to read and write values in the Printer object.

To be able to modify printer settings for reports or forms in your own applications, follow these steps:

Open the report in either preview or design view. (If you want to make your changes persistent, open the report in design view. If you want to apply changes for just this particular instance, open it in preview mode.)
Modify some of the properties of the Printer object provided by your form or report. For example, the sample form works with a small subset of the available printer-specific properties, using code like this:
```
DoCmd.OpenReport strReport, View:=acViewPreview
With Reports(strReport).Printer
    Me.txtCopies = .Copies
    Me.grpOrientation = .Orientation
    Me.grpPaperSize = .PaperSize
End With
```

When you're done working with the properties, write them back to the report's Printer object, using code like this (from the sample form):

strReport = Me.cboReportList
With Reports(strReport).Printer
    .Copies = Me.txtCopies
    .Orientation = Me.grpOrientation
    .PaperSize = Me.grpPaperSize
End With

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 reprint rights for this chapter

You'd like to be able to print the first page of your reports from a paper tray containing letterhead paper and then print the rest on normal paper stock. Is there some way in Access to switch paper trays programmatically from within your application?

The paper source is one of the properties of the Printer object associated with a report (see Section 5.4.2 for a description of the Printer object) that you can programmatically control. Given the information in Section 5.4.2 , it's relatively easy to change the paper source for a report so that the first page prints from one paper bin and the rest prints from another.

Load and run frmPaperSource in 05-05.MDB (Figure 5-5).

Figure 5-5: frmPaperSource allows you to print from different paper sources

With frmPaperSource loaded, choose a report. The report will load, minimized, in preview mode.
Choose a paper bin for the first page and a bin for the rest of the pages. Note that the lists of paper bins contain all the possible paper sources; your printer may not support all of the options listed in the combo boxes. You'll need to find the bins that work correctly with your printer driver.
Click the Print button. Access should print the first page of the report from the bin chosen for the first page and the rest from the bin chosen for the other pages.

To use this technique in your own applications, you'll need to add code that supports printing the first page, then the rest of the pages, as the result of some action (such as clicking a command button). In reaction to this event, call the PrintPages procedure, shown here:

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 reprint rights for this chapter

Access's File → Page Setup dialog allows you to specify either the default printer or a specific printer for each printable object. You'd like to be able to find out, programmatically, which printer has been selected for an object and whether the object is set to print to the default printer. How can you retrieve that information?

In addition to the properties you've seen so far, the Printer object keeps track of the three pieces of information that Windows must know about an output device: the device name (for example, "HP LaserJet 4"), the driver name ("WINSPOOL"), and the output port ("LPT1:"). Access also keeps track of whether the report has been set to print to the default printer or to a specific printer, in the UseDefaultPrinter property of the report. You'll use these properties to determine the information you need.

Load and run the form frmSelectedPrinters in 05-06.MDB. Figure 5-6 shows the form after rptReport3 is selected and the report's output device, driver, and port are filled in on the form. Because this report was set up to print to the default printer, the "Printing to Default Printer" checkbox is selected.

Figure 5-6: frmSelectedPrinters, after selecting rptReport3

The sample form uses this code to do its work:

Private Sub cboReportList_AfterUpdate(  )
    
    Dim strReport As String
    Dim rpt As Report
    
    On Error GoTo HandleErrors
    
    strReport = Me.cboReportList
    DoCmd.OpenReport strReport, View:=acViewPreview, WindowMode:=acHidden
    With Reports(strReport)
        With .Printer
            Me.txtDevice = .DeviceName
            Me.txtDriver = .DriverName
            Me.txtPort = .Port
        End With
        Me.chkDefault = .UseDefaultPrinter
    End With
ExitHere:
    DoCmd.Close acReport, strReport
    Exit Sub
    
HandleErrors:
    MsgBox "Error: " & Error & " (" & Err & ")"
    Resume ExitHere
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 reprint rights for this chapter

You'd like to be able to select an output device while your application is running without popping up the File → Page Setup dialog. Is there a way to present a list of available printers and have the chosen report print to the chosen device? For example, you want to print your reports to the printer sometimes and sometimes to the fax machine.

Though this topic sounds complex, its solution is really just a combination of other solutions in this chapter. Section 5.2.2 shows how to retrieve a list of available print devices and retrieve and set the default device. Section 5.6.2 shows how to determine if a given report or form is configured to print to the default printer. Given those two techniques, this solution shows you how to set a new output device, print the Access object (using the new default device), and then restore the original default device.

Access 2002 provides two ways in which you can change the output device: you can either change Access's default printer, then print your report to the new default printer; or you can simply change the report's selected output device. The first solution is easier and generally works better. The second requires an extra step (selecting the report on screen) but gives you more flexibility.

Load and run frmDefaultPrinterList from 05-07.MDB. Figure 5-7 shows the form in use, with the report rptReport3 selected and ready to print. Because rptReport3 has been configured to print to the default printer (you can open the File → Page Setup dialog to confirm this), the "Print to Default Printer" checkbox on the sample form is checked. You can choose a different output device from the combo box on the bottom of this form (of course, this will be interesting only if you happen to have more than one output device installed). If you choose a different output device (a fax driver, for example), the sample form will send the selected report to that output device.

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 reprint rights for this chapter

You are about to distribute your application to other Access users. You want to ensure that all your reports are set to print to the default printer so that they will work with the users' installations of Windows. How do you create a list of all your reports and show whether or not they have been saved with the default printer setting?

Building on the code examples in this chapter, you can investigate the UseDefaultPrinter property of each report to determine if it has the default printer selected. This solution uses this property, along with some simple ActiveX Data Objects (ADO) code, to get a list of reports in your database, to check the default printer setting, and to save the results to a table. This table feeds a report that you can print, rptReportPrinters. Once you have this list, you can set the output device for each report that has been set to print to a specific printer rather than to the Windows default printer.

Open and run frmShowReports from 05-08.MDB. Figure 5-8 shows the form once it's done all its calculations. It will show the name of every report in your database, along with the default printer setting for each.

Figure 5-8: The frmShowReports example form

You can obtain a printout of this information by pressing the Print Analysis button, which prints the rptReportPrinters report (Figure 5-9).

Figure 5-9: The Show Report Printers example report

To use this form in your own applications, follow these steps:

Import the objects listed in Table 5-5 from 05-08.MDB.

Table 5-5:

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 reprint rights for this chapter

The point of a database program is to manage data. Although Access provides most of the tools you'll need, there are many tasks for which you have to roll your own solution. This chapter concentrates on working with data in ways that traditional database operations don't support. You'll learn how to search for records phonetically, back up your database objects, perform lightning-fast finds on linked tables, save housekeeping information, and more. All the examples in this chapter use some form of Visual Basic for Applications (VBA) code, but don't worry—they are all clearly explained, and "testbed" applications are supplied to show you how each technique works. We present more tips for working with data in Chapter 14, focusing on techniques you can use when your data is stored in SQL Server, rather than in an Access Jet database (an .MDB or .MDE file).

Your application is used in a multiuser environment with users regularly adding and editing records. Access keeps track of when an object was created and last modified. However, it does not track this information at the record level. With each record, you want to log who created the record, who last edited the record, and the date and time associated with each of these actions. Is this possible?

Access has no built-in feature that records who edited a record and when the edit was made, but it's fairly easy to create your own. You'll need to add four fields to each of your tables to hold this information. You'll also need to create two simple procedures and attach them to the BeforeInsert and BeforeUpdate events of your forms.

To add this functionality to your applications, follow these steps:

Modify your table to include four new fields, as shown in Table 6-1.

Table 6-1: New fields for tblCustomer

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 reprint rights for this chapter

Your application is used in a multiuser environment with users regularly adding and editing records. Access keeps track of when an object was created and last modified. However, it does not track this information at the record level. With each record, you want to log who created the record, who last edited the record, and the date and time associated with each of these actions. Is this possible?

Access has no built-in feature that records who edited a record and when the edit was made, but it's fairly easy to create your own. You'll need to add four fields to each of your tables to hold this information. You'll also need to create two simple procedures and attach them to the BeforeInsert and BeforeUpdate events of your forms.

To add this functionality to your applications, follow these steps:

Modify your table to include four new fields, as shown in Table 6-1.

Table 6-1: New fields for tblCustomer
Field name	Field type	Default value
DateCreated	Date/Time	=Now( )
UserCreated	Text (20)
DateModified	Date/Time

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 reprint rights for this chapter

Often, you need to do different things depending on whether the current row is the "new" row on a form. For example, you might want to display a certain message box only when adding records. How can you do this?

You can use a form's NewRecord property to determine if you are on a new record by checking its value from an event procedure attached to the OnCurrent event property or some other event property of the form.

Follow these steps to implement this functionality in your own forms:

Create a new form or modify the design of an existing form.
Create an event procedure for the form's Current event. In that event procedure, create an If...Then statement that will branch based on the value of the form's NewRecord property. The code of the event procedure should look like this:
```
Private Sub Form_Current(  )
    If Me.NewRecord Then
        ' Do something for a new record.
    Else
        ' Do something for an existing record.
    End If
End Sub
```
You may wish to alter some visual cue on the form to indicate whether you are on a new record. For example, you might change the text of a label, the text of the form's titlebar, or the picture of an image control. In the sample form, we changed the picture of an image control in the form's header, imgFlag, by copying the picture from one of two hidden image controls that are also located on the form. The final Current event procedure looks like this:
```
Private Sub Form_Current(  )

    ' Determine if this is a new record and change the bitmap
    ' of the imgFlag control to give the user visual feedback.
    
    ' See Solution 9.7 for an explanation of using the
    ' PictureData property.
    
    If Me.NewRecord Then
        Me!imgFlag.PictureData = Me!imgFlagNew.PictureData
    Else
        Me!imgFlag.PictureData = Me!imgFlagEdit.PictureData
    End If
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 reprint rights for this chapter

You enter people's names into a table in which misspellings are a common occurrence. You would like a way to search for a person's record disregarding slight differences in spelling. You've tried using the Like operator with the first letter of the person's last name, but that produces too many names. Is there any way to search for records that sound alike?

Access has no built-in sound-alike function, but you can create one that employs a standard algorithm called the Russell Soundex algorithm. Using this algorithm, it's fairly easy to search for a last name phonetically.

Run the qrySoundex query found in 06-03.MDB. Enter a last name in the query parameter dialog, and qrySoundex will return all records from tblStaff that sound like the name you entered. For example, if you enter the name "Jahnsin" at the parameter prompt, qrySoundex will return the records shown in Figure 6-5.

Figure 6-5: The records returned by searching for "Jahnsin"

To perform Soundex searches in your own applications, follow these steps:

Import the basSoundex module from 06-03.MDB into your database.
Create a query based on a table that contains a field that holds people's last names. Include the LastName field and any additional fields you wish to see in the output of the query.
Create a calculated field that calculates the Soundex code for the LastName field using the acbSoundex function. In qrySoundex, we used the following calculation to create a new field called Soundex:
```
Soundex: acbSoundex([LastName])
```
Enter criteria for the calculated field that compare that field against the Soundex code of a user-entered parameter. Use 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 reprint rights for this chapter

You need to calculate the median for a numeric field. Access provides the DAvg function to calculate the mean value for a numeric field, but you can't find the equivalent function for calculating medians.

Access doesn't provide a built-in DMedian function, but you can make one using VBA code. This solution demonstrates a median function that you can use in your own applications.

Load the frmMedian form from 06-04.MDB. Choose the name of a table and a field in that table using the combo boxes on the form. After you choose a field, the median value will be calculated and displayed in a text box using the acbDMedian function found in basMedian (see Figure 6-7). An error message will be displayed if you have chosen a field with a nonnumeric data type; the string "(Null)" will be displayed if the median value happens to be Null.

Figure 6-7: The frmMedian form

Follow these steps to use acbDMedian in your own applications:

Import the basMedian module from 06-04.MDB into your database.

Call the acbDMedian function using syntax similar to that of the built-in DAvg function. The calling syntax is summarized in Table 6-3.

Table 6-3: The acbDMedian parameters
Parameter	Description	Example

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 reprint rights for this chapter

You like to use the ultra-fast Seek method to search for data in indexed fields in your table-type recordsets, but the Seek method won't work with linked tables because you can only open dynaset-type DAO recordsets against linked tables. You can use the Find methods to search for data in these types of recordsets, but Find is much slower at finding data than Seek. Is there any way to use the Seek method on linked tables?

The Seek method works only on table-type recordsets, so you can't perform seeks on linked tables. However, there's no reason why you can't open the source database that contains the linked table and perform the seek operation there. This solution shows you how to do this.

To use the Seek method on external tables, follow these steps:

Use the OpenDatabase method to open the source database that contains the linked table. For example, in the event procedure attached to the cmdSeek command button on the sample form, frmSeekExternal, you'll find the following code:

Set wrk = DBEngine.Workspaces(0)

' Directly open the external database. It will be opened
' nonexclusively, read-write, and with type = Access.
Set dbExternal = _
  wrk.OpenDatabase(acbGetLinkPath("tblCustomer"),, False, False, "")

Create a table-type recordset based on the source table. If you renamed the table when you linked to it, make sure you use the name used in the source database. The sample form uses this code:
```
' Create a table-type recordset based on the external table.
Set rstCustomer = dbExternal.OpenRecordset("tblCustomer", dbOpenTable)
```
Set an index and perform the seek operation, as in this code behind the sample form:

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 reprint rights for this chapter

You want to get a list of fields in a table or query and their properties. The ListFields method is fine for certain situations, but it returns only a few of the fields' properties. Microsoft has also made it clear that this method will not exist in future releases of Access. How can you create a replacement for ListFields that supplies all the available field information?

In Access 1.x, the ListFields method was the only supported way to return a list of fields and their properties. Its usefulness is limited because it returns only a few field properties and always returns a snapshot. Using the more flexible Data Access Objects (DAO) hierarchy, however, you can get all the properties of field objects and create a replacement for the outdated ListFields method that returns all of a field's properties (or as many as you'd like), placing the results in a readily accessible table.

Open and run the frmListFields form from 06-06.MDB (see Figure 6-9). Choose Tables, Queries, or Both, and whether you wish to include system objects. Select an object from the Object combo box. After a moment, the form will display a list of fields and their properties in the Fields list box. Scroll left and right to see additional properties and up and down to see additional fields.

Figure 6-9: The frmListFields form

To use this technique in your applications, follow these steps:

Import the basListFields module into your database.
Call the acbListFields subroutine, using the following syntax:
```
Call acbListFields (strName, fTable, strOutputTable)
```
The parameters are summarized in Table 6-5.

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 reprint rights for this chapter

You use AutoNumber fields in your tables to ensure that you have unique values for your key fields, but a key based on an auto-incrementing Long Integer AutoNumber field doesn't sort your tables in a useful order. Also, auto-incrementing AutoNumber fields always start at 1, and you want your AutoNumber values to start at another number. How can you create a replacement for Access's AutoNumber fields that gets around these limitations?

Access makes it easy to add unique value key fields to a table using the AutoNumber data type (referred to as the Counter data type prior to Access 95). AutoNumbers are automatically maintained by Access and ensure a unique value for each record. Auto-incrementing AutoNumber fields always start at 1, with 1 added for each new record. If your only concern is changing the starting number, you can do that by using an append query to insert a record with a specific value in the AutoNumber field. The next record added will automatically be assigned that value plus 1. However, you may have other good reasons for wanting to create a replacement for the built-in AutoNumbers. This solution shows how to create your own flexible AutoNumber fields that are multiuser-ready. You can also combine these custom AutoNumber values with other fields in the table to make your data sort more intuitively.

Open and run the frmFlexAutoNum form from 06-07.MDB. Add a new record. Type in some data, and be sure to put a value in the LastName field. Save the new record by pressing Shift-Enter. When you save the record, a new auto-incremented value will be placed into the ContactID field (see Figure 6-10).

Figure 6-10: The frmFlexAutoNum sample form

You can add this functionality to your own applications by following these steps:

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 reprint rights for this chapter

You use a standard backup program to save your databases, but this works only at the database level. This is fine for archival purposes, but you often want to back up individual objects. How can you get Access to display a list of objects and allow you to save selected ones to an output database you specify?

This solution shows how to create a form that selectively saves Access objects to another database. It works by using a multiselect list box and the CopyObject action.

Open frmBackup from 06-08.MDB (Figure 6-11). You can use this form to back up selected objects from the current database to another database. Select one or more objects from the list box, using the Shift or Ctrl keys to extend the selection. When you are finished selecting objects and have specified a backup database (a default database name is created for you), press the Backup button. The backup process will begin, copying objects from the current database to the backup database.

Figure 6-11: frmBackup backing up selected database objects

To add this functionality to your own database, follow these steps:

Import frmBackup from 06-08.MDB to your database.
Call the backup procedure from anywhere in your application by opening the frmBackup form. For example, you might place a command button on your main switchboard form with the following event procedure attached to the button's Click event:
```
DoCmd.OpenForm "frmBackup"
```

To see how it works, open frmBackup in design view. The form consists of a list box, two text boxes (one of which is initially hidden), two command buttons, and several labels. The list box control displays the list of objects. One text box is used to gather the name of the backup database; the other is used to display the progress of the backup operation. The command buttons are used to initiate the backup process and to close the form. All of the VBA code that makes frmBackup work is stored in the form's module.

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 reprint rights for this chapter

Most applications that are distributed to users include at least some Visual Basic for Applications (VBA) code. Because VBA provides the only mechanism for performing certain tasks (for example, using variables, building SQL strings on the fly, handling errors, and using the Windows API), most developers eventually must delve into its intricacies. The sections in this chapter cover some of the details of VBA that you might not find in the Access manuals. First you'll find a complete explanation of embedding strings inside other strings, allowing you to build SQL strings and other expressions that require embedded values. Two solutions are devoted to creating a procedure stack, which allows you to keep track of the current procedure at all times. The second of the two also creates a profiling log file, which helps you document where and for how long your code wandered. Next you'll learn about the DoEvents statement, which gives Windows time to handle its own chores while your code is running. A group of four solutions covers the details of creating list-filling functions, passing arrays as parameters, sorting arrays, and filling a list box with the results of a directory search. The final two solutions cover some details of working with Data Access Objects (DAO): how to set and retrieve object properties, whether the properties are built-in, and how to tell whether an object exists in your application.

The replacement of Embedded Basic with VBA is one of the most significant changes that occurred with the introduction of Microsoft Access for Windows 95. Because of this addition, Access can share a great deal of code with other VBA-equipped applications, such as Microsoft Excel, Project, and Visual Basic. It has inherited new language constructs and got a new integrated development environment (IDE) for free. When we refer to Visual Basic, VBA, or Access Basic in this chapter, we're referring to the version of VBA that Access supports as its programming language.

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 reprint rights for this chapter

You want to create criteria for text and data fields, but no matter what syntax you try you seem to get errors or incorrect results. What are you doing wrong?

You'll face this problem in any place in Access where you're required to provide a string expression that contains other strings—for example, in using the domain functions (DLookup, DMax, DMin, etc.), in building a SQL expression on the fly, or in using the Find methods (FindFirst, FindNext, FindPrevious, and FindLast) on a recordset. Because all strings must be surrounded with quotes, and you can't embed quotes inside a quoted string, you can quickly find yourself in trouble. Many programmers agonize over these constructs, but the situation needn't be that difficult. This section explains the problem and shows you a generic solution.

To see an example of building expressions on the fly, load and run frmQuoteTest from 07-01.MDB. This form, shown in Figure 7-1, allows you to specify criteria. Once you press the Search button, the code attached to the button will build the SQL expression shown in the text box and will set the RowSource property for the list box at the bottom of the form accordingly.

Figure 7-1: The test form, frmQuoteTest, with a subset of the data selected

To try all the features of the form, follow these three steps:

In the First Name text box, enter A. When you press Return, the form builds the appropriate SQL string and filters the list box. Note in the SQL string that the value you entered is surrounded by quotes. (This is the state in which Figure 7-1 was captured.)

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 reprint rights for this chapter

When you're writing an application, you often need to know the name of the current procedure from within your code. For example, if an error occurs, you'd like to be able to have a generic function handle the error and display the name of the procedure in which the error occurred (and all the procedures that have been called on the way to get there). VBA doesn't include a way to retrieve this information. How can you accomplish this?

By maintaining a list of active procedures, adding the current name to the list on the way into the procedure and removing it on the way out, you can always keep track of the current procedure and the procedure calls that got you there. There are many other uses for this functionality (see the next solution, for example), but one simple use is to retrieve the name of the current procedure in a global error-handling procedure.

The kind of data structure you'll need for maintaining your list is called a stack. As you enter a new procedure, you "push" its name onto the top of the stack. When you leave the procedure, you "pop" the name off the stack. Figure 7-2 shows a graphical representation of a procedure stack in action. The arrows indicate the direction in which the stack grows and shrinks as you add and remove items.

Figure 7-2: The call stack and the sample routines to fill it

To see the procedure stack in action, load 07-02.MDB. Open the module basTestStack in design mode. Open the debug window (choose View → Debug Window). In the debug window, type:

? A(  )

to execute the function named A. Figure 7-2 shows A and the procedures it calls. At each step, the current procedure pushes its name onto the procedure stack and then calls some other procedure. Once the calling procedure regains control, it pops its name off of the stack. In addition, each procedure prints the name of the current procedure (using 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 reprint rights for this chapter

You'd like to optimize your VBA code, but it's almost impossible to tell how long Access is spending inside any one routine and it's difficult to track which procedures are called by your code most often. You'd like some way to track which routines are called, in what order, and how much time each takes to run. Can you do this?

As outlined in Section 7.2.2 , you can create a code profiler using a stack data structure to keep track of the execution order and timing of the procedures in your application. Though the code involved is a bit more advanced than that in Section 7.2.2 , it's not terribly difficult to create the profiler. Using it is simple, as all the work is wrapped up in a single module.

Open the database 07-03.MDB and load the module basTestProfiler in design mode. In the debug window, type:

? A(  )

to run the test procedures. Figure 7-4 shows the profile stack and the code in A. As you can see, A calls B, which calls C, which calls D, which waits 100 ms and then returns to C. C waits 100 ms and then calls D again. Once D returns, C returns to B, which waits 100 ms and then calls C again. This pattern repeats until the code gets back to A, where it finally quits. The timings in the profile stack in Figure 7-4 are actual timings from one run of the sample.

Figure 7-4: The profile stack and the sample routines used to fill it

As the code is set up now, the profiler writes to a text file named LOGFILE.TXT in your Access subdirectory. You can read this file in any text editor. For a sample run of function A, the file contained this text:

********************************
Procedure Profiling
3/13/2001 3:29:11 PM
********************************
+ Entering procedure: A(  )
    + Entering procedure: B
        + Entering procedure: C
            + Entering procedure: D
            - Exiting procedure : D                 101 msecs.
            + Entering procedure: D
            - Exiting procedure : D                 100 msecs.
        - Exiting procedure : C                 301 msecs.
        + Entering procedure: C
            + Entering procedure: D
            - Exiting procedure : D                 100 msecs.
            + Entering procedure: D
            - Exiting procedure : D                 100 msecs.
        - Exiting procedure : C                 300 msecs.
    - Exiting procedure : B                 701 msecs.
    + Entering procedure: B
        + Entering procedure: C
            + Entering procedure: D
            - Exiting procedure : D                 100 msecs.
            + Entering procedure: D
            - Exiting procedure : D                 100 msecs.
        - Exiting procedure : C                 300 msecs.
        + Entering procedure: C
            + Entering procedure: D
            - Exiting procedure : D                 100 msecs.
            + Entering procedure: D
            - Exiting procedure : D                 101 msecs.
        - Exiting procedure : C                 301 msecs.
    - Exiting procedure : B                 701 msecs.
- Exiting procedure : A(  )               1513 msecs.

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 reprint rights for this chapter

If your VBA code includes a loop that runs for more than just a second or two, Access seems to come to a halt. You can't move the windows on the screen, and mouse-clicks inside Access are disregarded until your code has finished running. Why is this happening? Is there something you can do to relinquish some control?

You may have noticed that it's possible to tie up Access with a simple bit of VBA code. Though 32-bit Windows is multithreaded, this helps only if the applications running under it are also multithreaded. It appears that the executing Basic code ties up Access's processing, so the multithreaded nature of Windows doesn't help. If your code contains loops that run for a while, you should make a conscious effort to give Windows time to catch up and do its own work. VBA includes the DoEvents statement, which effectively yields time to Windows so that Access can perform whatever other tasks it must. Effective use of DoEvents can make the difference between an Access application that hogs Access's ability to multitask and one that allows Access to run smoothly while your VBA code is executing.

To see the problem in action, load and run the form frmMoveTest (in 07-04.MDB). Figure 7-5 shows the form in use. The form includes three command buttons, each of which causes the label with the caption "Watch Me Grow!" to change its width, in increments of 1, from 500 to 2,000 twips (in Figure 7-5, you can see only a portion of the label), in a loop like this:

For intI = 0 To 1500 Step 1
   Me!lblGrow1.Width = Me!lblGrow1.Width + 5
   ' Without this call to Repaint, you'll
   ' never see any changes on the screen.
   Me.Repaint
Next intI

Figure 7-5: The sample DoEvents Test form, frmMoveTest, in action

To test the effects of DoEvents, try these steps:

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 reprint rights for this chapter

Getting items into a list or combo box from a data source is elementary in Access. Sometimes, though, you need to put into a list box things that you don't have stored in a table. In Visual Basic and other implementations of VBA, this is simple: you just use the AddItem method. But Access list boxes don't support this method. How can you add to a list box items that aren't stored in a table?

It's true: Access list boxes (and combo boxes) don't support the AddItem method that Visual Basic programmers are used to using. To make it easy for you to get bound data into list and combo boxes, the Access developers had to forego a simple method for loading unbound data. To get around this limitation, there are two methods you can use to place data into an Access list or combo box: you can programmatically build the RowSource string yourself, or you can call a list-filling callback function. Providing the RowSource string is easy, but it works in only the simplest of situations. A callback function, though, will work in any situation. This solution demonstrates both methods.

One important question, of course, is why you would ever need either of these methods for filling your list or combo box. You can always pull data from a table, query, or SQL expression directly into the control, so why bother with all this work? The answer is simple. Sometimes you don't know ahead of time what data you're going to need, and the data's not stored in a table. Or perhaps you need to load the contents of an array into the control and you don't need to store the data permanently.

The following sections walk you through using both of the methods for modifying the contents of a list or combo box while your application is running. The first example modifies the value of the RowSource property, given that the RowSourceType property is set to Value List. The second example covers list-filling callback functions.

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 reprint rights for this chapter

You need a procedure that will work on a list of items, and you don't know ahead of time how many there will be. You know that VBA will allow you to use optional parameters, but this requires you to know exactly how many items you might ever need to pass, and it's impossible to predict that value. How can you accomplish this?

You have two choices in solving this problem: you can pass an array as a parameter, or you can pass a comma-delimited list, which Access will convert into an array for you. An array (an ordered list of items) must contain a single data type. By using the variant data type, though, you can pass a list of varying types into your procedure. This solution demonstrates both these techniques.

From 07-06.MDB, load the module basArrays in design mode and do the following:

Open the debug window (click the debug window button on the toolbar or choose the View → Debug Window menu item). In these steps, you will run code from the debug window.
If you need a procedure that will take a list of words and convert each to uppercase, you can use the UCaseArray procedure. To test it, type the following in the debug window:
```
TestUCase 5
```
You can replace the 5 in the command line with any value between 1 and 26. The procedure will create as many strings as you request, place them into an array, and then call UCaseArray. This procedure will convert all the strings in the array to uppercase. The test procedure will display the original version, followed by the altered version of the array. As you can see, no matter how many items you specify for the UCaseArray procedure to work on, it'll convert them all to uppercase. Figure 7-9 shows this procedure in use.

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 reprint rights for this chapter

Although it's a database product, Access doesn't include a way to sort an array. You need to present sorted arrays in an application, and you can't find a reasonable way to sort them without first saving them to a table. You know you've seen array-sorting methods in other languages. Can you write a sorting routine that executes quickly?

It's true that Access doesn't provide a built-in sorting mechanism for arrays. Entire volumes in libraries are devoted to the study of various sorting and searching algorithms, but it's not necessary to dig too deep for array-sorting methods for Access. Because you'll probably place any large data sets into a table, most arrays in Access aren't very large. Therefore, almost any sort will do. This solution uses a variant of the standard quicksort algorithm. (For more information on various sorting and searching algorithms, consult your computer library. This is a big topic!)

To try the sorting mechanism, load the module named basSortDemo in 07-07.MDB. From the debug window, type:

TestSort 6

where the 6 can be any integer between 1 and 20, indicating the number of random integers between 1 and 99 that you want the routine to sort. The sample routine, TestSort, will create the array of integers and send it off to VisSortArray, a special version of the sorting routine acbSortArray that shows what it's doing as it works. Figure 7-11 shows the output from a sample session.

Figure 7-11: The output from a sample run of TestSort

To use this sorting code in your own applications, follow these steps:

Import the module named basSortArray into your application.
Create the array you'd like to sort. This must be an array of variants, but those variants can hold any datatype; this solution uses an array of Integers and Section 7.8.2 uses an array of Strings.

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 reprint rights for this chapter

You need to present your users with a sorted list of files with a specific filename extension in a particular directory. You found the Dir function, but you can't find a way to get this information into a list box. Is there a way to do this?

This problem provides the perfect opportunity to use the past three solutions. It involves creating a list-filling callback function, passing arrays as parameters, and sorting an array. In addition, you'll fill that array with a list of files matching a particular criterion, using the Dir function.

Load the form frmTestFillDirList from 07-08.MDB. Enter a file specification into the text box (for example, c:\windows\*.ini). Once you leave the text box (by pressing either Tab or Return), the code attached to the AfterUpdate event will force the list box to requery. When that happens, the list box will fill in with the matching filenames. Figure 7-12 shows the results of a search for c:\winnt\*.exe.

Figure 7-12: frmTestFillDirList, searching for *.exe in the Windows directory

To include this functionality in your own applications, follow these steps:

On a form, create a text box and a list box, with properties set as shown in Table 7-6.

Table 7-6: Property settings for the controls on the directory list form
Control	Property	Setting

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 reprint rights for this chapter

You don't understand how to get and set property values in Access. It seems as if there are different kinds of properties, and what works for one object and property doesn't work for another. Is there some way to settle this once and for all?

There really are two kinds of properties for objects in Access. Built-in properties are those that always exist for an object, and user-defined properties are properties that you or Access creates for an object when requested. The syntax for referring to each type is different, but this solution provides a method that works for either type. This solution uses the user-defined Description property as an example, but the techniques will work just as well for any other property. The interesting part of this solution is that the Description property is not a built-in property, and attempting to set or retrieve this property using the standard object.property syntax will fail.

This solution provides a sample form, which is useful only for demonstrating the technique. The real power of the solution comes from the module, basHandleProperties, which provides procedures you can use to set and get any kind of property. To try out the sample form shown in Figure 7-13, load and run frmTestProperties from 07-09.MDB. Choose a table from the list of tables, and notice the Description property shown in the text box below the list. If you choose a field from the list of fields, you'll also see the description for that field in the text box below the list. You can enter new text into the two text boxes, and the code attached to the AfterUpdate event of either text box will write the text back to the Description property of the selected table or field.

Figure 7-13: frmTestProperties lets you set and get the Description property of any table or field

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 reprint rights for this chapter

You create and delete objects as your application runs. At some point, you need to be able to tell whether an object exists and make decisions based on that fact. But you can't find a function in Access that will tell you if a specific object already exists. Are you missing something? This ought to be a basic part of the product!

You haven't missed anything: Access really doesn't supply a simple method of determining if a specific object already exists. On the other hand, this is really quite simple, as long as you understand two important concepts: Access's support for DAO Container objects, and the ways you can use error handling to retrieve information. This solution uses these two subjects to provide a function you can call to check for the existence of any object.

Load and run frmTestExist from 07-10.MDB. This form, shown in Figure 7-14, lets you specify an object name and its type and then tells you whether that object exists. Certainly, you wouldn't use this form as-is in any application—its purpose is to demonstrate the acbDoesObjExist function in basExists (07-10.MDB). To make your exploration of frmTestExist easier, Table 7-8 lists the objects that exist in 07-10.MDB. Try entering names that do and don't exist, and get the types right and wrong, to convince yourself that the acbDoesObjExist function does its job correctly.

Figure 7-14: frmTestExist lets you check for the existence of any object in the current database

Table 7-8: The sample objects in 07-10.MDB
Object name	Object type

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 reprint rights for this chapter

One unavoidable fact of application design is that your application never runs as fast as you'd like it to. Unless you and your users are equipped with the latest and most powerful workstations with huge amounts of memory, performance will be less than ideal. Still, there are many techniques you can use to optimize your application, few of which are easily found in the Access documentation. Although your Access application may never run like that lean and mean dBASE II application you created 10 years ago, you certainly can make it run at an acceptable speed.

This chapter covers several optimizations that enable you to load forms faster, add and change data faster, and speed up your Visual Basic for Applications (VBA) code, for example. It also covers the optimization of queries, as well as multiuser and client/server optimization techniques. In addition, this chapter describes testing techniques that will help you gauge the speed gains of your optimizations.

The first time you open a form in your application, it seems to take forever to load. Is there any way to accelerate this?

You can radically improve the time it takes to load a form for the first time by preloading your forms when the database is initially opened. You can also decrease the load time for subsequent loadings by hiding instead of closing forms. This solution shows you how to improve form load time using these techniques.

Load the 08-01.MDB database. Note the time it takes for the switchboard form to appear (see Figure 8-1). Make sure that the "Preload and keep loaded forms" checkbox is unchecked; if it's checked, uncheck it, close the database, and start over. Now press one of the command buttons, such as the Orders button, and note how long it takes Access to initially load the form. Close the form.

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 reprint rights for this chapter

The first time you open a form in your application, it seems to take forever to load. Is there any way to accelerate this?

You can radically improve the time it takes to load a form for the first time by preloading your forms when the database is initially opened. You can also decrease the load time for subsequent loadings by hiding instead of closing forms. This solution shows you how to improve form load time using these techniques.

Load the 08-01.MDB database. Note the time it takes for the switchboard form to appear (see Figure 8-1). Make sure that the "Preload and keep loaded forms" checkbox is unchecked; if it's checked, uncheck it, close the database, and start over. Now press one of the command buttons, such as the Orders button, and note how long it takes Access to initially load the form. Close the form.

Figure 8-1: The 08-01.MDB switchboard form

Now check the "Preload and keep loaded forms" checkbox on the switchboard form and close the database. Reload the database and again note the time it takes for the switchboard form to appear. Load the Orders form, again recording the form load time.

You'll see that the switchboard form now takes longer to appear but that the form load time is significantly shorter. That's because checking the "Preload and keep loaded forms" checkbox and reloading the database flips a switch that causes the application to preload its forms (in a hidden state) as the switchboard form is loaded by Access. This lengthens the time it takes for the switchboard form to appear initially. However, because the Orders form is now preloaded, it takes much less time for it to appear when you press the Orders command button.

A switchboard form (or menu form) is an unbound form used for application navigation. They are usually made up of labels and command buttons with an optional picture.

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 reprint rights for this chapter

You are not happy with the speed at which your forms load and display. How can you change your forms so they will load and display faster?

Access gives you a lot of flexibility to develop dynamite-looking forms. Unfortunately, Access also makes it easy to create forms that run painfully slowly. Section 8.1.2 explained how you can speed up the loading time of all forms by preloading them. This solution discusses how to track down and fix various performance bottlenecks, thus improving form execution performance. We also discuss the use and misuse of graphic elements and combo and list box controls.

You should consider several potential issues when analyzing your forms for performance. We discuss here two common performance bottlenecks: controls involving graphic or memo field data, and combo and list box controls.

Section 8.2.2.1: Graphic and memo controls

Load the 08-02a.MDB database. Open the frmCategoriesOriginal form (see Figure 8-5). This form, although attractive, loads slowly and has a noticeable delay on slower machines when moving from record to record. Now open frmCategoriesStep3, which is the final version of the form after various optimizations have been applied to it (see Figure 8-6). Its load and execution times should be noticeably faster.

Figure 8-5: The original form, frmCategoriesOriginal, is slow

Figure 8-6: The final form, frmCategoriesStep3, is faster

Follow these steps to improve the performance of forms that include unbound graphic controls or bound controls that hold OLE or memo fields:

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 reprint rights for this chapter

Sometimes you need to use combo boxes that list many items. It takes the user a long time to scroll to the bottom of the list, because the list loads only a few rows at a time. Is there any way to get the list to load all at once?

There is a very simple VBA technique that forces the rows of a combo or list box to load all at once when you open the form. All you have to do is force the code behind the form to calculate the number of items in the list.

Load frmComboFast in 08-03.MDB. Click the down arrow of the top combo box and scroll to the bottom of the list. Access loads only part of the list each time you scroll, so it takes many attempts to get to the last items on the list. Now do the same with the second combo box. This time, you can scroll immediately to the last item on the list.

The Load event procedure in frmComboFast forces the second combo box to load the entire list, by calling the ListCount property of the control:

Private Sub Form_Load(  )
  Dim lngDummy As Long
  lngDummy = cboFast.ListCount
End Sub

To use this code on your form, simply change the name of the control from cboFast to the name of your combo or list box. You can handle multiple controls by reusing the lngDummy variable to retrieve the ListCount property value for each combo or list box that you want to load.

The form in this example loads a bit slower than it would if you didn't use this technique, because load time is sacrificed in order to improve the performance of the second combo box. If you need to use combo boxes that have very long lists, this is a price that your users probably will be quite willing to pay.

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 reprint rights for this chapter

You've heard that Rushmore can improve the performance of your queries. How do you create queries that use Rushmore?

Rushmore query optimizations help the Jet engine (the database engine built into Access) execute certain types of queries dramatically faster. This solution explains how Rushmore works and how you can take advantage of it. It also introduces a technique for timing the execution of queries.

Load the 08-04.MDB database. Open the qryOr1 query in design view. This query, which is shown in Figure 8-11, contains criteria on two fields, Menu# and Quantity. It returns all records from tblOrderDetailsNoIndexes where Quantity = 13 or where Menu# = 25. If you switch to SQL view, you'll see the following Where clause:

WHERE (((tblOrderDetailsNoIndexes.[Quantity])=13)) OR (((tblOrderDetailsNoIndexes.
[Menu#])=25))

Close the query and open the tblOrderDetailsNoIndexes table to confirm that this table has no indexes. The qryOr2 and qryOr3 queries are identical to qryOr1, but they are based on different tables. qryOr2 is based on tblOrderDetailsPartialIndexed, which contains an index on the Menu# field, and qryOr3 is based on tblOrderDetailsFullyIndexed, which contains indexes for both Menu# and Quantity.

Figure 8-11: The qryOr1 returns rows where Quantity = 13 or Menu# = 25

Run the three queries in turn. You should notice that qryOr3 is much faster than qryOr1 or qryOr2, which are of similar speed. To get more accurate timings, open the frmQueryTimer form in form view and create a new test comparing the three queries, as shown in Figure 8-12. Press the Run Test button to begin executing each query the number of times specified in the Number of Reps text box. When the test is complete, press the Results button to view a Totals query datasheet that summarizes the results of the test (see Figure 8-13). When we ran this particular test on a 650-Mhz Pentium III machine with 448 MB of memory, qryOr3—which takes advantage of Rushmore—was 3.67 times faster than qryOr2 and almost 60 times faster than qryOr1! On a slower machine, the results would be even more dramatic.

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 reprint rights for this chapter

You've optimized your forms and queries, but now you need to look at the entire application. Your application contains a lot of VBA code. What optimizations can you perform to make it run faster?

This solution demonstrates seven specific programmatic techniques you can apply to accelerate your code. The improvement can range from modest increases to 15-fold increases in performance.

To see the optimizations in action, open and run frmShowOptimizations from 08-05.MDB, shown in Figure 8-14. Click the Run Tests button, and the tests will run one by one, displaying the results in milliseconds. The tests compare two different methods of using VBA to achieve a result.

Figure 8-14: The frmShowOptimizations form

Follow these steps to apply the optimizations suggested by these tests to your applications:

When dividing integers, use integer division. A majority of the division operations performed by your application are probably done on integer values. Many developers use the slash (/) operator to divide two numbers, but this operator is optimized for floating-point division. If you're dividing integers, you should use the backslash (\) integer division operator instead. With \, Access works at the integer level instead of the floating-point level, so computation is faster. (Of course, this is useful only if you're assigning the results of the division operation to an integer. If you care about the fractional portion of the division, you'll need to use floating-point math and the / operator after all.) For example, instead of:
```
intX = intY / intZ
```

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 reprint rights for this chapter

Now that you've tried the optimization techniques in this chapter, you'd like to test some additional optimization ideas. How can you test various VBA optimization techniques in a standardized fashion?

By using a Windows API call, some simple math, and a wrapper function, you can easily compare the performance of two optimization techniques with relatively high accuracy. This solution shows you how to create a form to compare the performance of two functions. It runs the functions and then displays how long each took to execute.

Open and run frmTestOptimize from 08-06.MDB. The form shown in Figure 8-15 allows you to enter the names of two functions and test their performance relative to each other. The 08-06.MDB database contains two sample functions that show the relative performance of integer division and floating-point division. (This optimization was discussed in Section 8.4.2 .) To run the test, enter:

FloatDivision(  )

into the Function 1 text box, and enter:

IntegerDivision(  )

into the Function 2 text box. Press the Test button. The form will run each function, show the time taken by each function, and tell you which function is faster and by how much.

Figure 8-15: The Test Optimizations form

To use frmTestOptimize to test your own functions, follow these steps:

Import frmTestOptimize from 08-06.MDB into your database. This form is completely self-contained and requires no other objects.
Open frmTestOptimize in form view and enter the name of the two functions you wish to test along with any required parameters. Type the entries in the Function 1 and Function 2 text boxes exactly as if you were calling the functions in your VBA code, but omit the assignment operator and assignment object. For example, for a function that is called in your VBA code like this:

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 reprint rights for this chapter

You have a single-user application that you just converted to run on a network to be shared by multiple users. Your once-responsive application is now sluggish. How can you improve the performance of multiuser applications?

Moving a single-user application to a shared environment can make that application slower for at least three reasons. First, to read or write data from the database, the data must now travel across relatively slow network wires. This is almost always slower than reading and writing data directly to a local hard disk drive. Second, every time a record is written to disk, Access must spend time obtaining, releasing, and managing locks to make sure that two users do not write to a page of records at the same time. Third, if multiple users are trying to access the same records in the database, they must wait their turns before gaining access to the records. Because of these factors, you need to make an extra effort to optimize multiuser applications to bring their speed to an acceptable level. This solution discusses one way to improve performance by limiting the number of records in your form's recordsets.

This solution employs two files, 08-07FE.MDB and 08-07BE.MDB. You'll first need to link the data tables from 08-07BE.MDB (the "backend" or data database) to 08-07FE.MDB (the "frontend" or application database). Linking a data table allows you to use a table from one Access database within another Access database. Start Access and load 08-07FE.MDB. Choose File → Get External Data → Link Tables and select 08-07BE.MDB as the Access link database. At the Link Tables dialog, select tblPeople and click OK. (To appreciate the extra demands made on a multiuser application, you may wish to move the 08-07BE.MDB database to a file server on your local area network first.)

This solution makes use of a common multiuser technique: splitting the application and data into separate databases. Multiuser application performance can be improved considerably if you place the data (or backend database) on the file server and a copy of the application (or frontend database) on each user's desktop. Access includes the Database Splitter Wizard, which makes it easy to split an existing database into data and application databases. Select Tools

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 reprint rights for this chapter

You are using Access as a frontend to linked tables stored in a client/server database. You're not satisfied with the response time of your client/server application. What can you do to make it run faster?

You can apply a variety of optimization techniques when developing client/server applications. If you are attaching remote tables in databases such as SQL Server or Oracle, you are accessing data through open database connectivity (ODBC) drivers. Typically, client/server applications using ODBC require more horsepower on the part of workstations and the network. By knowing how data is retrieved from the server, you can make your application run faster.

Another option is to create an Access Data Project (ADP). This is possible only if your data is stored in SQL Server. Instead of using ODBC, ADPs use a newer technology, OLE-DB, to connect to the data. However, although OLE-DB is newer, it isn't necessarily faster than linking to tables using ODBC. Chapter 14 includes several solutions related to the use of Access project applications.

There is no sample database for this solution. Here are some suggestions to consider when optimizing your linked-table client/server application:

Your forms should retrieve as few records as possible when loading (fetching data is a significant bottleneck in client/server applications). Design your form to retrieve few or no records by using the technique demonstrated in Section 8.7.2 .
Optimize the way your application connects to the server. When the user starts your application, log the user into the server using the OpenDatabase method. This establishes a connection and caches it in memory. Subsequent data access is faster because the connection has already been established. Use code similar to the following:

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 reprint rights for this chapter

No matter how much you do behind the scenes to create a solid and robust application, the users of your application see only your user interface. Certainly, perfecting the database and application design is crucial—but once that's done, it pays to devote considerable time to designing a user interface that is workable, aesthetically pleasing, and helps the users get their work done. By implementing the ideas and techniques in this chapter, you'll be on your way to creating an interface that has ease of use and productivity written all over it.

You'll learn how to take full advantage of special keystrokes to help users navigate through a complex application. You'll also learn how to create forms that have no menus or toolbars and how to create a map-based interface that lets users navigate by pointing to and clicking on various parts of a map.

Next, you'll learn how to ease data-entry pain with forms that let users mark their place while they peruse other records, and how to add shortcut menus to forms. You'll also see how you can create forms that carry data forward from record to record, how to hide complexity from your users with a dialog that expands on request to reveal complex options, and how to use a combo box not just to select from a list, but also to maintain that list with new entries as they are needed.

Finally, you'll learn how to create and use two generic, reusable components: a pop-up calendar form for entering dates that makes use of an ActiveX control, and a custom-built status meter form complete with an optional Cancel button.

You've used Access's AutoKeys macro to create keyboard shortcuts for your application, but you'd like the shortcut keys to change based on the currently active form. Is there an easy way to create context-sensitive keyboard shortcuts in Access?

The SetOption method of the Application object allows you to change global database options programmatically. This solution shows you how to combine this functionality with the Activate and Deactivate event properties of your forms to create custom key shortcut macros for each form of your application.

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 reprint rights for this chapter

You've used Access's AutoKeys macro to create keyboard shortcuts for your application, but you'd like the shortcut keys to change based on the currently active form. Is there an easy way to create context-sensitive keyboard shortcuts in Access?

The SetOption method of the Application object allows you to change global database options programmatically. This solution shows you how to combine this functionality with the Activate and Deactivate event properties of your forms to create custom key shortcut macros for each form of your application.

For an example of key assignments that depend on the active form, open 09-01.MDB. This sample database contains information on units, assemblies that make up parts, and parts that make up assemblies. Open the frmUnit form in form view. At any time, you can press Ctrl-D to "drill down" to the next level of detail or Ctrl-R to revert to the previous level of detail. When you press Ctrl-D on frmUnit, frmAssembly is loaded; if you press Ctrl-D from frmAssembly, frmPart is loaded (see Figure 9-1). If you press Ctrl-D a third time while frmPart has the focus, nothing happens. Thus, the behavior of Ctrl-D changes based on its context. The Ctrl-R keyboard macro is similarly context-sensitive.

Figure 9-1: The sample database after pressing Ctrl-D twice

To keep the example simple, we have not added the additional macro code necessary to keep the forms synchronized. You must manually use Ctrl-R to return to the previous level/form, then navigate to the desired record, and then use Ctrl-D to drill down if you wish to keep the forms synchronized.

To add context-sensitive AutoKeys macros to your own application, follow these steps:

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 reprint rights for this chapter

You'd like to completely disable menus for a form, and the toolbar too. Is there any way to remove menus and toolbars from a form?

If you set the MenuBar property of a form to point to a macro in Access that contains no macro actions, you can trick Access into not displaying any menus. This solution demonstrates this trick and also discusses how you can apply it to the global menus of an application. In addition, you'll learn how to use VBA code to remove a form's toolbar.

To create forms in your database without any menus, follow these steps:

Create a new macro sheet without any actions. The mcrNoMenus macro sheet in 09-02.MDB has no macro actions.
Create a new form or open an existing form in design view. Select the menu macro from Step 1 as the MenuBar property for the form.

Add the following Activate and Deactivate event procedures to the form to remove the toolbar for this form only:

Private Sub Form_Activate(  )
 DoCmd.ShowToolbar "Form View", acToolbarNo
End Sub

Private Sub Form_Deactivate(  )
 DoCmd.ShowToolbar "Form View", acToolbarWhereApprop
End Sub

Optionally, you may wish to also eliminate right-click shortcut menus for your form. To do this, set the ShortcutMenuBar property of the form to No.
Save the form.

To see an example, load the 09-02.MDB sample database. Open the frmCustomerDefaultMenus form in form view and note that the default Access menu and toolbar are available at the top of the screen (see Figure 9-4). Close this form and open frmCustomerNoMenus. Note the absence of any menu or toolbar for the form (see Figure 9-5).

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 reprint rights for this chapter

You want to display a map and allow users to click on regions of the map. You want the form to react based on which region is clicked on. The regions aren't necessarily rectangular. How can you do this?

You can accomplish this task using a combination of bitmaps and transparent command buttons. Depending on how far from rectangular your shapes are, this task may be trivial or quite involved. By making the command buttons transparent, you make the application appear to react directly to mouse clicks on the map.

Open frmWesternUS in 09-03.MDB (Figure 9-7). This form has been created with an imported bitmap file as the background. Above each state's image on the map there's at least one command button with its Transparent property set to Yes. Figure 9-8 shows the form named frmWesternUSDesign, in which the buttons are not transparent. Here you can see the actual layout of the command buttons.

Figure 9-7: The finished map form, frmWesternUS, with transparent buttons

Figure 9-8: The same bitmap form with buttons showing

To implement similar functionality in your own forms, follow these steps:

Create a new form. Click anywhere in the detail section of the form, and select Insert → Object (or use the form design toolbox to place an unbound object frame control form on the form). Once you release the mouse button, Access displays a dialog requesting information about the object. At this point, you can create a new object by launching an application such as Microsoft Paint, or you can create an object from an existing file. If you choose the latter, a Browse button will appear. Click on the Browse button to select a file (see Figure 9-9). Choose the appropriate image for the background. For the example form, use

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 reprint rights for this chapter

Sometimes you are interrupted when you're editing a record on a form and need to move quickly to some other record. You'd like a way to save your place and easily return to it later. Is there an easy way to do this in Access?

Access forms have a Bookmark property that is similar to the bookmark you use when you put a book down but want to be able to quickly return to where you left off. This solution shows how to use VBA code to store the bookmark value of a particular record and return to it, presenting this functionality to your users with a toggle button. The solution also shows you how to add a custom shortcut menu to a control.

Follow these steps to add the ability to return to a designated record in your own forms:

Create a new bound form or open an existing form in design view. Add a toggle button (not a command button) control to the form's header or footer section. In the frmCustomer sample form, we named our button tglMark and added it to the header section.

Create an event procedure attached to the Click event of the toggle button. (If you're unsure of how to do this, see Section P.5.5 in the the preface of this book.) Add the following code to the event procedure:

Private Sub tglMark_Click(  )
    ' If the toggle button is depressed, mark this record;
    ' otherwise, return to the previously saved record.
    
    If Me!tglMark Then
        Call acbHandleMarkReturn(conMark)
    Else
        Call acbHandleMarkReturn(conReturn)
    End If
End Sub

Add the following constants to the declarations section of the form's module (if the form's module is not currently displayed, select View

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 reprint rights for this chapter

You'd like to reduce the tedium of data entry by carrying forward selected values from one record to the next. Ideally, this feature will be user-selectable at runtime so that each user can indicate, on a control-by-control basis, whether the current value of a control should carry forward onto newly added records. Is there any way to implement this in Access?

There are two parts to this problem: the mechanics of carrying a value from one record to the next, and how best to let a user select which controls should carry forward values. The first part of the problem can be solved with a little VBA code to change the value of a control's DefaultValue property at runtime, squirreling away the original DefaultValue, if one exists, in the control's Tag property. The second part of the problem can be handled in a variety of ways; in this solution, we suggest using a small toggle button for each bound control that will offer the carry-forward feature.

To see an example, load the 09-05.MDB database and open the frmCustomer form in form view. Note that many of the text box controls have a small, captionless toggle button located just to their right. Navigate to the record of your choice and depress one or more of the toggle buttons to indicate that you wish to carry forward that text box's value to newly added records (see Figure 9-16). Now jump to the end of the recordset and add a new record. (A quick way to accomplish this is to click on the rightmost navigation button at the bottom of the form.) The values for the "toggled" text boxes carry forward onto the new record (see Figure 9-17). To turn off this feature for a control, click again on its toggle button to reset it to the unselected state.

Figure 9-16: The toggle buttons to the right of several text boxes have been depressed

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 reprint rights for this chapter

You're using combo boxes for data entry on your forms, and you want to allow users to add a new entry to the list of values in the combo box. Can you do this without forcing users to close the data entry form, add the record using a different form, and then return to the original form?

You can use the NotInList event to trap the error that occurs when a user types into a combo box a value that isn't in the underlying list. You can write an event procedure attached to this event that opens a pop-up form to gather any necessary data for the new entry, adds the new entry to the list, and then continues where the user started. This solution demonstrates how to create combo boxes that accept new entries by using the NotInList event and the OpenArgs property of forms.

Load the sample database 09-06.MDB and open the frmDataEntry form in form view. This form allows you to select a U.S. state from the combo box, but the list is purposely incomplete for the example. To enter a new state, type its abbreviation in the form and answer Yes when Access asks whether you want to add a new record. A form will pop up, as shown in Figure 9-18, to collect the other details (in this case, the state name). When you close the form, you'll be returned to the original data entry form with your newly added state already selected in the combo box.

Figure 9-18: Adding a new record to the underlying table

To add this functionality to your own combo boxes, follow these steps:

Import the basNotInList module from 09-06.MDB into your application.
Open your existing form in design view and create the combo box to which you wish to add records. Set the combo box properties as shown in Table 9-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 reprint rights for this chapter

You'd like to add some pizzazz to your application. You've seen animated buttons in other applications; how do you create them on your forms?

Access command buttons have an under-documented property called PictureData that stores the bitmap displayed on the button face. This solution examines two ways to use this property. First, you will learn how to create "two-state" buttons with pictures that change when you click on them. Next, you will learn how to create continuously animated buttons that cycle through a set of pictures at all times, using the form's Timer event to display a smooth succession of bitmaps.

Load 09-07.MDB and open frmAnimateDemo in form view (Figure 9-20). The top two buttons are two-state buttons whose pictures change when you click them. The Copy button (on the top left) shows a second document, and the Exit button (on the top right) shows the door closing just before it closes the form. The bottom two buttons are examples of animated button faces. (Only the Exit button on this form actually does anything when you press it.)

Figure 9-20: The frmAnimateDemo form

Section 9.7.2.1: Two-state buttons

To add a two-state animated button to your form, follow these steps:

Open your form in design view. Place a pair of command buttons on the form. The first button should be sized correctly for your pictures and be located where you want the button to be displayed. The second button can be located anywhere and can be any size. For example, the two-state command button in the top left corner of frmAnimateDemo was created with cmdCopy and cmdCopy2. The cmdCopy button is shown selected in design view in Figure 9-21; cmdCopy2, which has been reduced in size to save space, is located just to the left of cmdCopy. Set the Visible property of the second command button to No.

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 reprint rights for this chapter

You have a dialog with a lot of options, most of which are needed only in specific situations. You'd like to create this form as an expanding dialog, similar to forms that have an Advanced button revealing more options. How can you do this with your own form?

You can make a hidden section of the form become visible at runtime, and use the Window → Size to Fit Form command to force the form to expand to fit its new dimensions. This solution shows you how to create this type of form using an expanding form footer. You'll also learn how to minimize screen flashing while resizing the form by manipulating the form's Painting property.

Follow these steps to create your own expanding dialog form:

Create a new form. To make the form look like a dialog, set the properties of the form as shown in Table 9-6. Some of these property settings are optional, since the expanding technique will work with non-dialog forms too. The settings for the DefaultView and AutoResize properties are required.

Table 9-6: Property settings for a dialog form
Property	Value
DefaultView	Single Form
ScrollBars

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 reprint rights for this chapter

Access ships with the ActiveX Calendar control. How can you incorporate this and other custom controls into your Access applications?

ActiveX controls are not as commonly used in Access as they are in development environments such as Visual Basic, and some controls that work in other environments don't work well in Access. However, a number of controls have been created to work well in Access, and Microsoft ships one such control with the product: a very useful Calendar control. This solution shows you how to use the Calendar control in both bound and unbound modes. You'll also learn how to create a general-purpose reusable pop-up calendar form.

Load the 09-09.MDB database and open frmAppointment1 in form view (see Figure 9-27). Create a new record, selecting a date by using the Calendar control's Month and Year combo box controls to navigate to the desired month and then clicking on the date on the calendar. Complete the rest of the record and close the form. Now open the tblAppointment table to verify that the date you selected was stored in the ApptDate field of that record.

Figure 9-27: The frmAppointment1 form

Open frmAppointment2 in form view and select a date by clicking on the calendar button to the right of the ApptDate text box. A pop-up form will be displayed, where you can select a date again using the Calendar control (see Figure 9-28). Double-click on a date to select it and close the calendar pop-up form, or click once on a date and use the OK button. You may also wish to experiment with the Go to Today button, the Month and Year navigation buttons, and the Cancel button.

Figure 9-28: Selecting a date using the frmPopupCal form

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 reprint rights for this chapter

Access allows you to control the built-in status meter using the SysCmd function, but you have no control over the location or appearance of this status meter. How do you create a status meter that you can control?

You can create a status meter based on an Access form and control it using VBA routines. The status meter is composed of a Rectangle control and a Label control. By updating the Width property of the rectangle, you can control the meter's progress. Additionally, by updating the Caption property of the label, you can insert messages such as "50% complete". All the internal workings of the control can be encapsulated (hidden) inside the form using Let and Get property procedures and a global wrapper function.

For an example of a programmatically controlled status bar, open and run frmTestStatusMeter from 09-10.MDB (see Figure 9-32). To start the status meter, click the Start button and frmStatusMeter will pop up. If you want the status meter to include a Cancel button, check the Include Cancel button checkbox before clicking the Start button. The status meter will slowly advance to 100% and then close. If you've included a Cancel button, you can click on it at any time to immediately close the status meter and notify the calling form (frmTestStatusMeter) that the cancel has been requested.

Figure 9-32: The frmStatusMeter form

Section 9.10.2.1: Create a generic status meter

To create a generic status meter for your own application, follow these steps (or skip these steps entirely and import frmStatusMeter and basStatusMeter from 09-10.MDB into your database):

Create a form and set its properties as shown in Table 9-10.

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 reprint rights for this chapter

Access offers native support, right out of the box, for multiuser applications. But this additional power brings with it some additional problems, chiefly those of coordinating multiple users who may be spread across a large network. This chapter explores some solutions to common problems in multiuser applications. You'll learn how to use a shared database table to help your users communicate with one another and see how to find out which users are logged in at any given time. You'll also learn how to implement basic transaction logging, how to determine who has a record locked, and how to prevent a user from locking a record for an excessive time period. Because multiuser applications often use Access security, we also explore the security system in detail. For instance, you'll learn how to properly secure your database, how to keep track of your users and groups, and how to check if they have blank passwords. You'll also see how you can maintain separate but synchronized copies of a database using Access replication.

The database you've developed contains sensitive data to which you wish to limit access. You'd like to be able to create different classes of users so that some users have no access to this data, others can read the data but can't change it, and still others can modify the data. At the same time, you don't want to secure every object in the database this way; you'd like to apply security only to selected objects. Is this possible with Access?

Access supports two forms of security: workgroup-based security and database-password security. If you use the simpler database-password security system, you can assign only a single password to the entire database, which is inadequate for your purposes. Fortunately, your needs can be met by using the more sophisticated workgroup-based security system. However, securing a database this way can be tricky. This solution guides you through the process, starting with a completely unsecured database and finishing with a well-secured database that should meet your needs.

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 reprint rights for this chapter

The database you've developed contains sensitive data to which you wish to limit access. You'd like to be able to create different classes of users so that some users have no access to this data, others can read the data but can't change it, and still others can modify the data. At the same time, you don't want to secure every object in the database this way; you'd like to apply security only to selected objects. Is this possible with Access?

Access supports two forms of security: workgroup-based security and database-password security. If you use the simpler database-password security system, you can assign only a single password to the entire database, which is inadequate for your purposes. Fortunately, your needs can be met by using the more sophisticated workgroup-based security system. However, securing a database this way can be tricky. This solution guides you through the process, starting with a completely unsecured database and finishing with a well-secured database that should meet your needs.

Before you can properly secure your database, you must have a security plan. Consider who will be using the database and what security permissions those users should have for each database object. With a plan in place, you can go about securing your database.

Section 10.1.2.1: Make a security plan

The first step in creating a security plan is to make a list of the people who will be using the database. Write out the names of the users and put them into distinct groups. A user can be a member of more than one group, but you need to assign each user a unique name. Users will have to type in their usernames each time they log into Access, so you may wish to keep the names as short as possible (but still unique). In a small workgroup, you may be able to use an individual's first name; in larger settings, you may need to use the first name plus the first letter of the last name or some similar scheme to ensure uniqueness. For example, if you were charged with designing a secured database for the solution company, you might come up with the users and groups in Table 10-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 reprint rights for this chapter

You have a database that you'd like to distribute to mobile salespeople. Multiple users update the central copy of the database on a daily basis, and the salespeople also need to make updates to their own copies of the database. Is there any way to let everyone make updates and synchronize these copies when a salesperson returns to the office and plugs into the network?

Access 95 introduced a powerful feature called replication, which allows you to keep multiple copies of the same database synchronized. Subsequent versions of Access have continued to improve on replication. In this solution, we discuss how to set up a database for replication, how to synchronize the replicas, and how to deal with synchronization conflicts.

Although it's easy to implement, it's difficult to undo the effects of replication. We recommend that you create a copy of your database and work with that copy while learning about replication. Do not experiment with a production database until you are ready to handle any problems that may arise.

Section 10.2.2.1: Replicating a database

The steps for replicating a database using the Access menus are as follows:

Back up the database and safely store the backup.
Select Tools → Replication... → Create Replica. A dialog will appear informing you that the database must be closed before you can create a replica and that the database will increase in size. Choose Yes to proceed. A second dialog will ask you if you want to make a backup of the database before replicating it. Choose Yes if you didn't make a backup in Step 1, or No if you did. If you choose Yes, a backup of your database will be made with the .BAK

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 reprint rights for this chapter

You want to keep a permanent record of activities in your database. With multiple users simultaneously changing data in your application, how can you keep track of who made which changes?

Client/server databases such as Microsoft SQL Server offer built-in transaction-logging facilities that provide both a permanent record and a way to recover from disasters by replaying the transaction log. This solution demonstrates a simpler transaction log using Access that tracks users and their edits without saving all the details that would be necessary to recreate the edits entirely.

Start Access and load 10-03.MDB. Open frmBook and add a few records, update some existing records, and delete some records. Then review the information in tblLog; you'll find a record in this table for each change you made, as shown in Figure 10-15.

Figure 10-15: Examining changed records

To add this simple logging capability to your own database, follow these steps:

Create a new table, tblLog, with the fields shown in Table 10-3.

Table 10-3: Fields in tblLog
Field name	Data type
ActionDate	Date/Time
Action	Number (Byte)
UserName

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 reprint rights for this chapter

When you have multiple users logged into your application, you want them to be able to communicate quickly and easily with one another. You need a simple interface for sending notes back and forth so users can check whether anyone else is editing a particular entry, compare notes on workflow, and so on. How can you implement this in Access?

You can keep your notes in a table in a shared database to which all users have access. Whenever someone writes a note to another user, that note is added as another record in this table. By using a form that makes use of the Timer event, you can monitor the status of this table from any Access application and notify users when new messages have arrived.

This solution employs two files, 10-04fe.mdb and 10-04be.mdb. Before you can try it, you'll need to link the data tables from 10-04be.mdb (the "backend" or data database) to 10-04fe.mdb (the "frontend" or application database). Linking a data table allows you to use a table from one Access database within another Access database. Start Access and load 10-04fe.mdb. Choose File → Get External Data → Link Tables, and select 10-04be.mdb as the Access link database. At the Link Tables dialog, select tblMessage and click OK, as shown in Figure 10-16.

Figure 10-16: Linking a data table

Now you can test-drive this solution by sending a message to yourself. Open both frmSendMail and frmReceiveMail. Minimize the Receive Mail form. Select your username from the To combo box. If you haven't altered the default Access security settings, your username will be Admin, which should be confirmed in the From text box. Enter any message and click the Send Message button. In Figure 10-17, Peter has used frmSendMail to compose a message to Jean.

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 reprint rights for this chapter

As the database administrator, you want to be able to track users and their groups within your workgroup. You know you can use Tools → Security → Print Security to print a report of users and groups, but you'd like to be able to use that information as part of the applications you write. How can you gather the information you need?

Using Data Access Objects (DAO), you can retrieve all the information you need about users' names and groups. Once you have that information, you can use it in creating your applications.

The sample form frmUserGroups in 10-05.MDB fills tables with the information you need and presents it to you in a list box. To test it, open and run frmUserGroups. Figure 10-20 shows the form in use for a sample workgroup.

Figure 10-20: frmUserGroups shows users and groups for a sample workgroup

To gather this information in your own applications, follow these steps:

Create the tables you'll need to hold the information. Either import the three tables from 10-05.MDB, or use the information in Table 10-13 to create your own.

Table 10-13: Table layouts for gathering user/group information
Table name	Field name	Field type	Primary key?
tblGroups

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 reprint rights for this chapter

You've secured your database so that certain classes of users can't edit data using a particular form or run a specific report, but this doesn't prevent them from trying to open the form or report and receiving a permission error. You'd like your application to adjust itself based on the current user's security level. Is there any way to accomplish this?

Using VBA code, you can create a function that determines if the current user is a member of a security group. Based on the value this function returns, you can change any runtime property of any form or control, thus adapting your application to the user's security level.

Because this solution makes use of Access Security, you'll need to join the workgroup you created when you secured the database before you can try the sample database.

Now start Access. You will be prompted for a username and password. Enter the name of a user from Section 10.1.2 's Table 10-1. With the exception of the Paul and Admin accounts, the passwords for these are blank. (The passwords for the built-in Admin account and the Paul account are both "password"; note that case is significant.)

Load 10-06.MDB and open the frmSwitchboard form. Depending on which user you logged in as, you will see either a Manager-, Programmer-, or Default-level form. For example, Manager-level users will see two Manager buttons and a Close button. In addition, a Close menu item will be included in the File menu. In contrast, a member of the Programmers group will see two Programmer buttons, no Close button, and no File → Close menu item.

To implement this system in your own database, follow these steps:

Import the basGroupMember module into your database.
For each form you want to customize at runtime based on the user's group membership, attach an event procedure to the form's Open event that calls 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 reprint rights for this chapter

As database administrator, you need to ensure that every member of your workgroup has an Access password. You can use the NewPassword method to create a new password, and you understand why you can't retrieve the value of a user's password, but you need a way to find out whether a user has established a password yet. You'd like to create a list of all users, indicating which ones don't have passwords. How can you do this?

You can't retrieve users' passwords, but there's an easy way to find out if a user has a blank password: simply try to log onto the user's account using a blank password. If you succeed, you know the user has no password. With a lot of users this becomes a tiresome process, but fortunately, you can automate it using DAO and the CreateWorkspace method.

The frmUserPasswords form fills a table with a list of users and whether their passwords are blank and then presents this information to you in a list box. To test it, open and run frmUserPasswords from 10-07.MDB. Figure 10-21 shows the form in use for a sample workgroup.

Figure 10-21: frmUserPasswords shows users and password status for a sample workgroup

To use this information in your own applications, follow these steps:

Create a table to hold the information. Either import the table tblUsers from 10-07.MDB, or use the information in Table 10-16 to create your own table. Figure 10-22 shows the table in design mode.

Table 10-16: Table layouts for gathering user/group information

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

Section 8.2.2.1: Graphic and memo controls

Section 9.7.2.1: Two-state buttons

Section 9.10.2.1: Create a generic status meter

Section 10.1.2.1: Make a security plan

Section 10.2.2.1: Replicating a database