Creating an Access application for other people means anticipating how the users are going to interact with your program. Without clear direction from the program or you, users can be easily confused by even simple things like the order in which data is to be entered.

One of the basic tools in Access is the subform which allows for the input of records related to a master record. A classic example is a database which stores customer orders and where each order might have multiple items (i.e. order lines) associated with it.

The screenshot shown here shows the table structure for the database where the parent table, “Orders”, stores the main information on the order such as date and sales rep and the OrderLines table stores the information for each item on the order. It also stores an OrderID value that references the primary order record for each item.

After you’ve created the tables, you need the form to enable the user to enter the data. The next screenshot shows a standard form and subform arrangement for the two tables. It’s not very elaborate and its easy to think that it’s self-explanatory … but sometimes it isn’t.

Sometimes, a user will want to enter the child data first, in this case order lines. Users can’t be expected to automatically understand table structures and referential integrity issues. If that happens, they will probably get errors like these. The form expects an OrderID for both tables but that value is null so the item records cannot be related to the main order. This causes Access to spit out errors that are very confusing for the average user.

That’s where it’s up to you as the developer to provide some guidance. You could add some instructions to the form but users are no more likely to read the documentation than you are so it’s best to clarify the order of the data entry by gently enforcing it using the tools in Access.

A Simple Bit of Code

My preferred way of carrying out any actions is to use Visual Basic for Applications (VBA). It provides a great selection of events on which you can act through code and allows for a lot of flexibility in your application.

The subform object actually has only two events – On Enter and On Exit. These fire whenever the users enters or exits the subform from the main form. In this case, I’m going to use the Enter event because I want the code to respond when the user tries to use the subform.

Private Sub subfrmOrderLines_Enter()

On Error GoTo errHandler

If IsNull(Me.OrderID) Then
    MsgBox "Please enter the main order details, such as customer and order date,
        before entering items to the order.", vbOKOnly, "Order details required 
        ..."
    Me.Customer.SetFocus
End If

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."

End Sub

The VBA code above is pretty basic. The IsNull() function tests the OrderID field on the main form. I use the AutoNumber field type for most of my primary keys so If the user has not entered any order details, the Order table will not have generated a new key for the record. When the user tries to use the subform, it will use the SetFocus command to kick the cursor back to the Customer field and display a helpful message box. Otherwise, it lets the user enter the item records.