Form Field Validation - "Cancel = True" problem

[CutAndPaste]

I'm calling this code on my Form's BeforeUpdate Event which I understand should only run if the form is dirty. In this example, the code should check to see if 4 fields ("txtField1", "txtField2", txtField3", "txtField4") have data present, if not highlight them yellow.

Function Msg()
Dim ctl As Control
Dim strFields As String
Dim strControl As String
Dim intCounter As Integer
Dim blnEmpty As Boolean

    For Each ctl In Me.Controls
        blnEmpty = False
        If ctl.ControlType = acComboBox Or acTextBox Then
            If ctl.Visible And Len(ctl.Tag) > 0 Then
                Select Case ctl.Tag
                    Case "txtField1", "txtField2", txtField3", "txtField4"
                        If IsNull(ctl.Value) Then
                            blnEmpty = True
                            ctl.BackColor = vbYellow
                        Else
                            ctl.BackColor = vbWhite
                        End If
                    Case Else
                        If ctl.Value = 0 Then
                            blnEmpty = True
                            ctl.BackColor = vbYellow
                        Else
                            ctl.BackColor = vbWhite
                        End If
                End Select
                If blnEmpty Then _
                    strFields = strFields & ctl.Tag & vbCrLf
                If blnEmpty Then _
                    If Len(strControl) = 0 Then _
                        strControl = ctl.Name
            End If
        End If
    Next
    
    If Len(strFields) > 0 Then
        Cancel = True
        MsgBox "You have not completed all data fields, " & _
               "please enter data in the following fields:" & vbCrLf & strFields, _
               vbExclamation, Me.Caption
        Me(strControl).SetFocus
        Me(strControl).BackColor = vbWhite
        Exit Function
    End If
End Function

However, it keeps falling over at:

Cancel = True

saying "Compile Error: Variable Not Defined"

I want to be able to run this code from a "Close Form button" to ensure that either the record is not created unless the fields listed are filled in or the form closure is cancelled so that the user can complete the missing data.

I've seen varients of this code on a couple of Access forums but can't find a solution for the "Cancel = True" issue.

Am I doing something wrong?

 

[Uncle Gizmo]

The reason you are getting the error is that you need to declare the variable "cancel" at the beginning of your function.

However the real reason you are having problems is that you have copied this code out of the forms close event (I guess), so in that event I would imagine the "cancel = true" was necessary.

You could try moving the code back into the routine it originally came from and see if you can get it working there.

Although it is a good idea to store your code in modules, sometimes some of the code will only work when called from the particular event it was designed to work with. That appears to be the problem in this case.

 

[Pat Hartman]

"Cancel" is an argument of the BeforeUpdate event. To set its value, you must be within the BeforeUpdate event itself. From any place else you will have a scope issue. I don't see that your function has a return value. You could use that to set the Cancel argument.

Public Function Msg() as Boolean
.....
End Fucntion

In the function set Msg to True if you want to cancel the update and false if you do not. Then in the BeforeUpdate event, you could call the Msg() function this way:

Cancel = Msg()