1. ## 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

2. ## 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?

3. ## 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

4. ## 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. ## 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. ## 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

7. ## 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

8. ## 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. ## 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

10. ## 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. ## 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. ## 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

13. ## 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. ## Re: Forcing Total (97)

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

Peter

15. ## 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 Last

#### Posting Permissions

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