Function Isn't Calling Correctly (1 Viewer)

[JMongi]

I have two issues with my data validation function.
1. It either doesn't get called (form close w/ nothing)
2. I get an object doesn't support this property or method

Function:

Function FormNullChk(FormName As String) As Boolean

Dim Ctrl As Control
'Return a True if No Nulls or False if Nulls
For Each Ctrl In Forms(FormName).Controls
    If Nz(Ctrl.Value) = 0 Then
        FormNullChk = False
        Exit For
        Exit Function
    End If
Next Ctrl
FormNullChk = True

End Function

BeforeUpdate Sub:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If FormNullChk(Me.Name) = False Then
    Cancel = True
Else
    Exit Sub
End If

End Sub

 

[JMongi]

That's why I love posting. The minute I post I already see one thing...I didn't add my message back in.

Updated Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If FormNullChk(Me.Name) = False Then
    MsgBox ("Please complete all fields before saving.")
    Cancel = True
Else
    Exit Sub
End If

End Sub

 

[MajP]

So there are many controls that do not support the value property (labels, images etc)
Normally Tag the ones you want

For Each Ctrl In Forms(FormName).Controls
   If ctrl.Tag = "YourTagNameHere"
   If isnull(Ctrl.Value) Then
        FormNullChk = False
        Exit For
        Exit Function
    End If
 end if
Next Ctrl

this will logically fail if you have a vaule of 0. I doubt that is what you want. So use isnull unless you are not allow zero values.

 

[Isaac]

The form control loop is too broad. you'll probably need to add a typename()="Textbox" type of check.
dim tb as textbox, ctl as control
for each ctl
if typename(ctl)="Textbox" then set tb=ctl, etc
labels for example, no value

 

[gemma-the-husky]

Function FormNullChk(FormName As String) As Boolean

Dim Ctrl As Control
'Return a True if No Nulls or False if Nulls
For Each Ctrl In Forms(FormName).Controls
    If Nz(Ctrl.Value) = 0 Then
        FormNullChk = False
        Exit For
        Exit Function
    End If
Next Ctrl
FormNullChk = True

End Function

Is this part of a form validation process?

This may only check the first control. Is that what you expect?
Add a msgbox to see which control you are checking, and what it finds. I am sure that will help.
The exit function line does nothing because you already exit for.
You ought to have some error handling.
I suspect .value is inaccurate. Some controls may not even have a value at all, and certainly some control types won't have value of 0.

Note that a false checkbox will have a value of zero, and that surely is not a reason to cancel the update.

I see others have made observations about improving the code.

I would probably validate by checking named controls, It might take a bit longer to write, but it's safer. Any non-blank entry may pass your test, but still may not really be acceptable as reasonable.

 

[The_Doc_Man]

Of the over 30 distinct types of controls, not that many have a .Value so "...doesn't support this property or method" means you found something that is a control without a value. Hint: If you have labels associated with your text boxes, none of the labels have values. Lines don't have values either. Nor do rectangles.

Not only that, but if a control with a .Value is unbound, it doesn't have a .OldValue and so can never become "dirty." So when writing code to deal with properties of controls, you should ALWAYS validate that the control in question will have the value you want.

 

[arnelgp]

Function FormNullChk(FormName As String) As Boolean

Dim Ctrl As Control
On Error Resume Next
'Return a True if No Nulls or False if Nulls
For Each Ctrl In Forms(FormName).Controls
    'arnelgp
    If Len(Ctrl.ControlSource) > 0 Then
        If Left$(Ctrl.ControlSource, 1) <> "=" Then
            If Len(Ctrl.Value & "") = 0 Then
                FormNullChk = False
                Exit For
                Exit Function
            End If
        End If
    End If
Next Ctrl
FormNullChk = True

End Function

 

[Minty]

As others have stated, I always think it is much better to present the user with a description of what they have missed.
I use a similar technique to loop around all the tagged required controls, but add the missing field descriptions to a string then display that as part of the validation.

On some forms, I highlight the missing data controls as well.

 

[Rene vK]

I use an event class for textbox.backcolor and a final check for missing/wrong info in required fields (like @The_Doc_Man )
Must notice that I allways use a button to add a new, single, record to a table.

 

[JMongi]

Thanks for all of the replies! It's given me a lot of food for thought.
1. I do need some error checking. I haven't added it into any of my other code since I'm in development mode. It's past time that I did.
2. This is part of a form validation process. I'd like to streamline form validation where I pass the form name to a function and get a true/false that all necessary form controls are filled in. I can see that I need a more robust handling of the specific controls. I also think the tag property is perfect for this idea.
3. I like the idea of capturing the control name(s) that are flagging the check and presenting that to the user. I'll incorporate that as well.
4. Thanks for the clarification on Nz(). I misunderstood the description of how it works (or how I wanted to use it). Either way IsNull() is definitely the correct way to go.
5. I had originally not cared if the function cycled through all the controls. I just needed one to flag to cancel the update. But, given the new scope, cycling through all (appropriate) controls is the better way to go.

This isn't meant to be an all encompassing check either. This was meant to be an easy first step to run before any more complex form validation.

 

[gemma-the-husky]

Checkboxes are tricky. A false textbox will have a value of zero. In Access you can't have a null yes/no, although you can in SQL server.
So there is not really any point testing a check box. The same could be true of a number zero, and of a zero length string. Finding a sort of "default" value doesn't really confirm whether the user has considered whether the control needs a particular value.

In general terms, in a function I would try to return true as a meaningful answer, rather than false. ie a function is by default false, unless something in it makes it true.

 

[Isaac]

I had originally not cared if the function cycled through all the controls. I just needed one to flag to cancel the update. But, given the new scope, cycling through all (appropriate) controls is the better way to go.

I mean to me personally it doesn't seem wrong at all to stop the function and flag as False at the first sign of misinformation, FWIW.

Going back to my original response to you about textboxes (for example) vs. Controls, and needing to declare both, then set tb=ctl only if the type is correct (which everyone seemed to agree with), I think if you add that one thing your code is more or less ready. Maybe check if len("" & tb.value)=0 ... that handles everything.