Forms and Wizards

Andy Baron

This month, Andy Baron takes a turn at bat here at Access Answers. He investigates some tricky forms issues that affect all versions of Access, and he tells you how to launch an Access 97 wizard from your code.

I'm using two combo boxes on a continuous form—one for categories, cboCategories, and one for items, cboItems. When the user selects a category, the list of available items should change. I thought I could do this by putting a criterion in the rowsource of cboItems that references cboCategories, and then requerying cboItems in the AfterUpdate event of cboCategories. But some of my items are disappearing after they've been entered. Can this work on a continuous form?

Combo boxes that affect each other in the way that you describe are often called cascading combo boxes, and users sure do love them when they work. I'll bet that problem you're having results from the fact that the bound column in cboItems is hidden. If the bound column were the displayed column, you wouldn't have this problem. What's causing some items not to show up is that those items aren't in the rowsource, when another row with a different category has the focus. Access combo boxes can only display an item that isn't in its combo box rowsource when the displayed column is the same as the bound column.

The form shown in Figures 1-3 illustrates this. This form allows you to switch between three different continuous subforms. One uses a combo box bound to the displayed item name. The second uses a combo box that displays the item name, but the combo box is bound to a hidden numeric Item ID. The third also uses an Item ID, but the combo box displays the ID.

Figure 1: Cascading combo boxes with the bound column displayed

Figure 2: Cascading combo boxes with the bound column hidden

Figure 3: Cascading combo boxes with the bound column displayed with another column

So, you have a couple of options. You could change the combo box to display the bound column, but it's probably an obscure number that has little meaning to the user, which is probably why you're hiding it in the first place. My advice is to reconsider your data design.

It's very common for Access developers to use many hidden autonumber (or counter) surrogate primary keys in tables that have perfectly good text fields that are natural primary keys. In your application, I'd guess that the item names that are displayed are all unique values. You might be thinking that performance would suffer by using that longer text value as the primary index in the Items table and as a foreign key in related tables. But the performance advantage you're getting might be negligible, and by using the text as the key, you'd avoid many joins that are now required in your queries to get the text value. By enforcing referential integrity with cascade updates, you can ensure that any changes to the text in the primary table will propagate to all related tables. The only time a hidden surrogate key makes much sense to me is when you're substituting it for a composite natural key that contains many fields, or when there's a demonstrated significant performance advantage (see Peter Vogel's article, "The Trouble with Normal," in this issue for a data-design based discussion of autonumber fields—Ed.).

One issue that you didn't raise is the fact that using cascading combo boxes in your continuous form, even when it's displaying the way you want, does force some denormalization of your data design. The Categories combo box must be bound to a field in your subform's recordsource. If it were unbound, it would show the same value on all rows. But really, you shouldn't have to store the category with the item in the detail records you're creating.

Category is an attribute of Item that you're probably already storing in the primary Items table; that's how cboItem is able to display the right choices based on the category selected in cboCategory. The SQL of cboItem is something like:

SELECT tblItems.Item 
FROM tblItems 
WHERE tblItems.CategoryName = _
 [Forms]![MainForm]![SubForm].[form]![cboCategories];

On a single-view form, cboCategory could be an unbound control, and you wouldn't need to store the value. But to use a continuous form, this denormalization is required, so be sure that you add code that protects against a user changing the category to an invalid value for an item that was already entered with a different category. For example, if a user selects Vegetables as the category and Avocado as the item, you don't want the user to be able to go back to the row and change Vegetables to Animals, leaving Avocado as the item. In
the sample database, I handle this by setting cboItem to null in the AfterUpdate event of cboCategory. This forces the user to choose an item from the combo box list, which will then only contain items from that category. I also put code in the GotFocus event of cboItem that checks for a null value in cboCategory and forces the user to select a category before choosing an item.

In your question, you mentioned that you're requerying cboItem in the AfterUpdate event of cboCategory. This strategy works on a single-record form, but on a continuous form you need to move that to the GotFocus event of cboItem. You must assure that the rowsource is recalculated if the user is on a row that has one category and then clicks directly into cboItem on another row that has a different category. Here's the full code for cboItem_GotFocus:

Private Sub cboItems_GotFocus()
    If IsNull(Me!cboCategories) Then
        MsgBox "Please select a category first."
        Me!cboCategories.SetFocus
    Else
        Me!cboItems.Requery
    End If
End Sub

The sample database was created with Access 97, but so far this answer applies to Access 2.0 and Access 95 as well. However, there's a subtle change that occurred between Access 95 and Access 97 that could threaten the integrity of your data if you're using Access 97. What happens if the focus is on cboItems in a row on the subform, and the user navigates to a different record on the main form? Each version of Access handles this a
little differently.

In Access 2.0, when the user is in a subform and navigates to a different record on the main form, the focus moves to the first control of the first record on the subform. So, in our example, if the user is in cboItems, the focus will change to cboCategories, which causes no problem.

In Access 95, the focus will also move to the first record of the newly requeried subform, but it won't move to the first control. The focus will stay on cboItems. Here, too, there's no problem for us because the GotFocus event of cboItems will fire and its rowsource will be requeried. So, even if the category in this row is different from the category in the row the user was previously on, the combo box list will be requeried to contain the correct items.

However, in Access 97 there's a difference that isn't readily apparent. The focus stays on cboItems, as it does in Access 95, but the Got_Focus event doesn't fire! This makes sense, perhaps, since the control that had the focus before the navigation still has the focus even though it's on a different row. Unfortunately, it isn't what we expect, and it can cause a problem for our cascading combo boxes. If the category on the new row is different from the category on the old row, then the dropdown list will include the wrong items, because the rowsource won't have been requeried. To fix this, you need to add code that requeries cboItems in the On Current event procedure of the main form, if you're using Access 97:

Private Sub Form_Current()
    Me!fsubItemList!cboItems.Requery
End Sub

If you follow all of these steps, you can give your users the convenience of cascading combo boxes on continuous forms without threatening the integrity of your data.

I have a mainform/subform where all the fields on the main form except the primary key have default values that get filled in automatically for a new record. The primary key is an autonumber field. My problem is that if the user accepts all the default values and enters a record on the subform, an error comes up: "Index or primary key can't contain a null value."

This error is coming up because the table that your subform is based on uses the primary key of the main form as part of its primary key. Your users are getting the error because the primary key in the main form hasn't been assigned yet. In our sample application, we can illustrate this by using an autonumber for ItemListID and a default value for ItemListDate on the main form (see Figure 4).

Figure 4: Attempting to add a child record before a master record

Autonumber values aren't filled in until the record they're on becomes dirty. Since your user hasn't actually entered anything on the new main form record, it isn't dirty yet, even though the default values are showing. You can use code to dirty the record by setting one of the fields that has a default value equal to itself. The place to put this code is in the On Enter event procedure of the subform control:

Private Sub fsubItemList_Enter()
    If IsNull(Me!ItemListID) Then
        Me!ItemListDate = Me!ItemListDate
    End If
End Sub

That will cause the autonumber value to be filled in. Some developers like to maintain their own incrementing primary keys, rather than using autonumbers, using code called in the BeforeInsert event procedure of the form to set the value. In that scenario, you'd call the event procedure that contains the call to your custom code:

Private Sub fsubItemList_Enter()
    If IsNull(Me!ItemListID) Then
        Form_BeforeInsert False
    End If
End Sub

The "False" argument is there because Access's BeforeInsert event procedure has a required parameter, Cancel.

Is there a way that I can launch one of the built-in Access wizards from my Access 97 application? I'm particularly interested in the Label Wizard and the Report Wizard.

Yes, there is. Just be aware that your users must own a full retail version of Access. The Office Developer's Edition doesn't give you the right to distribute the Access wizards with a runtime application.

Microsoft has released versions of the Access 97 wizards on its Web site (http://www.microsoft.com) that can be opened and inspected. The organization of the Web site is undergoing a lot of changes as of this writing, so I can't give you the exact URLs, but the file names to search for are: Wzmain80.exe, Wzlib80.exe, Wztool80.exe, and Utility8.exe. The code isn't commented and can be very hard to follow, but any serious Access programmer will find it fascinating.

When you poke around in the wizards, you'll discover that the Report and Label wizards are both in Wzmain80.mde. The functions to invoke them are called frui_Entry and mlbl_Entry. These both take a string argument that's the name of a table or query to be used as the recordsource of the report. You can't use a SQL string for this recordsource argument. It must be a string expression containing the name of a saved table or a saved query that returns records. The Report Wizard allows you to pass in a zero-length string ("") and pick the recordsource in the first pane of the wizard. Since frui_Entry is also used to launch the Form Wizard, frui_Entry requires that you pass in a second argument, either acForm or acReport (intrinsic constants holding the values 2 and 3) to specify which wizard you want to launch.

So, what exactly does the syntax for calling these functions look like? As with any function where the return value isn't needed, you could use the Call statement:

Call frui_Entry("MyTableOrQueryName",acReport
Call mlbl_Entry("MyTableOrQueryName")

But wait—don't you need to tell VBA where to find these functions? You sure do, and there are two ways to do that. The first way is to set a reference to the wizard, which you'd do by choosing Tools | References when you have a module open in design view. After opening References, click the Browse button and find the wizard file, Wzmain80.mde (it will normally be in the Office directory). Select the file to place it in the References dialog box, and then check the checkbox next to it in the dialog box to set the reference. The code above will now compile without complaint and will work.

What if you don't want to rely on always having that explicit reference to the wizard? There's another syntax you can use that will work without it. My colleague, Chris Bell, introduced this technique in the October 1997 Access Answers column (see "The Return of Access Answers"). The syntax is:

Application.Run "wzmain80.frui_Entry", "MyTableOrQueryName",acReport
Application.Run "wzmain80.mlbl_Entry", "MyTableOrQueryName"

The Run method of the Application object was introduced in Access 95, when Access first became an Automation server, to allow other applications to call Access procedures. It can also be used to allow one Access application to call functions in another. Note that the function name is preceded by the name of the VBA project that contains it, which in this case is the name of the wizard. This project.procedure pair is enclosed in quotes because that first argument to the Run method must be a string expression. The following arguments, separated by commas, can contain up to 30 values to be passed to the parameters of the called procedure. This syntax can be used in your Access application to invoke the wizards, even if you haven't set an explicit reference, because the wizards are installed in the same directory as msaccess.exe.

Your next question might now be, "How can I present my user with a list of saved tables and queries to choose from?" The sample database contains the form shown in Figure 5.

Figure 5: A form with a list of available tables

The button to launch the Label Wizard is enabled only after a selection is made from the Recordsource list box. There are several ways to go about populating this listbox.

The wizards often need to get lists of various Access objects for the user to choose from, and they do it by declaring and calling special functions that are contained in the msaccess.exe executable file. Those function calls rely on complex user-defined types, and the syntax is hard to decipher. Fortunately, it's documented and wrapped in some friendly functions in the Access 97 Developer's Handbook, by Litwin, Getz, and Gilbert, published by Sybex, which I heartily recommend. But there are simpler ways to get what you need here.

You can use DAO to walk through the Tabledefs and Querydefs collections, gathering up the names. You simply need to filter out the system objects that you probably don't want your users to see. In addition to the system tables that all have names starting with "Msys," Access 97 also now creates hidden saved queries anytime you use a SQL string in a recordsource or rowsource property. These queries aren't visible in the database container, even if you go into Tools | Options and choose to show system objects and to show hidden objects. However, these queries will be included if you walk through the querydefs collection (see "Is the Query Compiled?" by Michael Kaplan and Julianne Lee in this issue for a more detailed discussion—Ed.).

To populate the Recordsource listbox in the sample form, I used a list-filling callback function. I won't take the space here to explain the rules for setting up such a function. This function is covered in the Access help topic for the RowSource Type property. One aspect of this particular list-filling function that might be of interest is that this one creates a multicolumn listbox, whereas most example code only deals with one column. The first column displays the type of object (table or query), and the second column, which is the bound column, displays the object's name. The entire function is printed below, but I'll just focus on the section that fills the array astrValues() with the names of all the tables and queries that aren't system objects.

Public Function ListTablesQueries(ctl As Control, _ 
  lngID As Long, lngRow As Long, lngCol As Long, _ 
  intCode As Integer) As Variant
Static db As Database
Static astrValues() As String
Static intCount As Integer
Dim intI As Integer
Dim strTemp As String
Dim tdf As TableDef
Dim qdf As QueryDef
Const conSystemQuery = 3

Select Case intCode
    Case acLBInitialize
        ReDim astrValues(0 To 1, 0 To 0)
        Set db = CurrentDb
        For Each tdf In db.TableDefs
            'Filter out system tables
            If (tdf.Attributes And _ 
              dbSystemObject) = 0 Then
                strTemp = tdf.Name
                ReDim Preserve _ 
                  astrValues(0 To 1, 0 To intCount)
                astrValues(0, intCount) = "Table: "
                astrValues(1, intCount) = strTemp
                intCount = intCount + 1
            End If
        Next
       
        For Each qdf In db.QueryDefs
            'Filter out system queries
            If qdf.Type <> conSystemQuery Then
                strTemp = qdf.Name
                ReDim Preserve _ 
                  astrValues(0 To 1, 0 To intCount)
                astrValues(0, intCount) = "Query: "
                astrValues(1, intCount) = strTemp
                intCount = intCount + 1
            End If
        Next
        
        ListTablesQueries = True

    Case acLBOpen

        ListTablesQueries = Timer

    Case acLBGetRowCount
        ListTablesQueries = intCount

    Case acLBGetColumnCount
        ListTablesQueries = 2
    
    Case acLBGetColumnWidth
        ListTablesQueries = -1

    Case acLBGetValue
        ListTablesQueries = astrValues(lngCol, lngRow)

    Case acLBEnd
        intCount = 0
        Erase astrValues
    End Select
End Function

To filter out the system tables, I used the Attributes property of the tabledef object. Because this property is a bit field, I used the And operator to test for the binary value corresponding to dbSystemObject. Another viable approach would have been to simply use the Left$() string function to look for "Msys" at the start of the name.

Similarly, it's possible to use Left$() to test whether each querydef name begins with "~sq_" (the prefix tag that Access uses for all the hidden queries it creates). But instead, I snooped around and discovered that all these querydefs also have a distinct value of 3 in their Type property. This isn't a documented feature, but I used it because it's likely that you might also want to use the Type property to eliminate various other queries, like append or delete queries, that don't return records.

To limit the scope of this response, I didn't attempt to identify other possible kinds of hidden objects, which you might need to filter out. These could include tabldefs or querydefs that you named starting with "Usys," tabldefs with an Attributes property that includes dbHiddenObject, or objects that were hidden through the Access user interface by checking the Hidden box in the Properties dialog box. This last type of "hiddenness" is unfortunately not exposed by Access through DAO. The only way to check for it is to inspect the Flags column of MSysObjects, looking for a value of 8, which is also a completely undocumented and unsupported technique.

You might prefer to use a table-driven system to identify those particular tables and/or queries that you want to make available for use with the wizards. The list box could display the object type (that you might not even need to show) and a friendly description, and it could have a hidden bound column with the actual object name. Rather than using a list-filling function, you'd then use a rowsource query something like this:

SELECT tblObjectsToDisplay.ObjectType, 
tblObjectsToDisplay.ObjectDescription, 
tblObjectsToDisplay.ObjectName
FROM tblObjectsToDisplay
ORDER BY tblObjectsToDisplay.ObjectDescription;

It's always a challenge to attempt to let your users benefit from some of the great tools that ship with Access, while protecting them from shooting themselves (or you) in the foot. If you can pull that off, and if your users aren't working with runtime versions, then you might want to consider incorporating the powerful Access wizards into your application. s