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.
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
