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.SetFocu s
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_Responsibi lity.SetFo cus
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_Cooperatio n.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!
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.SetFocu
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_Responsibi
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_Cooperatio
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If your controls are bound to fields, have you thought of putting the validation into the field definitions?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How about posting a sample database?
ASKER
Unfortunately my boss doesn't want a dropdown, he specified checkboxes.
I've attached a sample database.
Sample.mdb
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)
ASKER
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!
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.
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.
ASKER
Although the feedback was appreciated, none of them actually answered my exact question.