VBA Traps: Errors in Logic

This article is the third in a series discussing subtle bugs that lie unrecognized in your code until particular conditions are met.

Close Without Record Save

This is a serious bug in Access that has been present since 1994, and remains unfixed.

Performing a Close action on a form can cause you to lose edits without warning.

The problem occurs if there is any reason why the record cannot be saved. Usually this is a validation rule that is not met, but it can be a primary key duplication, a related record required, etc. Access simply discards your edits and closes the form without warning that the record was not saved. The first you know of it is when you discover that the record you entered before closing the form is not in your database.

To demonstrate the problem:

In Table Design view, set the Required property to "Yes" for some field, say Surname.
Create a form based on this table.
In Form Design view, add a button to the form to close the form. (There is a wizard to write the code.) Save the form.
Enter a new record, leaving Surname blank.
Click the button to close the form. The form will close without a warning that Surname is required, but the new record is not saved in your database.

Solution:

You must explicitly save the record before executing a Close action. For example:

    If Me.Dirty Then
        RunCommand acCmdSaveRecord
    End If
    DoCmd.Close

Subform records with no main form record

You have a table with a primary key, and related table with a foreign key. You have created a relationship between these two tables, with referential integrity. Now you create a main form with a subform for these two tables. Some of the records you enter in the subform seem to disappear never to be seen again. Where have they gone?

Referential integrity does not block Nulls in the foreign key. Normally Access fills in the foreign key in a subform to match the primary key in the main form. But if the main form is at a new record, Access merrily accepts the entry in the subform with a Null foreign key. These records never show up again in the subform, since they do not relate to any record in the main form.

You must take specific action if you want to prevent this occurring.

Solutions:

Do either (or both) of the following:

Set the Required property of the Foreign key to True in Table Design.
Use the subform's BeforeInsert event procedure to cancel (or question) the insertion:

        Dim frm as Form
        Set frm = Me.Parent
        If IsNull(frm.[ID]) Then
            Cancel = True
            MsgBox "Create a record in the main form first.", vbExclamation, "Required data."
            frm.[ID].SetFocus
        End If

Printing without saving current edits

Changes to the current record are not saved just because you click a button to perform some other action. Novice Access users often forget to force a record save before executing an action such as OpenReport.

Solution:

Include this line before executing an action such as OpenReport:

    If Me.Dirty Then
        Me.Dirty = False
    End If

Leaving Echo Off

You can significantly speed up some code by turning Echo off. In a macro, Echo is automatically turned on again when the macro ends, but this is not the case in VBA code. This means that the machine is left in an apparently non-responsive state if you forget to turn Echo back on again. If your code generates an error while Echo is off, the error message is suppressed, and you will not know why your program has stopped.

Solution:

In any procedure that turns echo off, include Echo True in the exit recovery section:

    Sub MySub
    On Error Goto Err_MySub
        ' Some code here.
        DoCmd.Echo False
        ' Rest of your code here.

    Exit_MySub:
        'Turns Echo on again HERE: works even after an error.
        DoCmd.Echo True
        Exit Sub

    Err_MySub:
        Debug.Print Err.Number & ": " & Err.Description
        Resume Exit_MySub
    End Sub

Hint:

While developing, use an AutoKeys macro that turns Echo on.

Ambiguous Operators

VBA supports bitwise operators for comparing the binary digits (bits) of two values. For example, the expression 4 And 7 evaluates the bit values of 4 (0100) and 7 (0111) and returns 4 (the bit that is on in both numbers.) Similarly the expression 4 Or 8 evaluates the bit values in 4 (0100) and 8 (1000) and returns 12 (1100), i.e. the bits where either one is true.

Unfortunately, the bitwise operators have the same names at the logical comparison operators: And, Eqv, Imp, Not, Or, and Xor. This can lead to ambiguities, and even contradictory results.

As an example, open the Immediate Window (Ctrl+G) and enter:
? (2 And 4)
This returns zero, since there are no bits in common between 2 (0010) and 4 (0100).

Now create this query:
SELECT (2 And 4) AS Result;
If you expect the result to be zero, you will be disappointed. In the context of a query the And performs a logical comparison only. In this context, 2 is evaluated True (any non-zero value is True), and so is the 4. Since both parts of the comparison are true, the And returns True. Consequently the result is displayed as -1 (the value for True.)

The same expression returns True in one context, and False in another.

So what happens if you use this expression in the Access interface? Type this into the Control Source of a text box:
=(2 And 4)
Access performs the comparison, not the bitwise operation. Expressions in Format Conditions are also interpreted as comparisons. It seems that the bitwise operators work only in the context of VBA.

An ideal solution would be to have different operator names for the bitwise operators and the comparison operators, to avoid the ambiguity. Changing Access or changing VBA at this stage is probably not an option. ADO has binary operators prefixed with B (e.g. BAND, BOR, ...), and these are supported in JET 4 (Access 2000), but only when the query is run under ADO (so not in the query interface, which uses DAO.) That really limits its usefulness, but here is how it works:

Function ShowBand()
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT (2 BAND 4) AS Result;", CurrentProject.Connection
    ShowBand = rs!Result
    rs.Close
    Set rs = Nothing
End Function

So, remember that bitwise operations work only in VBA. In other contexts, you get a logical comparison - the wrong result if you expected a bitwise operation.

Home

Index of tips

Top