Validate group of textboxes for null

[Anthony George]

Hi Everyone

I wonder if anyone can help please.

I have a form with 40 textboxes.

They are divided into 4 groups of 10.

I need my user to fill in at least one box in each group before closing the form.

Is there a way to test each group to see if this has been done and to pop a message box up if at least one of the boxes in any group has not been filled in.

Just a bit of code to test one group will be fine, I will work the rest out for myself.

Kindest Regards

Tony

 

[boblarson]

Someone might have a better solution, but this is what my tired brain came up with just now.

Put a number into the tag of each text box. Make it the number of the group. So, you have 4 groups of 10, so in the first 10 put a 1 into the TAG property of the text box. Then, in the second group of 10 put a 2, and so on.

Then, you can take this code and put it into a standard module (not a form or report module and make sure the name of the module is not the same as the function):

Function TestForNulls(frm As Form) As String
    Dim ctl As Control
    Dim strMissing As String

    For Each ctl In frm.Controls
        If ctl.ControlType = acTextBox Then
            If ctl.Tag <> "" Then
                If Nz(ctl.Value & "") = "" Then
                    Select Case ctl.Tag
                    Case 1
                        If InStr(1, strMissing, "1", vbTextCompare) = 0 Then
                            If strMissing = "" Then
                                strMissing = "1, "
                            Else
                                strMissing = strMissing & "1,"
                            End If
                        End If
                    Case 2
                        If InStr(1, strMissing, "2", vbTextCompare) = 0 Then
                            If strMissing = "" Then
                                strMissing = "2, "
                            Else
                                strMissing = strMissing & "2,"
                            End If
                        End If
                    Case 3
                        If InStr(1, strMissing, "3", vbTextCompare) = 0 Then
                            If strMissing = "" Then
                                strMissing = "3, "
                            Else
                                strMissing = strMissing & "3,"
                            End If
                        End If
                    Case 4
                        If InStr(1, strMissing, "4", vbTextCompare) = 0 Then
                            If strMissing = "" Then
                                strMissing = "4, "
                            Else
                                strMissing = strMissing & "4,"
                            End If
                        End If
                    End Select
                    If strMissing <> "" Then
                        TestForNulls = "Missing values for groups " & strMissing
                    Else
                        TestForNulls = ""
                    End If
                End If
            End If
        End If
        Next
    End Function

 

[Anthony George]

Hi Bob

Thank's for the code it looks great and I'm sure it will do the job if I can work out how to call it properly.

I made a new module (module1), pasted the code into it as instructed.

I then put all of the 1s, 2s etc. in to the tag properties on my form.

I have a close button on my Student_Details form, which is the form I want to check so in the on click event I tried Call TestForNulls and it cme back with the error Compile error argument not optional.

I then tried Call TestForNulls("Student_Details")

and it came back with object required.

I have tried various ways to call this but alas I'm getting knowhere.

Am I doing something really stupid!!

Thank's Again

Tony

 

[boblarson]

Click the DEBUG on the Error dialog to see which line it is returning.

 

[Anthony George]

Hi Bob
If I call the routine from my close button with:-

Call TestForNulls(Student_Details)

It returns the error 424 object required

When I click Debug

It Highlights Call TestForNulls(Student_Details) in yellow

Cheers

Tony
PS
Is the above call syntax correct?

 

[boblarson]

Hi Bob
If I call the routine from my close button with:-

Call TestForNulls(Student_Details)

It returns the error 424 object required

When I click Debug

It Highlights Call TestForNulls(Student_Details) in yellow

Cheers

Tony
PS
Is the above call syntax correct?

Is StudentDetails the form that the code calling the function is on? If not, then you would need to use
Call TestForNulls(Forms!Student_Details)

and it has to be open.

And if it is the form that the call is on, then you just use

Call TestForNulls(Me)

 

[Anthony George]

Hi Bob
Hey! No more error messages

However what is it supposed to do?

If I fill all of the boxes the form closes ok without any message.

If I leave all of the boxes empty the form closes without any message.

Should there be a msgbox prompt when they are all empty or something?

Cheers

Tony

 

[boblarson]

Hi Bob
Hey! No more error messages

However what is it supposed to do?

If I fill all of the boxes the form closes ok without any message.

If I leave all of the boxes empty the form closes without any message.

Should there be a msgbox prompt when they are all empty or something?

Cheers

Tony

Actually, that's up to you. What do you need it to do? I built it just to return a message but you can modify it to do whatever.

 

[Anthony George]

Hi Bob

I would like it to trigger a message box if all of the boxes in 1 group are empty,
asking the user to fill one in before closing the form.

Can you give me the final bit of code to do this please?

Thanks

Tony

 

[boblarson]

I think this could work:

Change the Case to:

                    Case 1
                        If InStr(1, strMissing, "1", vbTextCompare) = 0 Then
                            If strMissing = "" Then
                                strMissing = "1, "
                            Else
                                strMissing = strMissing & "1,"
                            End If
                        End If
          If strMissing = "" Then
             MsgBox "You need to fill out at least one box in Group 1" & vbCrLf & _
"Please go back and fill one out", vbExclamation, "Error"
          End If

That should generate the message, but if you need to actually cancel the update beforehand, you may need to declare a variable in the General Declarations section (like Private blnCancel As Boolean) and then add to the end of the part I showed in this thread (blnCancel = True)
And then in your form's before update event you could use

Cancel = blnSave

 

[Anthony George]

Hi Bob

Thank's for all of your time and answers. I managed to get it all working with a bit of jiggery-pokery.

Kindest Regards

Tony

 

[boblarson]

Glad to hear you got it working for you.