ACC1x: Preventing Users from Adding New Records |
Q100465
The information in this article applies to:
-
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:
- 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.
- 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
Given the name of the table or query, this function will return True (-1)
if records exist, or False (0) if no records exist.
Another method you can use is the DCount() function. However, the DCount()
function may be significantly slower than the RecordsExist() function in
that it must count all the records in the database whereas RecordsExist
simply tests to see if there is a "first" record in the recordset.