Link to home
Start Free TrialLog in
Avatar of Senniger1
Senniger1

asked on

Help Streamling VBA Code in MS Access

I have a command button on a form in a MS Access 2003 database.  The VBA code is included below.  I'm thinking this code could be streamlined with a Select Case or a Function or something, but I don't have much experience with either.  

Private Sub cmdExit_Click()
  On Error GoTo Err_cmdExit_Click
    Me.Refresh
Check_Required_Click:  ' Confirms again all is completed
      If Me.JobPerfa1 = False And Me.JobPerfa2 = False And Me.JobPerfa3 = False _
        Or Me.JobPerfb1 = False And Me.JobPerfb2 = False And Me.JobPerfb3 = False _
            Or Me.JobPerfc1 = False And Me.JobPerfc2 = False And Me.JobPerfc3 = False _
                Or Me.JobPerfd1 = False And Me.JobPerfd2 = False And Me.JobPerfd3 = False _
                    Or Me.JobKnowa1 = False And Me.JobKnowa2 = False And Me.JobKnowa3 = False _
                        Or Me.JobKnowb1 = False And Me.JobKnowb2 = False And Me.JobKnowb3 = False _
                    Or Me.JobKnowc1 = False And Me.JobKnowc2 = False And Me.JobKnowc3 = False _
                Or Me.JobKnowd1 = False And Me.JobKnowd2 = False And Me.JobKnowd3 = False _
            Or Me.Judgmenta1 = False And Me.Judgmenta2 = False And Me.Judgmenta3 = False _
        Or Me.Judgmentb1 = False And Me.Judgmentb2 = False And Me.Judgmentb3 = False _
            Or Me.Judgmentc1 = False And Me.Judgmentc2 = False And Me.Judgmentc3 = False _
                Or Me.Judgmentd1 = False And Me.Judgmentd2 = False And Me.Judgmentd3 = False _
                    Or Me.Judgmente1 = False And Me.Judgmente2 = False And Me.Judgmente3 = False _
                        Or Me.Judgmentf1 = False And Me.Judgmentf2 = False And Me.Judgmentf3 = False _
                    Or Me.Dependa1 = False And Me.Dependa2 = False And Me.Dependa3 = False _
                Or Me.Dependb1 = False And Me.Dependb2 = False And Me.Dependb3 = False _
            Or Me.Dependc1 = False And Me.Dependc2 = False And Me.Dependc3 = False _
        Or Me.Dependd1 = False And Me.Dependd2 = False And Me.Dependd3 = False _
            Or Me.Teamworka1 = False And Me.Teamworka2 = False And Me.Teamworka3 = False _
                Or Me.Teamworkb1 = False And Me.Teamworkb2 = False And Me.Teamworkb3 = False _
                    Or IsNull(Me.txtJobPerf) Or IsNull(Me.txtJobKnow) Or IsNull(Me.txtJudgment) _
                        Or IsNull(Me.txtDepend) Or IsNull(Me.txtTeamwork) Or IsNull(Me.txtOutstanding) Then
        GoTo Ask_Continue_Click
            Else
        GoTo Close_cmdExit_Click
    End If
Ask_Continue_Click:  ' Checks to ensure if everything is complete and directs to incompleted items
    Me.AllowEdits = True
    If Me.JobPerfa1 <> True And Me.JobPerfa2 <> True And Me.JobPerfa3 <> True _
        Or Me.JobPerfb1 <> True And Me.JobPerfb2 <> True And Me.JobPerfb3 <> True _
            Or Me.JobPerfc1 <> True And Me.JobPerfc2 <> True And Me.JobPerfc3 <> True _
                Or Me.JobPerfd1 <> True And Me.JobPerfd2 <> True And Me.JobPerfd3 <> True _
                    Or Me.JobKnowa1 <> True And Me.JobKnowa2 <> True And Me.JobKnowa3 <> True _
                        Or Me.JobKnowb1 <> True And Me.JobKnowb2 <> True And Me.JobKnowb3 <> True _
                    Or Me.JobKnowc1 <> True And Me.JobKnowc2 <> True And Me.JobKnowc3 <> True _
                Or Me.JobKnowd1 <> True And Me.JobKnowd2 <> True And Me.JobKnowd3 <> True _
            Or Me.Judgmenta1 <> True And Me.Judgmenta2 <> True And Me.Judgmenta3 <> True _
        Or Me.Judgmentb1 <> True And Me.Judgmentb2 <> True And Me.Judgmentb3 <> True _
            Or Me.Judgmentc1 <> True And Me.Judgmentc2 <> True And Me.Judgmentc3 <> True _
                Or Me.Judgmentd1 <> True And Me.Judgmentd2 <> True And Me.Judgmentd3 <> True _
                    Or Me.Judgmente1 <> True And Me.Judgmente2 <> True And Me.Judgmente3 <> True _
                        Or Me.Judgmentf1 <> True And Me.Judgmentf2 <> True And Me.Judgmentf3 <> True _
                    Or Me.Dependa1 <> True And Me.Dependa2 <> True And Me.Dependa3 <> True _
                Or Me.Dependb1 <> True And Me.Dependb2 <> True And Me.Dependb3 <> True _
            Or Me.Dependc1 <> True And Me.Dependc2 <> True And Me.Dependc3 <> True _
        Or Me.Dependd1 <> True And Me.Dependd2 <> True And Me.Dependd3 <> True _
            Or Me.Teamworka1 <> True And Me.Teamworka2 <> True And Me.Teamworka3 <> True _
                Or Me.Teamworkb1 <> True And Me.Teamworkb2 <> True And Me.Teamworkb3 <> True _
                    Or IsNull(Me.txtJobPerf) Or IsNull(Me.txtJobKnow) Or IsNull(Me.txtJudgment) _
                Or IsNull(Me.txtDepend) Or IsNull(Me.txtTeamwork) Or IsNull(Me.txtOutstanding) Then
        Select Case MsgBox( _
            "One or more fields are incompleted.  Do you want to continue completing form?" _
               & vbCr & vbCr _
               & "     To continue completing form, click YES" _
               & vbCr & vbCr _
               & "     To close the form, click NO", _
            vbYesNo, "Instructions...")
      Case vbYes
      Me.lblRed.Visible = True
' ***  Job Performance  ***
        If Me.JobPerfa1 <> True And Me.JobPerfa2 <> True And Me.JobPerfa3 <> True _
            Or Me.JobPerfb1 <> True And Me.JobPerfb2 <> True And Me.JobPerfb3 <> True _
                Or Me.JobPerfc1 <> True And Me.JobPerfc2 <> True And Me.JobPerfc3 <> True _
                    Or Me.JobPerfd1 <> True And Me.JobPerfd2 <> True And Me.JobPerfd3 <> True Then
        MsgBox "Job Performance is Incomplete"
            Me.Job_Performance.SetFocus
                GoTo Highlight_Incomplete_Items
                    GoTo Exit_cmdExit_Click
                        End If
' ***  Job Knowledge  ***
        If Me.JobKnowa1 <> True And Me.JobKnowa2 <> True And Me.JobKnowa3 <> True _
            Or Me.JobKnowb1 <> True And Me.JobKnowb2 <> True And Me.JobKnowb3 <> True _
                Or Me.JobKnowc1 <> True And Me.JobKnowc2 <> True And Me.JobKnowc3 <> True _
                    Or Me.JobKnowd1 <> True And Me.JobKnowd2 <> True And Me.JobKnowd3 <> True Then
         MsgBox "Job Knowledge is Incomplete"
            Me.Job_Knowledge.SetFocus
                GoTo Highlight_Incomplete_Items
                    GoTo Exit_cmdExit_Click
                        End If
' ***  Judgment and Responsibility  ***
        If Me.Judgmenta1 <> True And Me.Judgmenta2 <> True And Me.Judgmenta3 <> True _
            Or Me.Judgmentb1 <> True And Me.Judgmentb2 <> True And Me.Judgmentb3 <> True _
                Or Me.Judgmentc1 <> True And Me.Judgmentc2 <> True And Me.Judgmentc3 <> True _
                    Or Me.Judgmentd1 <> True And Me.Judgmentd2 <> True And Me.Judgmentd3 <> True _
                        Or Me.Judgmente1 <> True And Me.Judgmente2 <> True And Me.Judgmente3 <> True _
                            Or Me.Judgmentf1 <> True And Me.Judgmentf2 <> True And Me.Judgmentf3 <> True Then
        MsgBox "Judgment and Responsibility is Incomplete"
            Me.Judgment_and_Responsibility.SetFocus
                GoTo Highlight_Incomplete_Items
                    GoTo Exit_cmdExit_Click
                        End If
' ***  Dependability  ***
        If Me.Dependa1 <> True And Me.Dependa2 <> True And Me.Dependa3 <> True _
            Or Me.Dependb1 <> True And Me.Dependb2 <> True And Me.Dependb3 <> True _
                Or Me.Dependc1 <> True And Me.Dependc2 <> True And Me.Dependc3 <> True _
                    Or Me.Dependd1 <> True And Me.Dependd2 <> True And Me.Dependd3 <> True Then
        MsgBox "Dependability is Incomplete"
            Me.Dependability.SetFocus
                GoTo Highlight_Incomplete_Items
                    GoTo Exit_cmdExit_Click
                        End If
' ***  Teamwork and Cooperation  ***
        If Me.Teamworka1 <> True And Me.Teamworka2 <> True And Me.Teamworka3 <> True _
            Or Me.Teamworkb1 <> True And Me.Teamworkb2 <> True And Me.Teamworkb3 <> True Then
        MsgBox "Teamwork and Cooperation is Incomplete"
            Me.Teamwork_and_Cooperation.SetFocus
                GoTo Highlight_Incomplete_Items
                    GoTo Exit_cmdExit_Click
                        End If
          Case vbNo
            Me.Completed = False
                GoTo CloseNoSave_cmdExit_Click
                End Select
                    End If
' ***  All the Text Boxes  ***
    ' ***  Job Performance  ***
      If IsNull(Me.txtJobPerf) Then
            Me.txtJobPerf.SetFocus
        Select Case MsgBox( _
          "Under Job Performance, the 'provide example of excellence ' or 'suggest ways to improve' is an optional field.  Did you want to add a comment?" _
            & vbCr & vbCr _
            & "To add a comment, click YES" _
            & vbCr & vbCr _
            & "If you don't want to add a comment, click NO", _
            vbYesNo, "Instructions...")
                Case vbYes
                    Me.txtJobPerf.SetFocus
                    GoTo Exit_cmdExit_Click
                Case vbNo
                    Dim intCopyNum As Variant
                    intCopyNum = "No Comment"
                    Me.txtJobPerf = intCopyNum
                End Select
            End If
    ' ***  Job Knowledge  ***
      If IsNull(Me.txtJobKnow) Then
            Me.txtJobKnow.SetFocus
        Select Case MsgBox( _
          "Under Job Knowledge, the 'provide example of excellence ' or 'suggest ways to improve' is an optional field.  Did you want to add a comment?" _
            & vbCr & vbCr _
            & "To add a comment, click YES" _
            & vbCr & vbCr _
            & "If you don't want to add a comment, click NO", _
            vbYesNo, "Instructions...")
                Case vbYes
                    Me.txtJobKnow.SetFocus
                    GoTo Exit_cmdExit_Click
                Case vbNo
                    Dim intCopyNum1 As Variant
                    intCopyNum1 = "No Comment"
                    Me.txtJobKnow = intCopyNum1
                End Select
            End If
    ' ***  Judgment and Responsibility  ***
      If IsNull(Me.txtJudgment) Then
            Me.txtJudgment.SetFocus
        Select Case MsgBox( _
          "Under Judgment and Responsibility, the 'provide example of excellence ' or 'suggest ways to improve' is an optional field.  Did you want to add a comment?" _
            & vbCr & vbCr _
            & "To add a comment, click YES" _
            & vbCr & vbCr _
            & "If you don't want to add a comment, click NO", _
            vbYesNo, "Instructions...")
                Case vbYes
                    Me.txtJudgment.SetFocus
                    GoTo Exit_cmdExit_Click
                Case vbNo
                    Dim intCopyNum2 As Variant
                    intCopyNum2 = "No Comment"
                    Me.txtJudgment = intCopyNum2
                End Select
            End If
    ' ***  Dependability  ***
      If IsNull(Me.txtDepend) Then
            Me.txtDepend.SetFocus
        Select Case MsgBox( _
          "Under Dependability, the 'provide example of excellence ' or 'suggest ways to improve' is an optional field.  Did you want to add a comment?" _
            & vbCr & vbCr _
            & "To add a comment, click YES" _
            & vbCr & vbCr _
            & "If you don't want to add a comment, click NO", _
            vbYesNo, "Instructions...")
                Case vbYes
                    Me.txtDepend.SetFocus
                    GoTo Exit_cmdExit_Click
                Case vbNo
                    Dim intCopyNum3 As Variant
                    intCopyNum3 = "No Comment"
                    Me.txtDepend = intCopyNum3
                End Select
            End If
    ' ***  Teamwork and Cooperation  ***
      If IsNull(Me.txtTeamwork) Then
            Me.txtTeamwork.SetFocus
        Select Case MsgBox( _
          "Under Teamwork and Cooperation, the 'provide example of excellence ' or 'suggest ways to improve' is an optional field.  Did you want to add a comment?" _
            & vbCr & vbCr _
            & "To add a comment, click YES" _
            & vbCr & vbCr _
            & "If you don't want to add a comment, click NO", _
            vbYesNo, "Instructions...")
                Case vbYes
                    Me.txtTeamwork.SetFocus
                    GoTo Exit_cmdExit_Click
                Case vbNo
                    Dim intCopyNum4 As Variant
                    intCopyNum4 = "No Comment"
                    Me.txtTeamwork = intCopyNum4
                End Select
            End If
    ' ***  Outstanding Achievements and/or Areas of Developement  ***
      If IsNull(Me.txtOutstanding) Then
            Me.txtOutstanding.SetFocus
        Select Case MsgBox( _
          "Under Outstanding Achievements and/or Areas of Developement, the 'provide example of excellence ' or 'suggest ways to improve' is an optional field.  Did you want to add a comment?" _
            & vbCr & vbCr _
            & "To add a comment, click YES" _
            & vbCr & vbCr _
            & "If you don't want to add a comment, click NO", _
            vbYesNo, "Instructions...")
                Case vbYes
                    Me.txtOutstanding.SetFocus
                    GoTo Exit_cmdExit_Click
                Case vbNo
                    Dim intCopyNum5 As Variant
                    intCopyNum5 = "No Comment"
                    Me.txtOutstanding = intCopyNum5
                End Select
            End If
Close_cmdExit_Click:
On Error GoTo Err_cmdExit_Click:
      MsgBox "This record is complete and has been saved."
    DoCmd.Close
    GoTo Exit_cmdExit_Click
CloseNoSave_cmdExit_Click:
    DoCmd.Close
Highlight_Incomplete_Items:
        If Me.JobPerfa1 <> True And Me.JobPerfa2 <> True And Me.JobPerfa3 <> True Then
            Me.lbl1a.ForeColor = 255
                End If
        If Me.JobPerfb1 <> True And Me.JobPerfb2 <> True And Me.JobPerfb3 <> True Then
            Me.lbl1b.ForeColor = 255
                End If
        If Me.JobPerfc1 <> True And Me.JobPerfc2 <> True And Me.JobPerfc3 <> True Then
            Me.lbl1c.ForeColor = 255
                End If
        If Me.JobPerfd1 <> True And Me.JobPerfd2 <> True And Me.JobPerfd3 <> True Then
            Me.lbl1d.ForeColor = 255
                End If
        If IsNull(Me.txtJobPerf) Then
            Me.lbl1txt.ForeColor = 255
                End If
        If Me.JobKnowa1 <> True And Me.JobKnowa2 <> True And Me.JobKnowa3 <> True Then
            Me.lbl2a.ForeColor = 255
                End If
        If Me.JobKnowb1 <> True And Me.JobKnowb2 <> True And Me.JobKnowb3 <> True Then
            Me.lbl2b.ForeColor = 255
                End If
        If Me.JobKnowc1 <> True And Me.JobKnowc2 <> True And Me.JobKnowc3 <> True Then
            Me.lbl2c.ForeColor = 255
                End If
        If Me.JobKnowd1 <> True And Me.JobKnowd2 <> True And Me.JobKnowd3 <> True Then
            Me.lbl2d.ForeColor = 255
                End If
        If IsNull(Me.txtJobKnow) Then
            Me.lbl2txt.ForeColor = 255
                End If
        If Me.Judgmenta1 <> True And Me.Judgmenta2 <> True And Me.Judgmenta3 <> True Then
            Me.lbl3a.ForeColor = 255
                End If
        If Me.Judgmentb1 <> True And Me.Judgmentb2 <> True And Me.Judgmentb3 <> True Then
            Me.lbl3b.ForeColor = 255
                End If
        If Me.Judgmentc1 <> True And Me.Judgmentc2 <> True And Me.Judgmentc3 <> True Then
            Me.lbl3c.ForeColor = 255
                End If
        If Me.Judgmentd1 <> True And Me.Judgmentd2 <> True And Me.Judgmentd3 <> True Then
            Me.lbl3d.ForeColor = 255
                End If
        If Me.Judgmente1 <> True And Me.Judgmente2 <> True And Me.Judgmente3 <> True Then
            Me.lbl3e.ForeColor = 255
                End If
        If Me.Judgmentf1 <> True And Me.Judgmentf2 <> True And Me.Judgmentf3 <> True Then
            Me.lbl3f.ForeColor = 255
                End If
        If IsNull(Me.txtJudgment) Then
            Me.lbl3txt.ForeColor = 255
                End If
        If Me.Dependa1 <> True And Me.Dependa2 <> True And Me.Dependa3 <> True Then
            Me.lbl4a.ForeColor = 255
                End If
        If Me.Dependb1 <> True And Me.Dependb2 <> True And Me.Dependb3 <> True Then
            Me.lbl4b.ForeColor = 255
                End If
        If Me.Dependc1 <> True And Me.Dependc2 <> True And Me.Dependc3 <> True Then
            Me.lbl4c.ForeColor = 255
                End If
        If Me.Dependd1 <> True And Me.Dependd2 <> True And Me.Dependd3 <> True Then
            Me.lbl4d.ForeColor = 255
                End If
        If IsNull(Me.txtDepend) Then
            Me.lbl4txt.ForeColor = 255
                End If
        If Me.Teamworka1 <> True And Me.Teamworka2 <> True And Me.Teamworka3 <> True Then
            Me.lbl5a.ForeColor = 255
                End If
        If Me.Teamworkb1 <> True And Me.Teamworkb2 <> True And Me.Teamworkb3 <> True Then
            Me.lbl5b.ForeColor = 255
                End If
        If IsNull(Me.txtTeamwork) Then
            Me.lbl5txt.ForeColor = 255
                End If
        If IsNull(Me.txtOutstanding) Then
            Me.lbl6txt.ForeColor = 255
                End If
Exit_cmdExit_Click:
    Exit Sub
Err_cmdExit_Click:
    MsgBox Err.Description
    Resume Exit_cmdExit_Click
End Sub

Can anyone help me?

Thanks in advance!
SOLUTION
Avatar of clarkscott
clarkscott
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If your controls are bound to fields, have you thought of putting the validation into the field definitions?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You might end up with a very long Select Case statement, but it is still easier to work with than all those If...Then statements.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Senniger1
Senniger1

ASKER

Helen Feddema,

Could you use your Select Case statement example with some of my data to help me follow what you're doing.

Basically I have a form with several questions.  Each question has three answers (e.g. a, b or c).  When I click the command button, it's checking to confirm for each question that either a, b or c is selected.
   Question 1
      Choose one:
         A   B   C
   Question 2
      Choose one:
         A   B   C
Thanks in advance.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How about posting a sample database?
Unfortunately my boss doesn't want a dropdown, he specified checkboxes.

I've attached a sample database.
Sample.mdb
Well if that much logic is required, then in reality, all that can be done is to change the interface (as GlobaLevel suggested)
Again, my boss wants checkboxes, not dropdowns.  I originally created dropdowns and was asked to change it to checkboxes.  So I can't use the suggestion from GlobalLevel for this particular situation.

At this point if anyone can use just a piece of my data to show me a select statement, that would get me started.

Thanks so much!
Have you tried running your code above, I am quite sure you will have performance issues, as you probably have not posted all of your code.

Im not in your office, however You as the it expert, are to bring experience to your boss so well informed decisions can be made.

That aside if checkboxes are still desired, then you can create a wizard situation where if the user selects these checkboxes then a panel will appear to them.  So on your form or forms(you may need to add more) will you will have a series of panels That will display to the user per which checkbox they select...the other panels with controls remain invisible still this requires you to consolidate your GUI design. Does this help? Using the visible/invisible panel approach will allow you to pass your session variables on until they can be further refine to execute the next process.
Although the feedback was appreciated, none of them actually answered my exact question.