Results 1 to 4 of 4
  1. #1
    GinaFlan is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Location
    Texas
    Posts
    68

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

    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.



    Last edited by GinaFlan; 10-16-2014 at 08:15 AM. Reason: Attached the file

  2. #2
    hansendl is offline Advanced Hobbyist
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    38
    There is no file attached to your post...

  3. #3
    GinaFlan is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Location
    Texas
    Posts
    68
    Ok now it is attached Thank you

  4. #4
    GinaFlan is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2014
    Location
    Texas
    Posts
    68
    I solved it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-04-2013, 09:51 AM
  2. Help with Validation code on exit event on a form
    By thebionicredneck2003 in forum Programming
    Replies: 2
    Last Post: 05-17-2013, 06:04 AM
  3. Replies: 4
    Last Post: 05-24-2011, 08:19 AM
  4. Replies: 21
    Last Post: 02-14-2011, 02:51 PM
  5. How to code master detail relation in tabforms
    By AshokS in forum Programming
    Replies: 18
    Last Post: 12-20-2010, 08:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums