Public function to cancel further execution of sub

[blublu123]

Hi all,

I have a number of fields on various forms that need data validation. If fields are empty (null), the form should not save and close, and the cursor should go to the appropriate field.
I don't want to re-write for every field on every form the same code again, so I created a public function (as standard module) doing the validation check:

Public Function ValidateField(ctl As Control, ctlLabel As String)
'Validate data entry: check for empty fields in required field
 
    Dim msg, nl, strID As String
    nl = vbNewLine
    msg = "Please enter valid data into field:" & nl
 
'Check field for null value and display message
    If IsNull(ctl) Or Trim(ctl & "") = "" Then
        MsgBox msg & ctlLabel, vbOKOnly, "Missing Data"
        ctl.SetFocus
        ctl = Nothing
        Exit Function      '<<== THIS IS THE PROBLEM!!!
    End If
 End Function
 

On the form I have a "Save" button with the Click-command:

Private Sub cmdSave_Click()
 
    Call ValidateField(Me!Field1, "Company name")
    Call ValidateField(Me!Field2, "Family name")
  'etc. for further fields
 
 'Only if all validation is OK, further commands should be executed:
 
      .....       'Whatever code here
      DoCmd.Close
 
End Sub
 

My problem now is that I need the "Exit Sub" to fire from within the public function, i.e. the Save & Close should be canceled if one of the validations fails.
In the public function the "Exit Sub" is not allowed. Is there a way to tell the function also to terminate the "Mother" Sub (in our case: the cmdSave_Click)???
Please also bear in mind that the public function will be executed from different forms, buttons (and thus subs), so I cannot hard-code something like "cancel the closing of form XY" into the function.

Of course I could write for each validation something like
If IsNull(Field1) then
Call ValidateField(Me!Field1, "Company name")
Else
Exit sub
End If

but this is exactly what I want to avoid! I do not want to have to write if...then statements for every single field I need to check, but only the one public function that does that for me!

Thanks for any help solving this problem!
blublu (Running Access 2003, VBA 6.5)

[hjozinovic]

Hi Blu,

I redesigned your function and on click code and it works. Try this:

Public Function ValidateField(ctl As Control, ctlLabel As String)
'Validate data entry: check for empty fields in required field
 
    Dim msg, nl, strID As String
    nl = vbNewLine
    msg = "Please enter valid data into field:" & nl
 
'Check field for null value and display message
    If IsNull(ctl) Or Trim(ctl & "") = "" Then
        MsgBox msg & ctlLabel, vbOKOnly, "Missing Data"
        ctl.SetFocus
        ValidateField = "Bad"
    End If
       Set ctl = Nothing
 End Function

And the OnClick code is:

Private Sub Command11_Click()
    If ValidateField(Me!eFName, "First name") = "Bad" Then Exit Sub
    If ValidateField(Me!eLName, "Family name") = "Bad" Then Exit Sub
 
End Sub

It still uses IF statements, but it's not that bad I guess.

[Delerna]

Ditto to hjozinovic's post.

PS
Good job using a function and passing each control that you want to check as a parameter by the way. One hint I can pass on to you

Public Function ValidateField(ByRef ctl As Control, ctlLabel As String)
   ...
   the rest of your code
End Function
 

Passing the control by reference like that means that you are passing the actual control to your function instead of a copy of it.
For example with this line in the sub cmdSave_Click()

Call ValidateField(Me!Field1,"string irrelevant for this example")
 

Now, ctl is not a copy of Me!Field1 rather it is Me!Field1.

So, in Public Function ValidateField
you could say ctl.text="Wrong entry" and Me!Field1.text would become "Wrong entry"
or ctl.setfocus and Me!Field1.text would receive focus
Likewise for the second line in cmdSave_Click()
except now anything you do to ctl will effect Me!Field2

[blublu123]

Hi hjozinovic

Thanks for the modification. This should do the trick.

I think I can live with this use of if-then, even if this means that I end up with say 6 repetitions of if then statements if I have six field validations.

I am sure there are more elegant ways to solve it! But for the time being I am happy to use your solution. Thanks again.
Cheers

[Delerna]

Oh yes
and good sensible names for all of your controls makes a lot of sense

[blublu123]

Hi Delerna

thanks for the clarification. I did not know this difference between passing only a copy instead of the actual control itself. This can come handy if you want to manipulate the values in the background!

I am however confused concerning the actual example: why should actually want to pass the real control to the function and enter a value into a field that is empty? By doing so I enter a fake value ("wrong entry" in your example) into a field that should not be null, thus if my user clicks again the Save button I end up with a family name "Wrong entry". Not really what I want to achieve, no?

Thanks again for all your fast help!
Blu

PS
Good job using a function and passing each control that you want to check as a parameter by the way. One hint I can pass on to you

Public Function ValidateField(ByRef ctl As Control, ctlLabel As String)
   ...
   the rest of your code
End Function
 

Passing the control by reference like that means that you are passing the actual control to your function instead of a copy of it.
For example with this line in the sub cmdSave_Click()

Call ValidateField(Me!Field1,"string irrelevant for this example")
 

Now, ctl is not a copy of Me!Field1 rather it is Me!Field1.

So, in Public Function ValidateField
you could say ctl.text="Wrong entry" and Me!Field1.text would become "Wrong entry"
or ctl.setfocus and Me!Field1.text would receive focus
Likewise for the second line in cmdSave_Click()
except now anything you do to ctl will effect Me!Field2[/quote]

[Delerna]

Sorry, I was only illustrating what you could do, not what you should do in this instance

Actually, now i'm confused because I just checked and it works without passing by reference.

[nico5038]

Personally I check all fields when the user presses the [OK] button.
This will also allow checks between fields like a Startdate and Enddate being in the right sequence.

I test all fields and create a complete error message while marking the erroneous fields with a yellow background color. This way a user can correct all fields in one go. The code would look like:

Private Sub btnSave_Click()
Dim txtMessage As String
On Error GoTo Err_btnSave_Click
    ' init error message 
    txtMessage = ""
    ' Check fields in reverse order to set focus to the first
    If Not Len(NZ(Me.Description)) > 0 Then
        txtMessage = "Description empty ?" & vbCrLf
        Me.Description.backcolor = vbRed
        Me.Description.SetFocus
    else
        Me.Description.backcolor = vbWhite
    End If
    If Not Len(NZ(Me.Severity)) > 0 Then
        txtMessage = "No Severity?" & vbCrLf & txtMessage
        Me.Severity.backcolor = vbWhite
        Me.Severity.SetFocus
    else
        Me.Severity.backcolor = vbWhite
    End If
    If Not Len(NZ(Me.Type)) = 0 Then
        txtMessage = "Recordtype empty ?" & vbCrLf & txtMessage
        Me.Typy.backcolor = vbWhite
        Me.Type.SetFocus
    else
        Me.Typy.backcolor = vbWhite
    End If
    ' Check error found
    If Len(txtMessage) > 0 Then
        MsgBox txtMessage
        Exit Sub
    End If
 
    DoCmd.Close
 
Exit_btnSave_Click:
    Exit Sub
 
Err_btnSave_Click:
    MsgBox Err.Description
    Resume Exit_btnSave_Click
 
End Sub
 

Idea ?

Nic;o)