Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Forcing Total (97)

    I have a table with ten numberic fields (MCCountsA - MCCountsJ). They correspond to how much a multiple choice answer counts. They are integer fields with default values of zero. I would like to force:

    1) No negative values
    2) The total of the ten fields to be 100

    Is there a way to do that at the table or form level? Also, in some tables, I have essay questions mixed in with the multiple choice questions. When this happens, is it possible to only enforce this rule when the type field is equal to either "Multiple Choice" or "True/False"?

    Thanks
    Ronny
    Ronny Richardson

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forcing Total (97)

    I'm sorry but I don't quite understand what you're trying to do. You can set a field's validation rule to be >=0 if you don't want negative numbers or you can use the control's beforeupdate event on the form to make sure it isn't negative, but how would you get a negative value in the field in the first place? I don't understand the second item at all. Are you saying that a multiple choice question can never have a value over 100 or that all multiple choice questions together can only add up to 100?
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Forcing Total (97)

    Only one of them answers can be right. That one should have a value of 100 while all the others have values of 0. I have questions entered by student assistants so I have to use more error checking than if I just entered them all myself. A perfect verification rule would only allow 0 or 100 and also would only allow one field to have a value of 100. I did not figure that was possible so I figured requiring a total of 100 across all the fields was the next best thing.

    Ronny
    Ronny Richardson

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Total (97)

    Ronny

    You can set the fields Validation rule to "0 or 100" which will only allow those numbers and the fields default value to 0 then they will only have to alter 1 field. You can check the total value for the questions in the BeforeUpdate event of the form to see that you have the correct total value.

    You might like to check out <!post=This thread,127982 >This thread<!/post> which has disscusion on how to do a survey database with some samples

    HTH


    Peter

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forcing Total (97)

    What you could do is only allow 0 or 100 as Peter says, then in the AfterUpdate event of each control set all the other controls to zero if this control is 100, if you can understand what I mean.
    HTH
    Pat

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Forcing Total (97)

    The validation rule for 0 or 100 turned out to be much easier than I would have expected. With your help, I got that working in just a few minutes. Thanks

    Ronny
    Ronny Richardson

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Forcing Total (97)

    I think I understand the verbal description of what you are saying. That is, entering a 100 into MCCountsC (for example) would force MCCountsA, MCCountsB, MCCountsD, and so on to 0. However, I don't have a clue how I would do this. I am assuming that this would use the ON EXIT property of the form? If you can point me in the right direction, I'll try to figure it out. Thanks

    Ronny
    Ronny Richardson

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Total (97)

    In the After Event code of each field, if the value entered was 100, set all the other fields to zero.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Forcing Total (97)

    >In the After Event code of each field, if the value entered was
    >100, set all the other fields to zero.

    The problem is, I don't know how to do this.

    Ronny
    Ronny Richardson

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Total (97)

    For each of your 10 fields, get the properties, and click the ... button by the After Update property.

    Then simply type an IF statement that checks whether the field in question is 100. If it is, set the other 9 fields to 0. Note that you'll need to enter similar code for all 10 fields.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forcing Total (97)

    I said the AfterUpdate event of each control, eg:

    Private Sub McCountsA_AfterUpdate()
    SetAllToZero ("A")
    End Sub

    Private Sub SetAllToZero(Exception As String)
    Dim iCount As Integer
    Const Alphabet = "ABCDEFGHIJ"
    Dim iPos As Integer
    iPos = InStr(1, Alphabet, Exception, vbTextCompare)
    If Me("McCounts" & Mid(Alphabet, iPos, 1)) = 100 Then
    For iCount = 1 To 10
    If Exception <> Mid(Alphabet, iCount, 1) Then
    Me("McCounts" & Mid(Alphabet, iCount, 1)) = 0
    End If
    Next iCount
    End If
    End Sub


    Private Sub McCountsB_AfterUpdate()
    SetAllToZero ("B")
    End Sub

    All you need to do is to setup the AfterUpdate events for "C" thru "J".

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Forcing Total (97)

    I am trying to make this work and I'm close but not there. Not being an Access programmer, I understood the loop you were using but figured it would be easier to avoid it and just write the code for each entry manually. The first problem I ran into was that the line

    Me(MCCountsB) = 0

    did not work. When I changed it to

    Me!MCCountsB = 0

    it did work. I don't know if I misunderstood the code you posted or if I introduced an error when I tried to eliminate the loop. Anyway, my code for MCCountsA became

    Private Sub CountsA_AfterUpdate()
    If MCCOUNTSA = 100 Then
    Me!MCCOUNTSB = 0
    Me!MCCOUNTSC = 0
    Me!MCCOUNTSD = 0
    Me!MCCOUNTSE = 0
    Me!MCCOUNTSF = 0
    Me!MCCOUNTSG = 0
    Me!MCCOUNTSH = 0
    Me!MCCOUNTSI = 0
    Me!MCCOUNTSJ = 0
    End If
    End Sub

    This works but it does not immediately change the values of the other variables. Instead, it only changes them if I tab to them, e.g. they get focus. Can it be made to immedlately work. If so, then I can then just cut and paste to the other 9 variables and then change the appropriate line to reset the other nine.

    Thanks
    Ronny
    Ronny Richardson

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forcing Total (97)

    The loop was written to save on the aount of code required.

    There was not a line
    Me(MCCountsB) = 0
    but
    Me("MCCountsB") = 0
    You see the line can be shown as Me!MCCountsB (as you have found) or Me("MCCountsB"), the latter lends itself to generating code on the fly.

    The whole point of using this line is so you can code it simply in a function and save a great deal of code.

    What you are doing will work, but it takes a great deal more code.

    Your request to make it immediately work (ie. show the others as zero when 100 is entered) would take quite a deal of work and frankly, in my opinion, would not be worth the effort.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Total (97)

    As far as I can see they should update automaticaly as soon as the AfterUpdate event fires!

    Peter

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Forcing Total (97)

    Peter,
    You are totally correct.

    However Ronny asked why he had to hit the Enter key (or Tab key) before showing the effect of keying 100 in a field.
    What he fails to realise is that, the BeforeUpdate and AfterUpdate events fire up after one hits the Enter or Tab key, and the fact that something has been entered in the field.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •