ACC2: DefaultEditing Set to "Can't Add Records" Removes Filter

ID: Q129302

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use Access Basic code to set a form's DefaultEditing property to Can't Add Records, any filter that you apply to the form is removed and all the records are displayed.


RESOLUTION

To work around this problem, re-apply the filter after you set the DefaultEditing property to Can't Add Records.


STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.


MORE INFORMATION

Because the Can't Add Records option for the DefaultEditing property is not available in the OpenForm macro action's Data Mode argument, you must use Access Basic code to set the property in the form's Load event. But if you set the property in the form's Load event, when the form opens, any filter you apply to the OpenForm action's Filter Name or Where Condition argument is removed.

For more information about opening a form with the Can't Add Records data mode, please see the following article in the Microsoft Knowledge Base:

Q129301 ACC2: No "Can't Add Records" Data Mode Arg. in OpenForm Action

Steps to Reproduce Problem

The following example demonstrates the problem described in this article. For an example that demonstrates how you can work around this problem, see the "Work Around" section later in this article.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.
  1. Open the sample database NWIND.MDB.


  2. Open the Employees form in Design view.


  3. Enter the following code for the form's Load event procedure: 
    
      Sub Form_Load (Cancel As Integer)
         ' Set the DefaultEditing property to the value passed in the
         ' OpenArgs argument.
         Me.DefaultEditing = Me.OpenArgs
      End Sub


  4. Save, and then close the form.


  5. Open the Utility Functions module in Design view and choose Immediate Window from the View menu.


  6. Type the following statement in the Immediate window and press ENTER:

    DoCmd OpenForm "Employees",,,,,,"4"

    Note that the Employees form opens. You cannot add new records, but all the employee records are available.


Work Around

To open the form with a filter, pass the Where Condition argument and the specified DefaultEditing property setting in the OpenArgs argument. In the form's Load event, parse the components and apply the filter after setting the specified DefaultEditing property.
  1. Change the Employees form's Load event procedure to the following code: 
    
      Sub Form_Load ()
         Dim iSemicolon As Integer
         Dim iDefaultEditing As Integer
         Dim sWhereCondition As String

         ' Find the semicolon.
         iSemicolon = InStr(Me.OpenArgs, ";")

         ' If the semicolon is found...
         If iSemicolon > 0 Then
            '...parse the DefaultEditing property and WhereCondition
            'argument...
            iDefaultEditing = Left(Me.OpenArgs, iSemicolon - 1)
            sWhereCondition = Mid(Me.OpenArgs, iSemicolon + 1)

            '...and set the DefaultEditing property and WhereCondition
            'argument.
            Me.DefaultEditing = iDefaultEditing
            If sWhereCondition <> "" Then
               DoCmd ApplyFilter , sWhereCondition
            End If
         End If
      End Sub


  2. Type the following statement in the Immediate window and press ENTER:

    DoCmd OpenForm "Employees", ,,,,,"4;[Employee ID]=9"

    Note that the form opens with a filter, and you cannot add new records.



REFERENCES

For more information about the DefaultEditing property, search for "DefaultEditing," and then "AllowEditing, DefaultEditing Properties" using the Microsoft Access Help menu. 


Keywords          : kbusage FmsProp 
Version           : 2.0
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 9, 1999