Save Data so Escape key doesn't work

[MintChipMadness]

I have 3 fields (locked and not enabled) that are autofilled after insert. The problem is I can't figure out how to lock those fields down so a user can't use the escape key to blank the autofilled fields out right after insert. I tried adding the last line to the afterinsert but I get an error. The error and code are below. The line that is causing the issue is in red. Any help would be greatly appreciated. Thank you.

Private Sub Form_AfterInsert()

Me.[OldBox#] = DMax("Val([OldBox#])", "[DataEntry]") + 1
Me.[EnteredBy] = Environ("UserName")
Me.[EntryDate] = date
RunCommand acCmdSaveRecord
End Sub

Run-time error '2115'
The macro or function set to the BeforeUpdate or validation rule property for this field is preventing Microsoft Access from saving the data in the field

Extra Information:
BeforeUpdate is not used in my form.

[June7]

Review http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

I have never used the form Insert events so just speculation but maybe use BeforeInsert to set those fields then AfterInsert to Requery or maybe just Refresh.

[MintChipMadness]

When I changed the code to BeforeInsert it didn't allow the current events to start. I tried all types of saving in 'afterinsert'. I have to click on my save button (in the same form) to make those fields stick. The save button has the 'RunCommand acCmdSaveRecord' code but for some reason after insert it is in some place where no commands work. Since I know that clicking on my save button works, how can I make that button execute in VBA?

[June7]

If code in same form as the button, try:

Call yourButtonName_Click

If from another form:

Call Forms("form name").yourButtonName_Click

I use both syntax but, as stated not using Insert events, so don't know if will work for you.

[MintChipMadness]

It didn't work. I got the same error. It is weird that I get stuck afterinsert. Maybe access wants to give the user one last chance to exit so I can't force a save. The strange part is all I have to do it click on a control after the insert and click save button or just hit the save button to make it stay yet I can't automate that.

Edit - I have a little bit more information. When I finish filling out the form and click into the subform or go to the next record, the 3 fields populate. In either case, if I immediately hit escape, they get blanked out. I am completely lost on this issue.

[June7]

So the code populating the fields works? But not forcing a record save? Will any other code run in the AfterInsert? Test a MsgBox or Debug.Print. What about form Refresh or Requery or SetFocus to subform? Will that run and also commit the edits?

Maybe form KeyPress event code to capture the ESC key press? Unfortunately, the event would fire with every keystroke. I have used a textbox KeyPress event to capture Enter key press.

[MintChipMadness]

"Me.DOC.Setfocus" (DOC = subform) does not work. I get an error saying access cannot set focus. Me.Requery and/or Me.Refresh gives the error in my first post. The only thing that worked is a message box but that didn't fix the problem. How would I capture the key press. Like if they hit escape, it would give the user a message box that would either have them delete the record or cancel the key press.

[MintChipMadness]

I found a website with a similar problem to mine. One of the comments has an alternate solution using code but I don't understand if the code will work for me.

http://database.itags.org/ms-access-database/96766/

[MintChipMadness]

I think there is a workaround for this. I could do an after update event. If all three values are null, it would fill them in. How should I write the code for it? I tried doing the if statement below but it doesn't add the information to the record for some reason and I don't get an error.

Private Sub Form_AfterUpdate()
If IsNull(Me.OldBox#) And IsNull(Me.EnteredBy) And IsNull(Me.EntryDate) Then
    Me.[OldBox#] = DMax("Val([OldBox#])", "[DataEntry]") + 1
    Me.[EnteredBy] = Environ("UserName")
    Me.[EntryDate] = date
End If
End Sub

Edit - I got the code to work with the code below. I still have some problems with the escape key. I'll have to figure out where to put it. I put it in AfterUpdate and Current. They both work but when I hit escape it still removes the information.

If IsNull([OldBox#] & [EnteredBy] & [EntryDate]) Then
    Me.[OldBox#] = DMax("Val([OldBox#])", "[DataEntry]") + 1
    Me.[EnteredBy] = Environ("UserName")
    Me.[EntryDate] = date
End If

[June7]

Did you try BeforeUpdate?

What exactly is triggering the Update event? My understanding is it would need to be form close, move to new record, or SaveRecord.

Google: Access disable ESC key

Everything I read involves trapping keystroke (KeyDown, KeyUp, KeyPress).

[MintChipMadness]

I can't use BeforeUpdate because it conflicts with my current event for some reason. The KeyPress seems to be the only option. I got it working without an if statement but I don't want it to execute unless the fields are null. I listed the key press code below and what I want it to check below that. I can't figure out a way to combine the two so that when the escape key is pressed, it checks if all 3 fields are no and if so, it fills in the information (or cancels key press. preferred method). If they are not null, I would like it to continue with the keypress.

Private Sub Form_KeyPress(KeyAscii As Integer) If KeyAscii = vbKeyEscape Then

If IsNull([OldBox#] & [EnteredBy] & [EntryDate])
    Me.[OldBox#] = DMax("Val([OldBox#])", "[DataEntry]") + 1
    Me.[EnteredBy] = Environ("UserName")
    Me.[EntryDate] = date
End If

[June7]

Check out this article http://www.techonthenet.com/access/f...isable_esc.php

If that works then maybe just need to populate the 3 fields in Current event or when record is commited.

[MintChipMadness]

I got the nested if statement to work in the key press. It looks like everything is fixed now. Thank you for all your help.

[June7]

Would you like to share your final code?