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(M e!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
[code=vba]
Public Function ValidateField(B yRef ctl As Control, ctlLabel As String)
...
the rest of your code
End Function
[/code]

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()
[code=vba]
Call ValidateField(M e!Field1,"strin g irrelevant for this example")
[/code]
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
[code=vba]
Public Function ValidateField(B yRef ctl As Control, ctlLabel As String)
...
the rest of your code
End Function
[/code]

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()
[code=vba]
Call ValidateField(M e!Field1,"strin g irrelevant for this example")
[/code]
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:

[code=vb]
Private Sub btnSave_Click()
Dim txtMessage As String
On Error GoTo Err_btnSave_Cli ck
' init error message
txtMessage = ""
' Check fields in reverse order to set focus to the first
If Not Len(NZ(Me.Descr iption)) > 0 Then
txtMessage = "Descriptio n empty ?" & vbCrLf
Me.Description. backcolor = vbRed
Me.Description. SetFocus
else
Me.Description. backcolor = vbWhite
End If
If Not Len(NZ(Me.Sever ity)) > 0 Then
txtMessage = "No Severity?" & vbCrLf & txtMessage
Me.Severity.bac kcolor = vbWhite
Me.Severity.Set Focus
else
Me.Severity.bac kcolor = vbWhite
End If
If Not Len(NZ(Me.Type) ) = 0 Then
txtMessage = "Recordtype empty ?" & vbCrLf & txtMessage
Me.Typy.backcol or = vbWhite
Me.Type.SetFocu s
else
Me.Typy.backcol or = vbWhite
End If
' Check error found
If Len(txtMessage) > 0 Then
MsgBox txtMessage
Exit Sub
End If

DoCmd.Close

Exit_btnSave_Cl ick:
Exit Sub

Err_btnSave_Cli ck:
MsgBox Err.Description
Resume Exit_btnSave_Cl ick

End Sub
[/code]

Idea ?

Nic;o)