AfterUpdate code does not seem to run in all cases

[pltmcs]

I have a form that keeps track of keys given to employees. We decided to add a feature to keep a digital signature for the keys so I created a second table to hold the signature and enough information to tie the signature to the key(s) issued. When the information is added to the form, the user clicks on a command button to save and exit the form;

Private Sub Command71_Click()
    On Error GoTo Command71_Error
    If Not IsNumeric(Me!DOOR) Then
      MsgBox "You must enter a numeric value for 'Door'"
      Me!ROOM.SetFocus
      Me!DOOR.SetFocus
    Else
       strGEmpNum = ""
       DoCmd.Close acForm, "KEYSADD", acSaveYes
    End If
 
   Exit Sub
 
Command71_Error:
    MsgBox Err.Description
 
End Sub

I added code in the AfterUpdate exit point to add a record to the signature file as a holder record for signature. I am finding that holder record is not always created/saved.

I added code to catch an error on the write, but the users say they have not seen the error produced. In as much as you can not always trust the users, I am entering this forum topic to see if there is a flaw in my processing. I don't see why the process works 95% of the time, yet in some cases the AfterUpdate code does not seem to fire. A common scenerio is that the user will enter a record, hit save, immediately go back into the form to enter a new key, repeat. Could this cause an issue?

Private Sub Form_AfterUpdate()
 
On Error GoTo InsertErr
 
Dim EN As String
Dim KN As String
Dim CD As String
Dim ID As String
Dim FN As String
Dim DP As String
Dim LN As String
Dim BD As String
Dim RM As String
Dim bSecondkey As Boolean
Dim iCounter As Integer
Dim lPause As Long
 
iCounter = 0
 
 
Dim stLinkCriteria As String
Dim strRunSQL As String
 
EN = Me.EMPLOYEEIDnew.Value
KN = Me.KEYNUM.Value
CD = Me.CODE.Value
ID = Me.ISSUEDATE
ID2 = Me.ISSUEDATE2
FN = Me.FIRSTNAME
DP = Me.DEPARTMENT
LN = Me.LASTNAME
BD = Me.BUILDING
RM = Me.ROOM
 
bSecondkey = False
 
If Len(ID2) > 0 Then
   bSecondkey = True
End If
 
retry_write:
 
strRunSQL = "INSERT INTO [ISSUEDKEYS-ACCESS] (EMPLOYEENUMBER, KEYNUM, ISSUEDATE, LASTNAME, FIRSTNAME, DEPARTMENT, CODE, BUILDING, ROOM, REISSUEDKEY, SECONDCOPY )" & _
 " VALUES (" & "'" & EN & "', '" & KN & "', '" & ID & "', '" & LN & "', '" & FN & "', '" & DP & "', '" & CD & "', '" & BD & "', '" & RM & "', " & False & ", " & bSecondkey & ")"
 
DoCmd.RunSQL strRunSQL
 
Exit_ER:
Exit Sub
 
 
InsertErr:
' on error code added 8/8/07
' the following code is to do a retry of  the insert if there is an error
If iCounter < 1 Then
   iCounter = iCounter + 1
   lPause = 5
   Pause (lPause)
   Resume retry_write:
End If
 
' the following code generates an error message if the insert fails
MsgBox Err.Description & Err.Number
Resume Exit_ER
 
 
End Sub

[MGrowneyARSI]

They may be overwriting the old record you could try locking down the form on open and on click of save and on exit create a sub that locks all the fields or me.Field_Name.locked call it LockAll or something likewise and call it on open on close and on click of save then create a sub that does the exact opposite you will want to create an add new record button and call the unlock on click of this one. This way you are forcing the user to only be able to create a new record it will also keep them from being able to edit records they have entered in the past last of all take away the record navigation buttons and set the cycle property to current record on the form. This may or may not help but it sounds like that may be what is going on.

[pltmcs]

Thank you for your response and suggestions. I understood part of what you wanted, but a big problem is that I need to keep it simple for the users and changing the process, even just adding a button or two, will get major push back.

I checked and the cycle property is set to current, but my record locks property is set to "No Locks". Should I change it?

As for adding the locking code. Can I change the Close (save) to two DoCmds and do the lock before the save and unlock after the save but before the close? The particular form is set to not allow edits, only adds, so I don't have to guard against updates.

[MGrowneyARSI]

Are you seting your fields = variables and then saving the variables to the table I seen this done before it's a real pain and could be why your haveing issues

[JKing]

Are you seting your fields = variables and then saving the variables to the table I seen this done before it's a real pain and could be why your haveing issues

It definitely looks that way. The after update event occurs after the values on the form have been committed to the database. In this event you are doing a programmatic insert of the same data.

[pltmcs]

It definitely looks that way. The after update event occurs after the values on the form have been committed to the database. In this event you are doing a programmatic insert of the same data.

Okay, since the update sometimes works and sometimes does not, does that mean there is a timing issue where I was just getting lucky that the form still contained the data for the AfterUpdate code to use to insert into the other table?

As an attempted fix, I move the code out of the AfterUpdate and after the save of the form, but before the close. This seems to have it's own set of problems where I am getting a "Save Action is Canceled" message when the form save is being done. Should I open a new topic for this issue?

[MGrowneyARSI]

Where was your code in the first place after update of what? was it after update of a required field and why did you not just build the form strait from a query instead of setting this and saving that I say keep it simple stop with the setting variables and just build from a query I had to rebuild an entire database I was modifying because it was all written like this code above.

[JKing]

Hi, I think I've misinterpretted your problem. I originally thought you were inserting the same data from the form into the exact same table creating duplicate entries. I see now you have an additional table that you are keeping a copy of the data in.

Have you thought about using an append query to transfer the data over?