By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Public function to cancel further execution of sub

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function ValidateField(ctl As Control, ctlLabel As String)
  2. 'Validate data entry: check for empty fields in required field
  3.  
  4.     Dim msg, nl, strID As String
  5.     nl = vbNewLine
  6.     msg = "Please enter valid data into field:" & nl
  7.  
  8. 'Check field for null value and display message
  9.     If IsNull(ctl) Or Trim(ctl & "") = "" Then
  10.         MsgBox msg & ctlLabel, vbOKOnly, "Missing Data"
  11.         ctl.SetFocus
  12.         ctl = Nothing
  13.         Exit Function      '<<== THIS IS THE PROBLEM!!!
  14.     End If
  15.  End Function
  16.  

On the form I have a "Save" button with the Click-command:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2.  
  3.     Call ValidateField(Me!Field1, "Company name")
  4.     Call ValidateField(Me!Field2, "Family name")
  5.   'etc. for further fields
  6.  
  7.  'Only if all validation is OK, further commands should be executed:
  8.  
  9.       .....       'Whatever code here
  10.       DoCmd.Close
  11.  
  12. End Sub
  13.  
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)
Aug 14 '08 #1
Share this Question
Share on Google+
7 Replies


100+
P: 167
Hi Blu,

I redesigned your function and on click code and it works. Try this:
Expand|Select|Wrap|Line Numbers
  1. Public Function ValidateField(ctl As Control, ctlLabel As String)
  2. 'Validate data entry: check for empty fields in required field
  3.  
  4.     Dim msg, nl, strID As String
  5.     nl = vbNewLine
  6.     msg = "Please enter valid data into field:" & nl
  7.  
  8. 'Check field for null value and display message
  9.     If IsNull(ctl) Or Trim(ctl & "") = "" Then
  10.         MsgBox msg & ctlLabel, vbOKOnly, "Missing Data"
  11.         ctl.SetFocus
  12.         ValidateField = "Bad"
  13.     End If
  14.        Set ctl = Nothing
  15.  End Function
And the OnClick code is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command11_Click()
  2.     If ValidateField(Me!eFName, "First name") = "Bad" Then Exit Sub
  3.     If ValidateField(Me!eLName, "Family name") = "Bad" Then Exit Sub
  4.  
  5. End Sub
It still uses IF statements, but it's not that bad I guess.
Aug 14 '08 #2

Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. Public Function ValidateField(ByRef ctl As Control, ctlLabel As String)
  2.    ...
  3.    the rest of your code
  4. End Function
  5.  
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()
Expand|Select|Wrap|Line Numbers
  1. Call ValidateField(Me!Field1,"string irrelevant for this example")
  2.  
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
Aug 14 '08 #3

P: 3
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
Aug 14 '08 #4

Delerna
Expert 100+
P: 1,134
Oh yes
and good sensible names for all of your controls makes a lot of sense
Aug 14 '08 #5

P: 3
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
Expand|Select|Wrap|Line Numbers
  1. Public Function ValidateField(ByRef ctl As Control, ctlLabel As String)
  2.    ...
  3.    the rest of your code
  4. End Function
  5.  
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()
Expand|Select|Wrap|Line Numbers
  1. Call ValidateField(Me!Field1,"string irrelevant for this example")
  2.  
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]
Aug 14 '08 #6

Delerna
Expert 100+
P: 1,134
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.
Aug 14 '08 #7

nico5038
Expert 2.5K+
P: 3,072
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSave_Click()
  2. Dim txtMessage As String
  3. On Error GoTo Err_btnSave_Click
  4.     ' init error message 
  5.     txtMessage = ""
  6.     ' Check fields in reverse order to set focus to the first
  7.     If Not Len(NZ(Me.Description)) > 0 Then
  8.         txtMessage = "Description empty ?" & vbCrLf
  9.         Me.Description.backcolor = vbRed
  10.         Me.Description.SetFocus
  11.     else
  12.         Me.Description.backcolor = vbWhite
  13.     End If
  14.     If Not Len(NZ(Me.Severity)) > 0 Then
  15.         txtMessage = "No Severity?" & vbCrLf & txtMessage
  16.         Me.Severity.backcolor = vbWhite
  17.         Me.Severity.SetFocus
  18.     else
  19.         Me.Severity.backcolor = vbWhite
  20.     End If
  21.     If Not Len(NZ(Me.Type)) = 0 Then
  22.         txtMessage = "Recordtype empty ?" & vbCrLf & txtMessage
  23.         Me.Typy.backcolor = vbWhite
  24.         Me.Type.SetFocus
  25.     else
  26.         Me.Typy.backcolor = vbWhite
  27.     End If
  28.     ' Check error found
  29.     If Len(txtMessage) > 0 Then
  30.         MsgBox txtMessage
  31.         Exit Sub
  32.     End If
  33.  
  34.     DoCmd.Close
  35.  
  36. Exit_btnSave_Click:
  37.     Exit Sub
  38.  
  39. Err_btnSave_Click:
  40.     MsgBox Err.Description
  41.     Resume Exit_btnSave_Click
  42.  
  43. End Sub
  44.  
Idea ?

Nic;o)
Aug 14 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.