BeforeUpdate event issue

[hermanko]

Hi,

I have a form that is used for data entry into my underlying table. I
have AllowAdditions turned ON as well as Yes to Data Entry in the form
properties. I am using the BeforeUpdate event so that the user can
click OK or Cancel to save or undo any changes made on a new record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save entry?", vbOKCancel + vbDefaultButton1) <> vbOK
Then
Cancel = True
Me.Undo
Me![File Code].SetFocus ' resets cursor to first field on form
called [File Code]
End If
End Sub

The issue I have is that I also want a command button for Save and
Cancel, so that the user doesn't need to TAB or ENTER all the way down
to the last record before triggering the BeforeUpdate event (as some
fields may not require info so the user can just click Save or Cancel
"prematurely").... but as I have it now, as soon as I click on, say, my
Cancel button, it'll trigger the above event as focus has left the new
record. Is there a way to bypass that if I just want to Cancel and
immediately close that form? Conversely, is there a way to bypass that
event so that I can just Save and immediately close the form? 'Cuz it
just doesn't make sense to have user click Cancel and then still have
the msgbox ask Save Entry?...if they click OK then effectively they
have saved the record by clicking Cancel!

I hope my problem is stated clear and if anyone can help me out that
would be greatly appreciated.

Herman

 

[Allen Browne]

The Form_BeforeUpdate code should not trigger just because you clicked a
command button.

Perhaps you are seeing an engine-level error on the field you are entering.
For example, if you are in a text box bound to a date field, and you have
entered:
6/17/
the entry is not a valid date yet, so if you click the Cancel button you
will get a message that won't let you out of the control.

There is no way to get Access to move to the command button without
triggering this error. You can trap the error in Form_Error, but it's not a
good solution.

To work around the problem, you need a solution that is not on the form:
a) Use <Esc> to undo the entry, or
b) Use the Undo button on the toolbar, or
c) Use Undo on the Edit menu.

Similarly Shift+Enter saves the record (except in a multi-line text box.)
You can customize the toolbar by adding a Save Record button also.

 

[hermanko]

Hi,

All of my fields are text fields. I am having a lot of problems with
what I want to do:

I would like to have a form open that performs the function of allowing
the user to add ONE (and only one) record at a time to an existing
table.

Criteria:
-the primary key field must not be null
-the primary key field must not be duplicating an existing record
-once a record's info is entered by user, they have an option to
save/cancel (cmd buttons)
-cannot have ability to tab to another new record (force close form
perhaps)
-only allowed to add records (AllowAdditions is YES only)

Right now I have code for the following:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save entry?", vbOKCancel + vbDefaultButton1) = vbCancel
Then
Cancel = True
Me.Undo
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "Add Document"
DoCmd.OpenForm "Admin Menu"
End Sub


Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
Response = acDataErrContinue ' Don't display the default message
MsgBox "You have entered a File Code that already exists in the
database. Enter a unique File Code.", vbCritical, "Duplicate File Code"
Me![File Code].SetFocus
Else
If DataErr = 3058 Then
Response = acDataErrContinue ' Don't display the default
message
MsgBox "The document File Code cannot be null.", vbCritical,
"Null File Code"
Me![File Code].SetFocus
Else
MsgBox "Error#: " & DataErr
Response = acDataErrDisplay ' Display Default message
End If
End If
End Sub


I do not have any cmd buttons yet because I can't get it properly
working with the above code. I keep getting errors (runtime and jet
engine) and I don't really know what is happening. Maybe I need a
different approach? Can anyone provide suggestions or recommendations
to how to do this?

Access newbie,
Herman

 

[Allen Browne]

I think you are making this much harder than you need to.

Looking at your 5 criteria:
1. The primary key cannot be null. No code needed.
2. The primary key cannot be a duplicate. No code needed.
3. Save and Cancel buttons are optional.
4. If the user is allowed to enter 1 record only, close the form in its
AfterInsert event.
5. If the form is only for adding new records, set its Data Entry property
to Yes.

So, you set the form's properties like this:
Data Entry Yes
Allow Additions Yes
After Insert [Event Procedure]
Then click the Build button (...) beside the After Insert property.
Access opens the code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
DoCmd.Close acForm, Me.Name

The form opens to a new record, and closes as soon as the user enters 1
record. And there's just one line of code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

All of my fields are text fields. I am having a lot of problems with
what I want to do:

I would like to have a form open that performs the function of allowing
the user to add ONE (and only one) record at a time to an existing
table.

Criteria:
-the primary key field must not be null
-the primary key field must not be duplicating an existing record
-once a record's info is entered by user, they have an option to
save/cancel (cmd buttons)
-cannot have ability to tab to another new record (force close form
perhaps)
-only allowed to add records (AllowAdditions is YES only)

Right now I have code for the following:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save entry?", vbOKCancel + vbDefaultButton1) = vbCancel
Then
Cancel = True
Me.Undo
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "Add Document"
DoCmd.OpenForm "Admin Menu"
End Sub


Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
Response = acDataErrContinue ' Don't display the default message
MsgBox "You have entered a File Code that already exists in the
database. Enter a unique File Code.", vbCritical, "Duplicate File Code"
Me![File Code].SetFocus
Else
If DataErr = 3058 Then
Response = acDataErrContinue ' Don't display the default
message
MsgBox "The document File Code cannot be null.", vbCritical,
"Null File Code"
Me![File Code].SetFocus
Else
MsgBox "Error#: " & DataErr
Response = acDataErrDisplay ' Display Default message
End If
End If
End Sub


I do not have any cmd buttons yet because I can't get it properly
working with the above code. I keep getting errors (runtime and jet
engine) and I don't really know what is happening. Maybe I need a
different approach? Can anyone provide suggestions or recommendations
to how to do this?

Access newbie,
Herman

 

[hermanko]

Thanks Allen!

That AfterInsert sure did the trick I was never even aware of this
event until now. Thanks again.
Herman

 

[hermanko]

Allen,

what if i want to include a button to Cancel the adding of a record in
mid-process....cuz if the user is in the middle of entering info for a
new record but decides against it, they should be able to do so with a
button. I've tried to add a button that closes the form but it gives me
an error "cannot close form". I currently have the following code
(excluding the error event codes)...

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save new record and exit form?", vbOKCancel +
vbDefaultButton1) = vbCancel Then
Cancel = True
Me.Undo
Me![File Code].SetFocus
End If
End Sub
-----------------------------------------------------------------------------------
Private Sub Form_AfterInsert()
DoCmd.Close acForm, "Add Document"
DoCmd.OpenForm "Admin Menu"
End Sub
-----------------------(the code below produces an error)-----------
Private Sub cmd_noadd_Click()
DoCmd.Close acForm, "Add Document"
DoCmd.OpenForm "Admin Menu"
End Sub

Any help with this would be truly appreciated.
Herman

 

[Allen Browne]

The cancel should work if you undo the form first:

If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name

 

[hermanko]

Thanks!
That makes sense
Herman