How do I prevent an error 2101 in Access when I have a button to save a record on a form?

Question

Here's something I think should work (and I believe I once had it working) in Microsoft Office Access.

I have a single record form with a few bound controls and a button to save the values on the form.

The button's OnClick handler is basically a

private sub btnSave_Click()
     me.dirty = false
end sub

Since I need to check the entered values, there's also a OnBeforeUpdate handler:

private sub form_beforeupdate(cancel as integer)

  if nz(me.SomeControl, "") = "" then
     msgBox("....")
     cancel = true
     exit sub
  end if

  ' ...  more checks ...

end sub

Now, when I click the button, and the handler reaches the me.dirty=false, the execution flow goes to the BeforeUpdate handler, which is expected. Since ComeControl is not filled, cancel is set to true and the sub is exited, which is still expected.

The problem is when the execution flow comes back to the buttons Click handler. The line me.dirty = false stops execution and the Run-time error '2101' The setting you entered isn't valid for this property is thrown.

This is not expected and I actually do believe I had this working.

Anyway, how do I go about having such a save button together with a BeforeUpdate handler?

Answer 1

Do you need to save the record at this point, given that saving is the default for Access? If so, perhaps trapping the error will suit:

Private Sub btnSave_Click()
On Error GoTo Err_Handler

 Me.Dirty = False

Exit_Here:
     Exit Sub

Err_Handler:
     If Err.Number=2101 Then
       'ignore or message
     Else
        MsgBox Err.Description
     End If

     Resume Exit_Here
End Sub

Answer 2

You've designed your interface in a way that I think is wrong. I don't let a user click a SAVE button until all the data is filled out.

Thus, the SAVE button is disabled until the point at which all the required fields are filled out. In order to accomplish this, you'd test the value of each required control in the control's AfterUpdate event. In general, you need to test the group of values, so I tend to write a function that tests all the required values and returns TRUE if all are filled out, and then use that in the AfterUpdate event of all the required controls:

  Private Sub txtLastName_AfterUpdate()
    Me!btnSave.Enabled = CheckRequiredFields()
  End Sub

Now, to make this easier on yourself, you can change CheckRequiredFields so that it is not just a function, but sets the Enabled property of the Save button, and then you can just paste "=CheckRequiredFields" into the AfterUpdate property of all the controls (this assumes you don't need to do anything else in the AfterUpdate events).

I do this all the time for dialog forms, disabling the OK button by default and enabling only the Cancel button. I then test that all fields have been filled out using the method above. Thus, the user can't perform the action until everything has been properly entered. This seems to me to be preferable to catching the missing data in the SAVE button -- that is, don't let the user even try to save until the record is done.