Tabbed form validation before switching tabs

[BrentD]

I have a tabbed form built in Access 2007. It's a series of about 32 different questions, with some being fill-in, some drop down boxes to select from, some radio buttons to choose a rating from 1 to 5. The reason I used a tabbed form was just to keep things consolidated on one screen where no scrolling was involved. All of the entries on each of these tabs are deposited into the same single table. I have 7 tabs, and all of the fields on the 7 tabs compose a single record in the table.

I have the actual tabs hidden, and instead use a button to switch to the next tab (Continue button at the bottom) once all the fields on that tab have been completed. I have all of the necessary fields set to be required, but Access does not validate the entries until the very end of the survey when they click a 'submit' button that actually saves the responses and returns to the opening splash page to start the survey again.

What I want to happen is for the fields on the current tab to be validated before it lets the user move to the next tab with the continue button. If they click continue and have left any fields blank they should get a message that all required fields must be completed or something similar to that.

I have found a thread here entitled "Form Validation before Moving to another Tab" (Sorry, won't let me post a link yet) which sounds similar to what I'm doing, but using the button method to navigate to the next tab, there is no BeforeUpdated event to assign that code to. Any help on what my options are? Thanks in advance.

 

[John Big Booty]

Welcome to the Forum.

Given that you are using buttons to move between tabs, you could incorporate some additional code in the buttons code to first validate the data before moving to the next tab.

Without knowing that nature of your validation criteria, I can't advise further at this stage.

 

[BrentD]

Welcome to the Forum.

Given that you are using buttons to move between tabs, you could incorporate some additional code in the buttons code to first validate the data before moving to the next tab.

Without knowing that nature of your validation criteria, I can't advise further at this stage.

Thanks. That's exactly what I would like to do. The only validation criteria is that fields are set to 'required' in their properties in the design of the table. If nothing is in any of those required fields, it needs to not let the user move to the next tab when they click continue. If they all have data in them, then the continue button should take them to the next tab as intended.

 

[John Big Booty]

I think the following should do the trick;

Dim intPgIndx As Integer


Dim ctl As Control

intPgIndx = Me.YourTabCtrl.Pages(Me.YourTabCtrl).PageIndex
    
For Each ctl In Me("YourTabCtrl").Pages(intPgIndx).Controls
    
    If ctl.ControlType = acTextBox Then
        If IsNull(ctl) Or ctl = "" Then
            MsgBox ctl.Name & " Requires a value"
            ctl.SetFocus
            Exit Sub
        End If
    End If
        
    
Next


On Error GoTo ErrorHandler

    
    
    Me.YourTabCtrl.Pages(intPgIndx + 1).SetFocus
    
    Exit Sub
    
ErrorHandler:
    MsgBox "You are on the last tab"
    
    Exit Sub

You'll know how many tabs you have so you will need to test to see when you have reached the last tab and deal with that eventuality as you require.

 

[BrentD]

I think the following should do the trick;

...code removed for space...

You'll know how many tabs you have so you will need to test to see when you have reached the last tab and deal with that eventuality as you require.

That partially works. However, not ALL of the fields in my form are required. For example, there's an assigned position drop down box, but if the person's assigned position isn't on that list they can select other and fill in a write-in field. The write-in field is not required because it would be blank if their position was listed in the assigned position drop down box. I'm not a coding expert by far, but if I follow your code correctly it appears that it's only checking to see if any "acTextBox" controls are empty, and out of those, it's not taking into account whether they are flagged as required or not. I come to that conclusion because I picked an assigned position from the drop down and left the 'other' field blank, but it told me I needed to fill in that one before proceeding.

That being said, since I also have radio button selections on other questions, this doesn't throw an error when I leave those blank either.

I have this code from the other thread I mentioned, but the issue with this code is that it doesn't throw any error when switching to another tab. It only gives the error when I try to close the survey, BUT its errors are accurate in that they work for text boxes, radio buttons or combo boxes properly. I have a feeling that what I need is somewhere between this one and what you have generated.

Private Sub Form_BeforeUpdate(Cancel As Integer)
'This throws the error and still switches tabs
'Enforce required fields on a form
Dim ctl As Control
For Each ctl In Me
 
        If ctl.Tag = "Required" Then
            If IsNull(ctl) Or ctl = "" Then
 
                Cancel = True
                MsgBox "You must complete all required fields to continue", vbCritical, "Required Field"
                ctl.SetFocus
                Exit Sub
            End If
        End If
    Next
    'All fields are validated, now set ctl to essentially being unbound
 
    Set ctl = Nothing
 
End Sub

 

[John Big Booty]

Here's a bit a a kludge that should solve that problem. Add an additional and unique character to the end of the name of all fields that are required;

then amend the code to;

Dim intPgIndx As Integer


Dim ctl As Control

intPgIndx = Me.YourTabCtrl.Pages(Me.YourTabCtrl).PageIndex
    
For Each ctl In Me("YourTabCtrl").Pages(intPgIndx).Controls
    
    If ctl.ControlType = acTextBox Then
        If IsNull(ctl) Or ctl = "" [B][COLOR="Magenta"]And Right(ctl.Name, 1) = "R"[/COLOR][/B] Then
            MsgBox Left(ctl.Name, Len(ctl.Name)-1) & " Requires a value"
            ctl.SetFocus
            Exit Sub
        End If
    End If
        
    
Next


On Error GoTo ErrorHandler

    
    
    Me.YourTabCtrl.Pages(intPgIndx + 1).SetFocus
    
    Exit Sub
    
ErrorHandler:
    MsgBox "You are on the last tab"
    
    Exit Sub

As for testing your option groups you have all the tools you need, so it should be a simple matter for you to add an additional test(s) for other control types

 

[BrentD]

Here's a bit a a kludge that should solve that problem. Add an additional and unique character to the end of the name of all fields that are required;

then amend the code to;

... removed for space ...

As for testing your option groups you have all the tools you need, so it should be a simple matter for you to add an additional test(s) for other control types

Going on the previous code that worked for validating required fields and yours that was validating text boxes only.... would something along these lines work?

Private Sub Command39_Click()
Dim intPgIndx As Integer
Dim ctl As Control
intPgIndx = Me.TabCtl84.Pages(Me.TabCtl84).PageIndex
 
For Each ctl In Me("TabCtl84").Pages(intPgIndx).Controls
 
        If ctl.Tag = "Required" Then
            If IsNull(ctl) Or ctl = "" Then
                MsgBox ctl.Name & " Requires a value"
                ctl.SetFocus
                Exit Sub
            End If
        End If
 
Next
 
On Error GoTo ErrorHandler
 
 
 
    Me.TabCtl84.Pages(intPgIndx + 1).SetFocus
 
    Exit Sub
 
ErrorHandler:
    MsgBox "You are on the last tab"
 
    Exit Sub
Me.CourseContent.SetFocus
End Sub

I tried that code and it works as far as validating the right fields, but it only does it when you try to exit and/or save and not when clicking to go to the next tab (where yours DOES validate before going to the next tab). I'm trying to do this with one chunk of code (and not one for each control type) as it seems as if it should be possible, I'm just not sure what I'm missing here. I'm trying to avoid having to go in and modify all of my control names to validate them based on an R being added to the end, particularly since we already have a required or not property that they could be validated with, no? I'm just missing something in the code that does the validating before allowing you to switch to the next tab.

 

[John Big Booty]

See how this works;

Dim intPgIndx As Integer


Dim ctl As Control

intPgIndx = Me.YourTabCtrl.Pages(Me.YourTabCtrl).PageIndex
    
For Each ctl In Me("YourTabCtrl").Pages(intPgIndx).Controls
    
        If IsNull(ctl) Or ctl = "" And Right(ctl.Name, 1) = "R" Then
            MsgBox Left(ctl.Name, Len(ctl.Name)-1) & " Requires a value"
            ctl.SetFocus
            Exit Sub
        End If
        
    
Next


On Error GoTo ErrorHandler

    
    
    Me.YourTabCtrl.Pages(intPgIndx + 1).SetFocus
    
    Exit Sub
    
ErrorHandler:
    MsgBox "You are on the last tab"
    
    Exit Sub

You would of course need to implement the kludge previously mentioned.

 

[boblarson]

I usually use the TAG property to be able to identify things to either use or to skip over. So, if you put REQUIRED in the TAG property of the control you can use

For Each ctl In Me.Controls
   If ctl.Tag = "REQUIRED" Then
     ' do your validation here
   End If
Next

 

[BrentD]

Ok, finally got it sorted out. Bob's comment on the TAG property jarred something in my thoughts. The original code I tried (in post #5) had a check in it for what I thought was the 'required' property, but I never got any flags for incomplete answers til the end when I tried to save the survey. I wasn't aware that the TAG property even existed, so I assumed my earlier code was checking the required property when it was, in fact, checking the TAG property instead.

So...I used the code I posted in post #7. The reason it wasn't flagging anything when I tried to change tabs is because I didn't have any TAG properties in any of my fields, and it was only checking at the end programmatically due to the required property I had set on the fields...basically like it was supposed to be doing. So I added "Required" in the TAG property of all my fields (this was easier than changing all the field names to add the R suffix) and it all works like a charm now.

I'm a graphic designer by trade, and my only previous programming experience of any sort was HTML and CSS. Over the winter my job has had me developing products in Flash and now this oddball one in Access, so I've picked up VBA and ActionScript recently. Amazed at all I've been able to implement and absorb, and a TON of it is due to this very site, so I'm much appreciative for all the help! Thanks John for putting me on the right trail, and Bob for jarring my thought process!

I even impress myself at what all this survey tool I've been developing can do with how little I know I knew about Access 4 months ago!