VBA Code Not Working (1 Viewer)

[Tupacmoche]

Hi VBA Masters,

I have the following code that I adopted from the web. It is cool because the loop checks any control whose tag is set to 'v'. It returns the name of the offending control and puts the focus on it so the user can fill it in. The issue that I have is the call to the function. After looping through any offending control the button that launches it simple does nothing when clicked. Can anyone see why?

'***CODE TO VALIDATE DONOR FORM REQUIRED FIELDS**********************************************************'
 '***This function checks Donor Name, Transmittal type, Payment Method, Gift Matched, and Pre-deposited **'
 If ValDonorFrm() = 0 Then
    Exit Sub
 Else
    Exit Sub
 End If
'********************************************************************************************************'

Public Function ValDonorFrm()

Dim bCheck As Boolean
Dim ctl As Control

For Each ctl In Me.Detail.Controls
    With ctl
            If .Tag = "v" Then
                If IsNull(.Value) Then
                    MsgBox ctl.Name & " is empty, this is a required field!", vbOKOnly, "Required Field"
                    ctl.SetFocus
                    Cancel = True
                    Exit Function
                End If
            End If
    End With
Next ctl

End Function

 

[pbaldy]

The function isn't declared as returning a value, and you never set a return value in it. Typically:

Public Function ValDonorFrm() AS Boolean

and then in the function

ValDonorFrm = True 'or False, as appropriate

 

[June7]

The first If Then block is not in event procedure. What you have should cause compile error.

The button Click event needs to be set to [Event Procedure]. Then click the ellipsis (…) to open VBA editor. Type or copy/paste code into the procedure.

The line Cancel = True accomplishes nothing. Should be what Paul suggested instead.

 

[Micron]

This seems to be a common topic lately. FWIW, I'd suggest a few things:
The With block isn't needed and checking only for Null isn't a guarantee. The Cancel = True is possibly meant for the BeforeUpdate event.

For Each ctl In Me.Controls
 If ctl.Tag = "v" AND NZ(ctl,"") = "" Then
   MsgBox ctl.Name & " is empty, this is a required field!", vbOKOnly,   "Required Field"
   ctl.SetFocus
   Exit Function
 End If
Next ctl

Last, validating one control at a time can be irritating to a user. Building a list of controls with missing data when a save is invoked might be better. The following also introduces the notion of using the label for a field rather than the control name, as some (e.g. txtDept) can be meaningless to the user. However, the label must be attached, otherwise such use will raise an error.

Dim strMsg As String

For Each ctl In Me.Controls
  If ctl.Tag = "v" AND NZ(ctl,"") = "" Then strMsg = strMsg & ctl.Controls(0).Caption & vbCrLf
Next ctl

If strMsg <> "" Then Msgbox "Please enter a value for " & vbCrLf & strMsg

 

[CJ_London]

or use the format property to display a short message in the control

for numbers #;;;"you must enter a value"

for text @;"you must enter a value"

or use conditional formatting

 

[Tupacmoche]

The code that calls the function is in the on Click event of a button that prints the information in the main form. But this code checks to see if all required field have been filed in. Normally, this code would be put into the After Update event of a form but since I have subforms that come before and after the fields being checked which would trigger a save before the user got to the fields I'm using a button to run the code.

It actually, works if a field is blank (IsNull() ) then the message returns the offending controls Name and set focus to it. It's only after the last offending control has been filled in that the print button on click does nothing. But, I will try what was suggested.

 

[pbaldy]

You would test the result of the function in your button code, and do whatever you want done if everything validates based on that result. Just calling the function won't do anything (as currently written anyway).

 

[Tupacmoche]

I appreciate your assistance Paul and don't want to be argumentative but when, I leave the fields empty and click the button a msgbox opens with the name of the control that is blank says that it must be filled in and put the cursor in the offending control.

 

[Micron]

Maybe if it were put to you another way:
Sure the function does stuff, but it returns no value and I think that's the intended message. Typically functions are used to return a value to the calling procedure (although there are exceptions), thus if the function returns nothing to the calling procedure, it might as well be a sub.
Now it sounds like your button click event is the problem. Put a break on that procedure and step through line by line when the button is clicked. Likely you'll find it does what it's being told to do, which is probably not "nothing" but isn't what you expect either.

 

[pbaldy]

I appreciate your assistance Paul and don't want to be argumentative but when, I leave the fields empty and click the button a msgbox opens with the name of the control that is blank says that it must be filled in and put the cursor in the offending control.

You haven't posted your button code. If all it's doing is calling the function, what else do you expect to happen when all the fields have valid values? As mentioned, I'd expect the function to return true or false, one meaning all fields are good, the other meaning validation failed. In your button code, assuming True means validation was successful:

If ValDonorFrm() = True Then
  'do whatever you want
Else
  'do something else or nothing since the function already warned the user
End If

 

[Tupacmoche]

Yes, that is correct there is no return value even as you have both said it does something. I did not get that that is what Paul was saying. Thanks!

 

[Tupacmoche]

I see what your saying in your code Paul. But, the code:

If ValDonorFrm() = 0 Then
Exit Sub
Else
Exit Sub
End If

Is simply meant to call the code like a sub and do everything needed for the validation in that code and that's it. How would it be coded so that it simple calls ValDonorFrm() to run the code check all the controls and then just move on to the next bit on code in the on click event?

 

[pbaldy]

If you don't want to do something different based on whether the validation is successful or not, I guess the function doesn't have to return a value and you just call it:

ValDonorFrm
Next desired line of code

It doesn't make sense to me why you'd continue either way, but I obviously don't know your situation.

 

[pbaldy]

I'll say this a different way. I think the assumption we are operating on is that in our experience, when you have validation code you want to abort whatever process you were doing if validation fails. If fields are okay, save/print/whatever, otherwise stop and wait for the user to fix the issue. It sounds like you want to continue without the user fixing the problem, which may be appropriate to your situation but confused us.

 

[Micron]

How would it be coded so that it simple calls ValDonorFrm() to run the code check all the controls and then just move on to the next bit on code in the on click event?

I think the simplest answer to that is, put the validation code in the click event. Not only would it solve that problem, there is absolutely no need shown here to branch out of the click procedure into a function or a sub, be it within the form module or in a standard module. You're only making the code more complicated.
You validate within the click event, and wherever you want it to end when validation fails, you exit the sub. I/we can't comment on how that should be done as again, you've not shown the click event.Thus whether or not there's any object clean up required, settings reset, etc. can't be commented on.
EDIT- the lack of ability to advise could be extended to not knowing if you're even in the correct procedure. Usually, validation takes place in a BeforeUpdate event, which I think you're not doing.

 

[Tupacmoche]

Micron, I did mention the reason why, I'm not doing the validation in the BeforeUpdate event. Is the reason that, I mentioned valid?

 

[Micron]

You mean this, where you mentioned the AfterUpdate (not BeforeUpdate) event?

Normally, this code would be put into the After Update event of a form but since I have subforms that come before and after the fields being checked which would trigger a save before the user got to the fields I'm using a button to run the code.

It's too late to validate data after a record has been updated/appended. Typically it's done before it is written to the target table, not after.

Not sure what is meant by 'subforms before and after'.

 

[Tupacmoche]

Sorry that was a faux pas, I meant BeforeUpdate.

 

[MajP]

The code that was taken from the web was originally in a Beforeupdate event. The reason I know that there is a line of code
Cancel = True

Cancel is an event parameter of the before update event that allows you to cancel the update, but does nothing in your code. (Which tells me you do not have option explicit set, since it assumes Cancel is just an undeclared variable)

If you want to leave the function broken out just have the function return true or false back to the before update. I think this is what everyone meant that it did not return anything or do anything

Private Sub Form_BeforeUpdate(Cancel As Integer)
  'Cancel is an argument of the event procedure
  'It calls the function to determine if it should cancel
   cancel = ValDoorFrm()
   if cancel then me.undo  
End Sub

The function returns true or false (true if you should cancel)

Public Function ValDonorFrm() as boolean

Dim bCheck As Boolean
Dim ctl As Control

For Each ctl In Me.Detail.Controls
    With ctl
            If .Tag = "v" Then
                If IsNull(.Value) Then
                    MsgBox ctl.Name & " is empty, this is a required field!", vbOKOnly, "Required Field"
                    ctl.SetFocus
                    valDonorFrm = True
                    Exit Function
                End If
            End If
    End With
Next ctl

End Function

 

[Gasman]

MajP,

Where is the function located please.?