ACC1x: Preventing Users from Adding New Records

• Microsoft Access versions 1.0, 1.1

SUMMARY

The DefaultEditing form property, when set to AllowEdits, allows users to both modify existing records and to add new records. Microsoft Access has no built-in mechanism to prevent users from adding new records.

MORE INFORMATION

The following example demonstrates a sample, user-defined Access Basic function that you can use to prevent users from entering new employees in the Employees form in the sample database NWIND.MDB:

1. Type the following Access Basic function in a new or existing module:

         Function PreventNew ()
            Dim X
            On Error Resume Next
            X = Screen.ActiveForm.Bookmark
            If Err = 3021 Then DoCmd GoToRecord , , A_LAST
         End Function
   
      NOTE: There are two commas between GoToRecord and A_LAST above.
   
   

2. Change the OnCurrent property of the Employees form to read as follows:

         Form: Employees
         ----------------------------
            OnCurrent:  =PreventNew()
   
      Note that the pointer is placed in the last record of the Employees form
      when you attempt to gain access to or navigate to the new record.
   
   

How PreventNew() Works

Whenever you navigate to a different record, the OnCurrent property runs the PreventNew() function. This function attempts to reference the Bookmark property associated with the current record. All records are assigned a string, called a bookmark, that uniquely identifies each record.

However, a new record that has not yet been saved has no bookmark associated with it. Any attempt to reference the bookmark of a new record generates an error message. If an error occurs, the PreventNew() function immediately performs a GoToRecord action to navigate to the last record in the form.

Using PreventNew() with Forms That Contain No Records

If the table or query that the form is based on contains no records, PreventNew() does not prevent you from adding a new record to the form. You must first test to see if records exist in the table or query prior to opening the form. You can use the following Access Basic function to test whether records exist in a table or query.

      Function RecordsExist (TableQueryName As String)
         Dim DB As Database
         Dim DS As Dynaset
         Set DB = CurrentDB()
         On Error Resume Next
         Set DS = DB.CreateDynaset(TableQueryName)
         DS.MoveFirst
         If Err <> 0 Then
             RecordsExist = False
         Else
             RecordsExist = True
         End If
     End Function