Missing one line (I think) (1 Viewer)

[Alc]

I have a form on which all fields are mandatory. I've written a function to check for missing data when the user attempts to move to another record and to inform the user of the missing data. This runs from the form's BeforeUpdate event and does correctly identify blank fields as well as preventing any changes made from saving.

What I can't work out is how to prevent the user from moving to a different record until the current one is complete or cancelled. Ideally, I want them to stay on the same record until either they've opted NOT to save the changes or have completed all blank fields.

I can't help thinking the answer is simple, but having spent a day playing with Undo, Cancel, etc. I'm not getting results I want.

Any suggestions gratefully received.

Current code is as follows

 Private Sub btnNext_Click()
    DoCmd.GoToRecord , , acNext
End Sub
  
 Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Dirty_Check = True Then
        DoCmd.CancelEvent
    End If
End Sub
  
 Function Dirty_Check() As Boolean
    Dim strMsg As String
    Dim strTitle As String
    Dim strResponse As String
    Dim lbDirty As Boolean
    Dim ctl As Control
     lbDirty = False
    If Me.Dirty Then
        strTitle = "Data has changed"
        strMsg = "Do you wish to save the changes?"
        strResponse = MsgBox(strMsg, vbYesNo, strTitle)
        
        If strResponse = vbYes Then
            For Each ctl In Me.Controls
                If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
                    If Nz(ctl, "") = "" Then
                        strMsg = "The " & ctl.Name & " field needs to be completed before the record can be saved"
                        strTitle = "Missing Data"
                        strResponse = MsgBox(strMsg, vbExclamation, strTitle)
                        GoTo Exit_Point
                    End If
                End If
            Next ctl
            Me.Dirty = False
        Else
            Me.Undo
        End If
    End If
    
Exit_Point:
    Set ctl = Nothing
    Dirty_Check = lbDirty
End Function

 

[Frothingslosh]

Instead of DoCmd.CancelEvent, try Cancel = True

 

[Alc]

Thanks for the suggestion.

Unfortunately, that change just meant that I got the same warnings as required, but the record DID save before going to the next one.

 

[Frothingslosh]

Huh, weird. That's how I always cancel moving to the next record.

I think I see the issue - if they say no, you have nothing set up to return a TRUE result from Dirty_Check. If they say no, you undo the changes, but you never change the value of Dirty_Check from its default FALSE value. That means the record change is never cancelled.

 

[Alc]

Thankyou! TI knew it was something small, but that was driving me mad.

In case it helps anyone, the following code works as desired:
If the record has been changed, the user is prompted to complete all fields on the form. Until they do so, they only have the option of leaving the record without saving their changes.

 Private Sub btnNext_Click()
    On Error GoTo Exit_Sub
    
    DoCmd.GoToRecord , , acNext
    
Exit_Sub:
End Sub
  
 Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Dirty_Check = True Then
        DoCmd.CancelEvent
    End If
End Sub
  
 Function Dirty_Check() As Boolean
    Dim strMsg As String
    Dim strTitle As String
    Dim strResponse As String
    Dim lbDirty As Boolean
    Dim ctl As Control
     lbDirty = False
    If Me.Dirty Then
        strTitle = "Data has changed"
        strMsg = "Do you wish to save the changes?"
        strResponse = MsgBox(strMsg, vbYesNo, strTitle)
        
        If strResponse = vbYes Then
            For Each ctl In Me.Controls
                If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
                    If Nz(ctl, "") = "" Then
                        lbDirty = True
                        strMsg = "The " & ctl.Name & " field needs to be completed before the record can be saved"
                        strTitle = "Missing Data"
                        strResponse = MsgBox(strMsg, vbExclamation, strTitle)
                        GoTo Exit_Point
                    End If
                End If
            Next ctl
            Me.Dirty = False
        Else
            Me.Undo
        End If
    End If
    
Exit_Point:
    Set ctl = Nothing
    Dirty_Check = lbDirty
End Function