Form validation based on Yes/No selection (1 Viewer)

H

hardhitter06

Registered User.
Local time
Today, 13:21
Joined
Dec 21, 2006
Messages
600
Hi All,

It was recommended to put this in a fresh thread since it is a new problem.

I inquired on this original thread (http://www.access-programmers.co.uk/forums/showthread.php?t=219821&page=2) to hide/show fields based on two Yes/No dropdowns. This was accomplished thanks to BL.

Summary:
1.
If "OtherUnivEmployeesInvolved" = "Yes":
The fields
"OtherUnivEmployeeFullName1"
"OtherUnivEmployeeFullName2"
"OtherUnivEmployeeFullName3" are shown.

If "OtherUnivEmployeesInvolved" = "No": These 3 fields are hidden.

2.
If "OutsideRepresentVendor" = "Yes":
The fields
"OutsideIndividualLastName"
"OutsideIndividualFirstName"
"OutsideIndividualCompanyName"
"OutsideCompanyStreetAddress"
"OutsideCompanyCity"
"OutsideCompanyState"
"OutsideCompanyZip" are shown.

If "OutsideRepresentVendor" = "No": These 7 fields are hidden.

Now here's where i need some help. I have this basic validation code when a user hits the save button:

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctrl As Control

    For Each ctrl In Me.Controls
        If ctrl.Tag <> "skip" Then
            If ctrl.ControlType = acTextBox Then
                If IsNull(ctrl) Then
                    MsgBox ctrl.Name & " Cannot Be Left Empty!"
                    Cancel = True
                    ctrl.SetFocus
                    Exit Sub
               
                End If
            End If
        End If
    Next
    MsgBox "Record Saved!"
End Sub

Within "1." - If yes is selected, I only need "OtherUnivEmployeeFullName1" required.

Usually skipping FullName2 and FullName3 would be easy using the ctrl.tag "skip" but I am already using the control to show hide these fields so I'm kind of stumped how to do that.

Within "2." - If yes is selected, I need all 7 fields required.

So I need to figure out how to require fields based on those Yes/No selections because right now the form is checking every field regardless of the yes/no selections. I would also need to skip "OtherUnivEmployeeFullName2" and "OtherUnivEmployeeFullName3" everytime.

Thank you

Josh
 

Attachments

  • SunlightDB.accdb
    1.3 MB · Views: 60
missinglinq

missinglinq

AWF VIP
Local time
Today, 13:21
Joined
Jun 20, 2003
Messages
6,423
hardhitter06 said:
...Usually skipping FullName2 and FullName3 would be easy using the ctrl.tag "skip" but I am already using the control to show hide these fields so I'm kind of stumped how to do that...
Click to expand...
Am fuzzy trying to follow your exact logic here; my fault, not yours, Josh. Don't have time to schlepp through your app, right now, but a couple of things for you to know/think about:

First off, using your current Tag of 'Skip' to hide/show Controls doesn't prevent your from using it for other purposes, as well.

Secondly, and probably more importantly, here, you can assign multiple Tags to any given Control! In the Tag Property Box on the 'Other' Tab, in the Properties Pane, you simply separate them using a semi-colon:

For ControlA a Tag of Group1; Group2

or

For ControlB a Tag of Group1; Group3

Then, instead of checking using the syntax

If ctl.Tag = "Group1" Then

you'd use something like

If InStr(ctl.Tag, "Group1") > 0 Then

which would would let you do something to both ControlA and ControlB

In other words, you can assign a given Control to be part of more than one group of Controls. Or you could do things to Controls that belong to more than one group.

Some typical code would be:

Code: 
Dim ctl As Control

For Each ctl In Me.Controls
  
 If InStr(ctl.Tag, "Group1") > 0 Or InStr(ctl.Tag, "Group2") > 0 Then
   'Do something if Group1 or Group2 are present
 End If
    
 If InStr(ctl.Tag, "Group3") > 0 Or InStr(ctl.Tag, "Group3") > 0 Then
   'Do something if Group3 is present
 End If

Next ctl

Confused enough now?

Linq ;0)>
 
H

hardhitter06

Registered User.
Local time
Today, 13:21
Joined
Dec 21, 2006
Messages
600
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctrl As Control

    For Each ctrl In Me.Controls
        If ctrl.Tag <> "skip" Then
            If ctrl.ControlType = acTextBox Then
                If IsNull(ctrl) Then
                    MsgBox ctrl.Name & " Cannot Be Left Empty!"
                    Cancel = True
                    ctrl.SetFocus
                    Exit Sub
                    Exit For
                    End If
    ElseIf ctrl.ControlType = acComboBox Then
              If IsNull(ctrl) Then
              MsgBox ctrl.Name & " Cannot Be Left Empty!"
              Cancel = True
              ctrl.SetFocus
              Exit Sub
              Exit For
              End If
            End If
        End If
    Next
    MsgBox "Record Saved!"
End Sub

I realized I had my combobox not being validated...seperate from the Yes/No so I added that in.

I tried what you said "OTHERINVOLVED; skip" for FullName2 and FullName3 and it still tried to validate them. I wouldn't think I'd have to change my code around for that to work since you said you can have multiple controls for one tag.

And now those fields are not being hidden when No is selected.
 
missinglinq

missinglinq

AWF VIP
Local time
Today, 13:21
Joined
Jun 20, 2003
Messages
6,423
You can have multiple Tags but how you check for a given Tag has to change, as I demonstrated above.

I've been over all Controls on the Form and do not see the Tag 'skip' anywhere in the copy you posted.

Given your requirements

Within "1." - If yes is selected, I only need "OtherUnivEmployeeFullName1" required.

Within "2." - If yes is selected, I need all 7 fields required.

I would also need to skip "OtherUnivEmployeeFullName2" and "OtherUnivEmployeeFullName3" everytime.
Click to expand...

This code covers them:

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctrl As Control

If Me.OtherUnivEmployeesInvolved = -1 Then
 If Nz(Me.OtherUnivEmployeeFullName1, "") = "" Then
   MsgBox "The OtherUnivEmployeeFullName1 Field Cannot Be Left Empty!"
   Cancel = True
   OtherUnivEmployeeFullName1.SetFocus
   Exit Sub
 End If
End If

If Me.OutsideRepresentVendor = -1 Then
  
  For Each ctrl In Me.Controls
    
    If ctrl.Tag = "OUTSIDEENTITY" Then
      If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
         If Nz(ctrl, "") = "" Then
           MsgBox ctrl.Name & " Cannot Be Left Empty!"
           Cancel = True
           ctrl.SetFocus
           Exit Sub
          End If
      End If
    End If
    
    Next
    
    MsgBox "Record Saved!"

End If

End Sub

Looking at the complexity of your Form's module, I suspect that you're going to have other problems, as you progress, but the above addresses the validation for those stated conditions.

Linq ;0)>
 
H

hardhitter06

Registered User.
Local time
Today, 13:21
Joined
Dec 21, 2006
Messages
600
Well here's an idea...I just tried your code and it handles everything from the first drop down and below.

If I use a Ctrl Tag group ("Required") for the rest of my felds (above portion of the form) and have that group validated with the otheir groups validation then that should work right?
 
Last edited:
H

hardhitter06

Registered User.
Local time
Today, 13:21
Joined
Dec 21, 2006
Messages
600
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctrl As Control

If Me.OtherUnivEmployeesInvolved = -1 Then
 If Nz(Me.OtherUnivEmployeeFullName1, "") = "" Then
   MsgBox "The OtherUnivEmployeeFullName1 Field Cannot Be Left Empty!"
   Cancel = True
   OtherUnivEmployeeFullName1.SetFocus
   Exit Sub
 End If
End If

If Me.OutsideRepresentVendor = -1 Then
  
  For Each ctrl In Me.Controls
  
     If ctrl.Tag = "Required" Then
      If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
         If Nz(ctrl, "") = "" Then
           MsgBox ctrl.Name & " Cannot Be Left Empty!"
           Cancel = True
           ctrl.SetFocus
           Exit Sub
          Exit For
      End If
    
     ElseIf ctrl.Tag = "OUTSIDEENTITY" Then
      If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
         If Nz(ctrl, "") = "" Then
           MsgBox ctrl.Name & " Cannot Be Left Empty!"
           Cancel = True
           ctrl.SetFocus
           Exit Sub
           Exit For
          End If
      End If
    End If
    
    Next
    
    MsgBox "Record Saved!"

End If

End Sub

Something is off here, the order is messed up, definately doesn't flow right....
 
H

hardhitter06

Registered User.
Local time
Today, 13:21
Joined
Dec 21, 2006
Messages
600
I think this is in the right order but all my Ifs and Fors and what not are probably wrong. I'm getting an error on the Next "Compile error: Next without For".

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctrl As Control

For Each ctrl In Me.Controls
  
     If ctrl.Tag = "Required" Then
      If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
         If Nz(ctrl, "") = "" Then
           MsgBox ctrl.Name & " Cannot Be Left Empty!"
           Cancel = True
           ctrl.SetFocus
           Exit Sub
         Exit For
      End If
      
    ElseIf Me.OtherUnivEmployeesInvolved = -1 Then
        If Nz(Me.OtherUnivEmployeeFullName1, "") = "" Then
        MsgBox "The OtherUnivEmployeeFullName1 Field Cannot Be Left Empty!"
        Cancel = True
        OtherUnivEmployeeFullName1.SetFocus
        Exit Sub
        Exit For
    End If
    End If
      
    ElseIf Me.OutsideRepresentVendor = -1 Then
     If ctrl.Tag = "OUTSIDEENTITY" Then
      If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
         If Nz(ctrl, "") = "" Then
           MsgBox ctrl.Name & " Cannot Be Left Empty!"
           Cancel = True
           ctrl.SetFocus
           Exit Sub
           Exit For
      End If
    End If
    End If
    
    Next
    
    MsgBox "Record Saved!"

End If

End Sub
 
missinglinq

missinglinq

AWF VIP
Local time
Today, 13:21
Joined
Jun 20, 2003
Messages
6,423
Where did you get the Exit For line from? There's no reason for it and I suspect that it's causing the problem.

Linq ;0)>
 
H

hardhitter06

Registered User.
Local time
Today, 13:21
Joined
Dec 21, 2006
Messages
600
Linq,

Can't figure this out, still getting an error in that spot. Please helpppp lol

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctrl As Control

For Each ctrl In Me.Controls
  
     If ctrl.Tag = "Required" Then
      If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
         If Nz(ctrl, "") = "" Then
           MsgBox ctrl.Name & " Cannot Be Left Empty!"
           Cancel = True
           ctrl.SetFocus
           Exit Sub
          End If
       End If
      
      
    ElseIf Me.OtherUnivEmployeesInvolved = -1 Then
        If Nz(Me.OtherUnivEmployeeFullName1, "") = "" Then
        MsgBox "The OtherUnivEmployeeFullName1 Field Cannot Be Left Empty!"
        Cancel = True
        OtherUnivEmployeeFullName1.SetFocus
        Exit Sub
        End If
   
      
    ElseIf Me.OutsideRepresentVendor = -1 Then
     If ctrl.Tag = "OUTSIDEENTITY" Then
      If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
         If Nz(ctrl, "") = "" Then
           MsgBox ctrl.Name & " Cannot Be Left Empty!"
           Cancel = True
           ctrl.SetFocus
           Exit Sub
         End If
      End If
    
    Next
    
    MsgBox "Record Saved!"

End If

End Sub
 
boblarson

boblarson

Smeghead
Local time
Today, 10:21
Joined
Jan 12, 2001
Messages
32,059
You have your Next and last End If in the wrong place. It should be like this:

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.Tag = "Required" Then
            If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
                If Nz(ctrl, "") = "" Then
                    MsgBox ctrl.Name & " Cannot Be Left Empty!"
                    Cancel = True
                    ctrl.SetFocus
                    Exit Sub
                End If
            End If
 
        ElseIf Me.OtherUnivEmployeesInvolved = -1 Then
            If Nz(Me.OtherUnivEmployeeFullName1, "") = "" Then
                MsgBox "The OtherUnivEmployeeFullName1 Field Cannot Be Left Empty!"
                Cancel = True
                OtherUnivEmployeeFullName1.SetFocus
                Exit Sub
            End If
 
        ElseIf Me.OutsideRepresentVendor = -1 Then
            If ctrl.Tag = "OUTSIDEENTITY" Then
                If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
                    If Nz(ctrl, "") = "" Then
                        MsgBox ctrl.Name & " Cannot Be Left Empty!"
                        Cancel = True
                        ctrl.SetFocus
                        Exit Sub
                    End If
                End If
[COLOR=red]            [B]End If[/B][/COLOR]
[COLOR=red]        [B]Next[/B][/COLOR]
[COLOR=red]        MsgBox "Record Saved!"[/COLOR]
    End Sub
 
H

hardhitter06

Registered User.
Local time
Today, 13:21
Joined
Dec 21, 2006
Messages
600
Still getting an error in the same spot even with the change.

I attached DB.
 

Attachments

  • SunlightDB.zip
    209.9 KB · Views: 60
H

hardhitter06

Registered User.
Local time
Today, 13:21
Joined
Dec 21, 2006
Messages
600
Wow that is so frustrating that it was as simple as that.

Big thanks to Bob and Linq...you guys are amazing. Have a great weekend!!
 
You must log in or register to reply here.

Similar threads

C
Force requirement based on yes/no
Replies
10
Views
557
Crabman1985
C
D
Updating table based on main form and combo box on subform
2
Replies
28
Views
830
Design by Sue
D
MajP
Show / Hide Columns Easily in Datasheet
Replies
3
Views
387
skymou
S
J
Clear Yes/No field in form with a command button
Replies
5
Views
565
MajP
MajP
H
Update subform based on main form combo box
Replies
5
Views
455
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom