Bound Form to Table - Cancel Operation

  • Thread starter Thread starter omsoft
  • Start date Start date
O

omsoft

Hello,

I am new to Access VBA. I have a table and a form bound to the fields in
that table.
When I open the form (click event on a button), the date entered on the form
is added to the table. At times just a blank record is added. I wan to make
sure that the record is only added when Submit button is pressesd. Otherwise,
the record is backed out.

Is there a way to do this? Any help would be greatly appreciated.

Thanks.
 
omsoft said:
Hello,

I am new to Access VBA. I have a table and a form bound to the fields in
that table.
When I open the form (click event on a button), the date entered on the
form
is added to the table. At times just a blank record is added. I wan to
make
sure that the record is only added when Submit button is pressesd.
Otherwise,
the record is backed out.

Is there a way to do this? Any help would be greatly appreciated.
Click to expand...


This problem would only occur if there is code that assigns a value to a
field on the form when the form is opened. The first thing to do, then, is
not to assign that value. Instead, you may consider setting a Default Value
instead.

The second thing to do, as you request, is to keep the form from saving its
record unless the Submit button is pressed. This is a bit more complicated,
because it's contrary to the way Access is designed to operate. To do it,
add a hidden check box control to the form, and call it, "chkOkayToSave".
By "hidden", I mean that you should set its .Visible property to False.

Now, in the form's Current event, put a line of code that sets the check box
to False:

Private Sub Form_Current()

Me.chkOkayToSave = False

End Sub

In the Submit button's Click event, put code that sets the check box to True
and forces a record save:

Private Sub cmdSubmit_Click()

Me.chkOkayToSave = True
Me.Dirty = False

End Sub

Finally, use the form's BeforeUpdate event to check whether the check box is
True. If not, cancel the update:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.chkOkayToSave = False Then

Cancel = True ' cancel the update

Select Case MsgBox( _
"Do you want to save the changes you made to this record?",
_
vbQuestion+vbYesNoCancel+vbDefaultButton2, _
"Record Has Been Modified")

Case vbYes
' Do nothing

Case vbNo
Cancel = True ' Cancel the update
Me.Undo ' Undo the form

Case vbCancel
Cancel = True ' Cancel the update
' Leave the form with the user's edits

End Select

End If

' If the record is to be saved, you may still want to
' validate the record and cancel the save.

If Cancel = False Then

' ... code to validate the record; set Cancel = True
' if the record shouldnt be saved ...

End If

End Sub
 
omsoft said:
I am new to Access VBA. I have a table and a form bound to the fields in
that table.
When I open the form (click event on a button), the date entered on the form
is added to the table. At times just a blank record is added. I wan to make
sure that the record is only added when Submit button is pressesd. Otherwise,
the record is backed out.
Click to expand...


A record is added because either the user entered some data
in the new record OR because you have some code that sets
the value of a bound control. Once that happens, navigating
to a different record, moving the focus to a subform or
closing the form will automatically save the dirtied new
record. If the user does not want that data to be saved,
then the the Esc key can be used to undo the changes to the
record (or you can use the form's Undo method in a macro or
a code procedure).

How did the date get set?
 
All controls in the form are bound ot fields in the table, so when the user
starts entering data, it is saved to table automatically.
I added me.undo on cancel (button click) as well as on validation error
That should do it, right?

It would be OK to save in all other cases.
 
Personally I think a button just adds clutter to your form,
but, yes, it should work. Don't forget that users can(?) be
trained to hit the Esc key once to undo a change to the
current control or twice to undo all the changes to the
current record. Once the incomplete record is saved (via
navigation, etc), then the only thing you can do is delete
the record.
 
Back
Top