Access Cookbook - Ken Getz [15]
To create a form similar to frmSurvey, follow the steps described in this solution, placing the combo boxes in the detail section of the form instead of the header. Create an event procedure in the AfterUpdate event of the first combo box, cboArtistID, to blank out and requery the second combo box, cboAlbumID. Because the artist may be different on different records in the form, cboAlbumID also needs to be requeried as you navigate from record to record. You can accomplish this by requerying cboAlbumID in the Current event of the form:
Private Sub Form_Current( )
cboAlbumID.Requery
End Sub
WARNING
Using related combo boxes in the detail section of a continuous form can cause problems. Unbound combo boxes will show the same value on every row, and bound ones may mysteriously turn blank when they lose focus. This happens if a dependent combo box has a displayed column that isn't also its bound column. You can demonstrate this by changing the DefaultView property of frmSurvey from Single Form to Continuous Forms. You'll find that cboAlbumID appears blank on all rows that have a different artist than the one selected on the current row. That's because the bound column in cboAlbumID is not the displayed column (the bound AlbumID column has a column width of 0). Access can't display a value that's not in the current row source unless it's in the bound column.
See Also
To fill a combo box programmatically, see Recipe 7.5 in Chapter 7. To optimize your combo box performance, see Recipe 8.3 in Chapter 8.
1.4. Make Formatted Date Columns Sort Correctly in a Crosstab Query
Problem
If you have a crosstab query that uses the built-in Format function to convert dates into text for column headings, Access sorts them alphabetically (Apr, Aug, and so on) rather than chronologically. For example, open 01-04.MDB and run the qryAlbumTypeByMonth1 crosstab query (see Figure 1-7). This query shows the cross-tabulation of the number of albums purchased by album type and the month the albums were purchased. The month columns are sorted alphabetically instead of chronologically.
Figure 1-7. The months in qryAlbumTypeByMonth1 sort alphabetically
When the purpose of using the month in a crosstab query is to examine chronological variation by month, this makes the crosstab query all but useless. Is there some way to tell Access to sort the columns by date rather than alphabetically?
Solution
The query properties sheet allows you to specify fixed column headings for a crosstab query. This solution illustrates how to use the ColumnHeadings property to specify column headings so that formatted dates sort chronologically.
Follow these steps to create a crosstab query with correctly sorted formatted-date columns:
Create a select query. Select Query → Crosstab to convert the query into a crosstab query.
Add the columns you want to the crosstab query. Use a calculation for the Column Heading field. This calculation should use the built-in Format function to convert a normal date into an alphabetic string for cross-tabulation purposes. This might be the day of week or the month of year—in the example shown in Figure 1-7, we took the date field, DateAcquired, and formatted it as a three-letter month string. Add the remaining fields to qryAlbumTypeByMonth2, as shown in Table 1-3.
All crosstab queries must have at least three fields: Row Heading, Column Heading, and Value.
Table 1-3. Field settings for the qryAlbumTypeByMonth2 crosstab query
Field
Table
Total