BeforeUpdate event running but record not being saved

[gbyerly]

I have a form displayed in datasheet view where the user can modify all but a few fields. In the form's BeforeUpdate event, i have the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctrl As Control
 
    If Me.Dirty Then
        For Each ctrl In Me.Controls
            With ctrl
                If .ControlType = acTextBox Then
                    Debug.Print .Name
                    If .Value <> .OldValue Then
                        .SetFocus
                        Debug.Print .Name, .OldValue, .Text
                    End If
                End If
            End With
        Next
    End If
    'Cancel = False
    'DoCmd.RunCommand acCmdSaveRecord
'    DoCmd.Save
End Sub
 

This is a test to see if I can later save the changed values to another table in the same database, When as a user, I modify the data in several fields and then click the arrow navigation button to move to the next or previous record in the datasheet, the code above runs successfully displaying the field names whose values have changed and the changes as well. However, the record remains in edit mode and does not move to the next or previous record as I expected based on the fact I clicked the Next or Previous navigation button, If I press the button again, it saves the record and moves to the Next or Previous record as usual. As you can see in the code, I tried several ideas to force the save and move, but none produced the expected result.

Anyone have any idea why?

[NeoPa]

Hi, and welcome to Bytes.com.

Let's start by saying I'm not absolutely clear on what is causing this behaviour, but I have my suspicions ;-) I'll point out what I see and hopefully that will lead you to :

1. Something that works.
2. Something that makes good logical sense.

1. Line #4 is superfluous. The event procedure can only possibly be triggered (and thus this code run) when there are changes to be saved (IE. Me.Dirty = True).
2. I would not expect setting the focus to a Control on the current record to cancel the update but it would certainly cancel the movement you requested to another record.
   
   Maybe it does have the side-effect of cancelling the update too. Try without that element to see if it works.
3. Lines #17 will neither help nor hurt. Cancel is always set to False when (before) this procedure is entered, and the rest of your code does nothing to change this.
   
   It seems like a good idea to try it, but I'm not too surprised it didn't help. That said, had you not already tried it I would have suggested it ;-)
4. Lines #18 to #19 should never be seen within the Form_BeforeUpdate() procedure. They will always cause problems as you are already within the saving process if you're in this code.
5. For clarity of code, and you've done a pretty good job on that generally, I always recommend (for line #15) that you include the variable name with the Next (IE Next ctrl).
6. Minor quibble as most people get confused by this - TextBoxes are not Fields. They are Controls that are sometimes (only if bound) associated with Fields.

Let us know how you get on after reading this :-)