Recipe 6.2 Determine if You're on a New Record in a Form

6.2.1 Problem

Often, you need to do different things depending on whether the current row is the "new" row on a form. For example, you might want to display a certain message box only when adding records. How can you do this?

6.2.2 Solution

You can use a form's NewRecord property to determine if you are on a new record by checking its value from an event procedure attached to the OnCurrent event property or some other event property of the form.

Follow these steps to implement this functionality in your own forms:

  1. Create a new form or modify the design of an existing form.

  2. Create an event procedure for the form's Current event. In that event procedure, create an If...Then statement that will branch based on the value of the form's NewRecord property. The code of the event procedure should look like this:

    Private Sub Form_Current( )
        If Me.NewRecord Then
            ' Do something for a new record.
            ' Do something for an existing record.
        End If
    End Sub
  3. You may wish to alter some visual cue on the form to indicate whether you are on a new record. For example, you might change the text of a label, the text of the form's titlebar, or the picture of an image control. In the sample form, we changed the picture of an image control in the form's header, imgFlag, by copying the picture from one of two hidden image controls that are also located on the form. The final Current event procedure looks like this:

    Private Sub Form_Current( )
        ' Determine if this is a new record and change the bitmap
        ' of the imgFlag control to give the user visual feedback.
        ' See the Solution in Recipe 9.7 for an explanation of using the
        ' PictureData property.
        If Me.NewRecord Then
            Me.imgFlag.PictureData = Me.imgFlagNew.PictureData
            Me.imgFlag.PictureData = Me.imgFlagEdit.PictureData
        End If
    End Sub
  4. Create any additional code that reacts to the NewRecord property. In the sample form, we decided to remind the user to log in the new record when saving it. Thus, we created the following event procedure attached to the form's BeforeUpdate event:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Dim strMsg As String
        If Me.NewRecord Then
            strMsg = "You just added a new record " & _
             "(# " & Me.ContactID & ")" & vbCrLf & _
             "Please don't forget to log it in!"
            MsgBox strMsg, vbOKOnly + vbInformation, "New Record Added"
        End If
    End Sub

To see an example, load and open frmContacts from 06-02.MDB. Notice that the picture in the upper-left corner of the form changes to indicate whether you are editing an existing record (Figure 6-3) or adding a new record (Figure 6-4). In addition, when you save a newly added record, a message box is displayed that reminds you to log the new record (Figure 6-4). The message box does not appear when you save changes to an existing record.

Figure 6-3. The sample form indicates that you are editing an existing record
Figure 6-4. The sample form indicates that you are adding a record

6.2.3 Discussion

The NewRecord property is simple: its value is True when adding a new record and False otherwise. This property is True from the moment the pending new record becomes current until the moment the record is saved. NewRecord is reset to False right after the BeforeUpdate event; it is False during both the AfterUpdate and AfterInsert events.

The image control used to display the add/edit icon uses a trick to change its picture quickly. Rather than loading a bitmap image from a disk file, which would be slow, it copies the picture from one of two hidden "source" image controls on the form.

To do this, set the image control's PictureData property to the value of the PictureData property of another image control. Chapter 9 discusses the PictureData property in more detail.