A Detail/Explanation of form help need new number validation code or new location for code

[GinaFlan]

PAISDDistrictScorecardsupdatedOct16th.zipI need help with validation code for goals for a form that are numeric, yes/no, and percentages. I need either different code or need to know another part of the form I should place the code.


I also attached the file if you delete the following validation code under cmdAdd_Click() you will see the form works perfect

[If IsNumeric(Me.TxtActualBenchmark) = False Then
Select Case Me.ComboGoals.Column(2)
Case 1, 4, 6, 10, 11, 12, 13, 22, 23, 24, 25, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 46, 47, 48, 50, 51, 56, 59, 61, 74, 75, 79, 80, 84, 85, 86, 87, 88, 89, 90, 91, 96, 99, 100, 108, 110, 112, 115, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 142, 143, 144, 145, 146, 147, 148, 149, 152, 153, 154, 156, 158, 159, 160, 161, 162, 163, 164, 165, 166, 171, 172, 173, 174, 184, 185, 186, 187, 188, 190, 191, 192, 193, 195, 196, 197, 200, 204, 205, 208, 209, 210, 211, 213, 214, 216, 219
Me.TxtActualBenchmark.Undo
MsgBox "Please enter a numeric value ", _
vbInformation, "Missing Information"
End Select
Exit Sub
End If
If InStr(Me.TxtActualBenchmark, ".") = 0 Then
Select Case Me.ComboGoals.Column(2)
Case 2, 3, 9, 20, 43, 45, 54, 55, 58, 60, 97, 103, 116, 117, 150, 167, 168, 169, 182, 194, 199, 201, 207
MsgBox "Please enter a decimal", vbInformation, "Missing Information"
Cancel = True
End Select
Exit Sub
End If]





My form is call Scorecard. I have 243 goals that are in a table named Geans that populate the combo box in in my form. My combo box has 3 columns the goals/benchmarks/autonumber I created a table called ScoreCard for the data entered into to the form to go.

The employees will enter their department results in a textbox named TxtActualBenchmark.


Numeric answers are expected for approximately 60 of the goals so far

Yes/No answers are expected for approximately 55 of the goals so far

Decimal answers for percentages are expected for approximately 20 of the goals so far


I created a primary key/auto number for the goals to keep the coding simple.

I also created a pop up combo box for the Yes/No questions called Comboyesorno. I coded those results to go into TxtActualBenchmark on change,because I need all of the department results in one column in the table.

This code makes my yes/no box appear:

[Private Sub ComboGoals_AfterUpdate()
Select Case Me.ComboGoals.Column(2)
Case 7, 14, 15, 16, 17, 18, 19, 21, 33, 39, 40, 41, 42, 44, 48, 49, 53, 57, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 76, 77, 78, 81, 82, 83, 92, 93, 94, 95, 98, 101, 102, 104, 105, 106, 107, 109, 113, 140, 212, 215, 217, 218, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 240, 241, 242, 243
Me.Comboyesorno.Visible = True
Me.TxtActualBenchmark.Visible = False
Me.Label32.Visible = False
Me.TxtActualBenchmark.Enabled = False
Case Else
Me.Comboyesorno.Visible = False
Me.TxtActualBenchmark.Visible = True
Me.Label32.Visible = True
Me.TxtActualBenchmark.Enabled = True


End Select
End Sub]

This code throws the answer to the yes/no question into TxtActualBenchmark.

[Private Sub Comboyesorno_Change()
Me.TxtActualBenchmark.Value = Me.Comboyesorno.Column(0)
End Sub]


If I code the long way for each of the numeric goals my form works. When I say my form works. This means my "Yes/No" questions work and are "added" to the table per the cmdAdd button.

[If IsNumeric(Me.TxtActualBenchmark) = False _
And txtBenchmarks.Value = "1 promotion per month" Then
Me.TxtActualBenchmark.Undo
MsgBox "Please enter a numeric value ", _
vbInformation, "Missing Information"
Exit Sub


If I try to make the code shorter with a case select statement, then my yes/no answers cannot be added, I do not get an error but nothing happens.


[If IsNumeric(Me.TxtActualBenchmark) = False Then
Select Case Me.ComboGoals.Column(2)
Case 1, 4, 6, 10, 11, 12, 13, 22, 23, 24, 25, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 46, 47, 48, 50, 51, 56, 59, 61, 74, 75, 79, 80, 84, 85, 86, 87, 88, 89, 90, 91, 96, 99, 100, 108, 110, 112, 115, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 142, 143, 144, 145, 146, 147, 148, 149, 152, 153, 154, 156, 158, 159, 160, 161, 162, 163, 164, 165, 166, 171, 172, 173, 174, 184, 185, 186, 187, 188, 190, 191, 192, 193, 195, 196, 197, 200, 204, 205, 208, 209, 210, 211, 213, 214, 216, 219
Me.TxtActualBenchmark.Undo
MsgBox "Please enter a numeric value ", _
vbInformation, "Missing Information"
End Select
Exit Sub
End If]


Here is some more information. I also have the employees results coded to have "met goal" or "did not meet goal" I coded this to happen
[Comboyesorno_AfterUpdate()] after update for yes/no questions and after update [TxtActualBenchmark_AfterUpdate()] but I had used me.combogoals.column(2) for the numbers to work.

[Private Sub TxtActualBenchmark_AfterUpdate()
'Zero or less than 1
Select Case Me.TxtActualBenchmark
Case Is <= 0
Select Case Me.ComboGoals.Column(2)
Case 8, 34, 36, 37, 38, 74, 85, 86, 112, 129, 134, 135, 143, 144, 145, 146, 147, 148, 150, 153, 163, 184, 185, 188
Me.txtMetGoal = "Met Goal"
End Select
End Select]

I need help on the best shortest way to make all of this happen. I appreciate everyone's help. I would have no one to communicate or understand without you guys.
I did have the idea of making another text box appear for numeric answers, I thought that may be faster.

[hansendl]

There is no file attached to your post...

[GinaFlan]

Ok now it is attached Thank you

[GinaFlan]

I solved it.