It really doesn’t make any sense to save an Order without any details. Christopher R. Weber explains how, by enhancing your interface design, you can create an event that stops users from omitting the detail records.

Not every data integrity rule can be enforced through referential integrity. One of the features I really like about Access is its support of main forms with subforms. If you ever traced the events on a main form/subform (by using the Event History from the Northwind database, for example) you’d notice that once you’ve filled in all of the necessary fields on the main form and tabbed into your subform, the parent record is saved before the child record is created. For referential integrity purposes, this makes perfect sense, as it ensures that the parent record exists for the child records that your users are about to create. In fact, at this point your user could navigate to another record and leave the Order record without any detail lines at all. In a typical business, an Order without details doesn’t make any sense, but there’s no way to enforce this rule using Jet’s referential integrity. In this article, I’ll show you how you can prevent this from happening and add some neat features to your form along the way.

The OnNavigate event

In Access, the only events that you can use to check for the existence of detail records when a user moves to another Order (or closes the main form, shuts down Access, or any one of a number of other occurrences) are the OnCurrent and OnClose events. Unfortunately, while the OnCurrent event fires as you move from record to record, it fires just after the move. If you ever want to use the OnCurrent event to validate data, then you have to bookmark the current record (the one you just moved to), go back to check the record you just left (using a bookmark created earlier), and either return to the current record if the record passes the edit or stay on the old record if a business rule is violated. Eventually you write enough code that you dig yourself into a hole from which you’ll never climb out.

Other development tools provide an OnNavigate event that allows you to take action just before the user moves off of a record. Borland’s Delphi, JBuilder, and C++Builder have had this feature for several years. If you’re using ADO, you can take advantage of the Recordset’s WillMove event. But, if you enjoy working in Access and want to continue with DAO, then you’ll have to create your own OnNavigate event.

The easiest way to create your own OnNavigate event is to force your user to navigate with custom navigation buttons. “Oh no,” you’re thinking, “another navigation button article.” As you’ll soon see, though, I’ll show you some features of Access that you might not be aware of, and besides, my navigation buttons enable and disable themselves at the appropriate times.

Here’s an example of the Northwind Orders form using my custom navigation buttons (see Figure 1). Notice that the form is on the first record and that the previous and first buttons are disabled appropriately. Another neat feature: the record identifier nestled in between the navigation buttons shows the OrderID field for the current record’s position in the Recordset. You can find the sample database in the Download file and check out how I set up the buttons and record identifier in the Order form.

 Figure 1

Coding the buttons

The code for each of my navigation buttons looks very similar. The code first calls a function named ContinueNoDetails and, if the function returns True, uses the GoToRecord method of Access’s DoCmd object to move to the next record. Here, as an example, is the code behind the “go to previous record” button:

Public Sub cmdPrev_Click()

On Error Resume Next



If ContinueNoDetails() Then

DoCmd.GoToRecord acForm, Me.Name, _

record:=acPrevious

End If



If Err Then

MsgBox "Record not available", vbOKOnly, _

"Navigation cancelled..."

End If



End Sub

By adding the ContinueNoDetails routine to each of my buttons, I create a kind of pseudo-event that occurs just before the user leaves the record. I’ve converted the button’s OnClick event into my own OnNavigate event.

Shall we continue?

ContinueNoDetails returns a Boolean value indicating whether or not the user is ready to move to the next record. The user can move to the next record if either the record has details or the user has selected to continue without adding details. The user can also opt to halt the navigation because details have been omitted.

The routine first attempts to save the current Order by setting the Form’s Dirty property to False. No attempt is made to save the detail records. Saving the detail records is unnecessary because only one of two conditions can be true:

• the user hasn’t used the detail records subform, so there’s nothing to save
• the user has used the subform but has returned to the main form (even if it’s just to click the navigation button)

If the user created detail records on the subform, those records will all have been saved as soon as the user left the subform.

After saving the Order record, ContinueNoDetails then checks to see whether the parent record exists in the database. In this table, the OrderId field is an autonumber field that’s generated as soon as the record is saved. To determine whether the Order record has been saved, I only need to check whether the OrderId field is not Null. If it isn’t, that means the autonumber has been generated and the record exists.

If the parent record exists, the routine determines whether or not details have been created. Rather than retrieve the detail records from their table to see whether they exist, I just check the RecordCount property of the subform’s RecordSetClone. If it’s any number greater than zero, detail records exist for this order.

If the ContinueNoDetails code determines that no detail records exist, a message box pops up advising the user that there are no detail records for this Order. The user can choose to stay on the current record and add those details or move on without entering detail records. The function returns False if the user chooses to stay and add details, True if details exist or the user decides not to enter details. Here’s the code:

Function ContinueNoDetails() As Boolean

On Error GoTo Err_ContinueNoDetails



ContinueNoDetails = False

Me.Dirty = False

'check to see if we're on a saved record

If Not IsNull(Me.OrderID) Then

'determine if details exist

If Not CBool([Orders Subform].Form. _

RecordsetClone.RecordCount) Then

If MsgBox("Details missing. Continue?", _

vbQuestion + vbYesNo, _

"Please confirm...") = vbYes Then

ContinueNoDetails = True

End If

Else

'details exist

ContinueNoDetails = True

End If

Else

'new record

ContinueNoDetails = True

End If



Exit_ContinueNoDetails:

Exit Function

End Function

I’ve omitted some error handling code that can be found sample database. Now, when my user attempts to move off of a record that has no details, he or she is warned (see Figure 2). You can, of course, modify the code to not give the user the choice of leaving without entering detail records.

 Figure 2

But what about those buttons?

The form also enables and disables the navigation buttons at the appropriate times. This is where the form’s OnCurrent event comes in. In the OnCurrent routine, I call the NavButtons subroutine, which sets the enabled state of the buttons. While the OnCurrent event occurs too late to edit the record I just left, it occurs at exactly the right time to determine the state of the record that’s about to be displayed.

The version of NavButtons shown here uses the Recordset’s AbsolutePosition and RecordCount properties to determine which record is currently being displayed. In case you’re using Access 2.0 with an older version of Jet that doesn’t support the AbsolutePosition property, I’ve included a version of this routine in the sample download that uses a different technique.

Sub NavButtons()

Dim rst As Recordset

cmdLast.Enabled = True

cmdFirst.Enabled = True

cmdPrev.Enabled = True

cmdNext.Enabled = True



Set rst = Me.RecordsetClone

rst.Bookmark = Me.Bookmark



If rst.AbsolutePosition = 0 Then

cmdFirst.Enabled = False

cmdPrev.Enabled = False

End If

If rst.AbsolutePosition = rst.RecordCount-1 Then

cmdLast.Enabled = False

End If



End Sub

To make this completely foolproof, you’ll have to remove any navigation selections from your menus and custom toolbars. You’ll also have to override the page down and page up keys by setting the Form’s KeyPreview property to Yes and checking for those Page Up and Page Down keycodes (33 and 34) in the Form’s KeyDown event. Setting the KeyCode to 0 will suppress Page Up and Page Down:

Private Sub Form_KeyDown(KeyCode As Integer, _

Shift As Integer)

If KeyCode = 33 Or KeyCode = 34 Then

KeyCode = 0

End If

End Sub

Finally, don’t forget to call ContinueNoDetails in the form’s OnClose event.

With just a little bit of code you can provide direct support for a business rule (no Orders without details) that can’t be implemented through Jet’s referential integrity. Now, if Microsoft would just give us that OnNavigate event…

Other Pages That You Might Like To Read
 

Let Your Users Sort it Out
Zoom, Zoom, Zoom
Accessing Subforms 
Displaying Hierarchical Data in a TreeView