'If all controls are valid then' How do I say this in VBA?

[nick404]

How would I be able to say something like ' if all the controls are valid then cancel = false ' ?



Right now I have a public cancel (glblcancel) and my before update sets it to true if some controls are blank. Now if all these controls are not blank, i.e. 'valid', I would like to set my public cancel to false in order to run my after update. I am using an unbound form. My 'save' button:

Sub cmdenter_Click()
Call BeforeUpdate(0)
If glblcancel = false Then
Call AfterUpdate()
ElseIf glblcancel = True Then
End If
End Sub

However since once a single control is blank then glblcancel = True and doesn't get set back to false ever, so in theory I can't stop my after update from running.
All help appreciated!


EDIT: before update code. added the blue lines and it works well but once I have a majority of fields 'valid' then the after update occurs anyway.

For Each ctl In Me.Section("Detail").Controls
    Select Case ctl.Tag
        Case "Required;Check"
            If Len(Trim(ctl.Value & "")) = 0 Then
            ctl.BackColor = &HD0D0FF
            glblcancel = True
            End If
            If Not Len(Trim(ctl.Value & "")) = 0 Then
            glblcancel = False
            End If
        End Select
Next ctl
End Sub

[ItsMe]

Maybe you can have your For Each statement ion a private function within your module. Have your private function return a value. Call your private function from your Before Update. Also, call your private function from your Click Event.

private function checkRequiredIsComplete() as Boolean

checkRequiredControls = False


if <Your code here that determines all is good> then
checkRequiredControls = true
end if


end function

[nick404]

Not quite understanding what this accomplishes:

As in, what differentiates the private fxn's 'checkRequiredControls = false/true' from my public variable 'glblcancel = true/false' in the for each statement

[ItsMe]

You have the following in your Form's Before Update ...

For Each ctl In Me.Section("Detail").Controls
    Select Case ctl.Tag
        Case "Required;Check"
            If Len(Trim(ctl.Value & "")) = 0 Then
            ctl.BackColor = &HD0D0FF
            glblcancel = True
            End If
            If Not Len(Trim(ctl.Value & "")) = 0 Then
            glblcancel = False
            End If
        End Select
Next ctl

Instead of placing that in your Before Update, you can create a function and place it in a function. Then, you can call the function from multiple events. you can create a private function within your form's module or a public function within a Standard Module.

[nick404]

Ah Okay, however, my issue is not with where the validation is located. The validation itself works well, but I need a way to 'count', per se, how many controls have been considered 'valid' (i.e. contain data). I have 7 fields in my form that I check with the code. And currently what I have works until 4/7 fields are filled/valid, then the AfterUpdate proceeds, since the majority of glblcancel is true (I imagine this is what is occurring).

Sub cmdenter_Click()
Call BeforeUpdate(0)
If glblcancel = false Then
Call AfterUpdate()
ElseIf glblcancel = True Then
End If
End Sub

Is there a way for me to count the value of glblcancel and when it is appropriate for 7 valid controls then Call AfterUpdate()?

[ItsMe]

I changed your code a little. There was some extraneous code I removed. It seemed you were calling the form's Before Update event and that was why I was suggesting to create a function. Maybe there is another name available that better describes what the procedure does. Overall, I do not understand what you are trying to do. I understand you are trying to determine if mandatory fields are populated. I don't understand your other objectives.

Why are you passing the argument 0 to your BeforeUpdate procedure?
What is glblcancel, a Boolean? And where else is it used?

What is the afterupdate procedure doing, appending records? Running a query?

Sub cmdenter_Click()

Call BeforeUpdate(0)

If glblcancel = false Then
Call AfterUpdate()
end if

End Sub

[nick404]

The AfterUpdate adds records to a table, that's pretty much it.

glblcancel is a Public variable that is a Boolean. It is only used in my BeforeUpdate event and my save button's click event. I am using a public variable so I can reference it in my 'save' button's on click to decided whether or not to run the AfterUpdate.

The (0) with my BeforeUpdate is there because as I was browsing the net for some help I noticed most calls had the (0) after the BU and when I tried it without (just as Call BeforeUpdate ) i got an error 'Argument not optional' so I put it back.

So let me try and better explain my issue here..
Yes, I am trying to check if certain fields are populated when the form is submitted, a total of 7 fields I check (denoted with a Tag property of "Required;Check"). If any of the fields are blank they are highlighted in red so the user can see what needs to still be completed (from my other post on setting back color-- that is all taken care of now-- have that part functioning).

What I want to have happen now is if ALL the 7 controls with the Tag property are 'valid' (populated) then and only then should the AfterUpdate (committing record to table) code run. Upon reflection this key part may have been fairly ambiguous in my OP - apologies for that. Currently this code below is in my BeforeUpdate event. It does it's job until there are 4 out of 7 fields populated in the form, then the AfterUpdate fires, but it shouldn't since 3 fields are still blank.

For Each ctl In Me.Section("Detail").Controls
    Select Case ctl.Tag
        Case "Required;Check"
            If Len(Trim(ctl.Value & "")) = 0 Then
            ctl.BackColor = &HD0D0FF
            glblcancel = True
            End If
            If Not Len(Trim(ctl.Value & "")) = 0 Then
            glblcancel = False
            End If
        End Select
Next ctl
End Sub

So I only want the AfterUpdate to be run if all 7 of the 'mandatory' fields (mandatory by the Tag ppty) in my form are populated.
The current Code behind my button is what you posted above with the Calls and If .. Then statement. Is there a way to, in layman's terms, say if glblcancel = false (7 times) then proceed to AfterUpdate?

[nick404]

I suppose I could just look at each control separately but I was just curious if there was a shorter way...

[ItsMe]

Here, try this. You will probably need to validate more than one form. So you should create a Public Function in a Standard Module. Start by creating a new standard module and then past the following code inside. Save the module and call it anything but RequiredFields, maybe name your module modCheckFields.

Here is the function.

Public Function RequiredFields(FormName As String) As Boolean

Dim ctl As Control
Dim frm As Form

Set frm = Application.Forms(FormName)

'Make sure the required fields have values
RequiredFields = False

    For Each ctl In frm.Controls

        If InStr(ctl.Tag, "Required") <> 0 Then
        ctl.BackColor = 16777215
            If IsNothing(ctl.Value) Then
                ctl.BackColor = 65535
            RequiredFields = True
            End If  'IsNothing
        End If  'Required

    Next ctl
    
If RequiredFields = True Then
    MsgBox "Please complete the required fields."
Exit Function

End If

End Function

With this, you can call the function from any "Form". You would call it like this ...

If RequiredFields(Me.Name) Then
    Your code here because not all of the fields are populated
else
    Your code here because everything is good
End If

There is one caveat to the function I posted. It uses another function to check for variables that are Nothing, Empty String, equal to zero, etc. I will PM you this function.

[ssanfu]

I think we need to step back and forget about the code for now.

Let's say you have 3 text box controls you want to have filled before saving. And let's say the first control is empty/null and the other two have entries.
There is a global boolean variable. "glblcancel", that when TRUE cancels the save and when FALSE allows the save. "glblcancel" defaults to FALSE when the form is opened.

So the first control is checked and, since the control is empty/null, glblcancel is set to TRUE.
The second control is checked and, since it has an entry, glblcancel is set to FALSE.
The third control is checked and, since it has an entry, glblcancel is set to FALSE.
Is this correct? No, since the first control is empty/null, the save should be canceled.

----------------------------------------------------------------------------

This is the code I would try: (air code - I modified your code)

For Each ctl In Me.Section("Detail").Controls
    Select Case ctl.Tag
        Case "Required;Check"
            If Len(Trim(ctl.Value & "")) = 0 Then
            ctl.BackColor = &HD0D0FF
            glblcancel = True
            Exit For
         Else
            ctl.BackColor = vbWhite ' or whatever your default backcolor is
            glblcancel = False
            End If
        End Select
Next ctl
End Sub

The above code stops at the first control that is empty/null, so if you had 15 controls, you could have to click the save button 15 times (worst case )


This code snippet checks all controls, setting the BG color if the control is empty/null. Then is sets the global variable to TRUE/FALSE.

    Dim Knt As Integer  'new declaration
    .
    .
    Knt = 0
    For Each ctl In Me.Section("Detail").Controls
        Select Case ctl.Tag
            Case "Required;Check"
                If Len(Trim(ctl.Value & "")) = 0 Then
                    ctl.BackColor = &HD0D0FF
                    Knt = Knt + 1
                Else
                    ctl.BackColor = vbWhite    ' or whatever your default backcolor is
                End If
        End Select
    Next ctl

    If Knt = 0 Then
        glblcancel = False
    Else
        glblcancel = True
    End If

Hope this makes sense.......

[nick404]

You all have been extremely helpful
Thanks beyond count

[ItsMe]

OK, let us know how things go.

[nick404]

Happy to report the functionality of the form is superb!

ItsMe, the Modules and coding you provided worked perfect, and I am glad to have verification module up my sleeve for other projects.

Steve, the code you provided works perfectly as well, it also taught me a way to 'count' more or less.