Check Box Help

[escoyne]

I need a little help with some check boxes. This is what I want to happen.

I have 5 items that I want to have check boxes for (pistol, revolver, rifle, shotgun, knife).

I need to set a value for each item.
pistol = 10
revoler = 10
rifle = 10
shotgun = 15
knife = 5

Now, if any of the check boxes are checked, i need to take the value of the items checked and add them to together....i.e.
pistol = 10 (checked)
revolver = 10 (checked)
knife = 5 (checked)

total = 25

Then take that total, and rate it against a level.

Total 0-10 == Low risk
Total 11-20 == Moderate risk
Total 21 up == High risk

 

[John Big Booty]

A checked check box has a value of -1, so you could have an unbound Text Box, that has the following code as it's control source;

=(ABS([PistolCheckBox])*10+ABS([RevolverCheckBox])*10+ABS([RifleCheckBox])*10+ABS([ShotgunCheckBox])*15+ABS([KnifeCheckBox])*5

This will give you a score for the check boxes that have been checked. You could then have a second unbound Text Box with the following code;

=IIf([TextBoxName] <= 10, "Low Risk", IIf([TextBoxName] >=11 or [TextBoxName] <= 20, "Moderate risk", "High risk"))

You could of course do this in one Text box, I've used two just for clarity. You could also use code behind the on Click events of the check boxes but this is probably simpler as it requires no coding.

 

[escoyne]

John, this worked, however, without checking any of the boxes, I have "high risk" showing in my text box.

Also, as I check the boxes, I don't have a running total until i check a majority or all the check boxes...

 

[escoyne]

Got it firgured out.....Thanks anyways...

 

[escoyne]

John, I still dont have the running total. Any ideas?

 

[Scooterbug]

Escoyne,
Playing around I came up with this:

Public Function FindRisk()
Dim Pistol As Integer
Dim Revolver As Integer
Dim Rifle As Integer
Dim Shotgun As Integer
Dim Knife As Integer
Dim RiskLevel As Integer
 
Pistol = Me.chkPistol * -10
Revolver = Me.chkRevolver * -10
Rifle = Me.chkRifle * -10
Shotgun = Me.chkShotgun * -15
Knife = Me.chkKnife * -5
RiskLevel = Pistol + Revolver + Rifle + Shotgun + Knife
 
If RiskLevel <= 10 Then
    FindRisk = "Low Risk"
ElseIf RiskLevel >= 11 And RiskLevel <= 20 Then
    FindRisk = "Moderate Risk"
ElseIf RiskLevel >= 21 Then
    FindRisk = "High Risk"
End If
 
End Function

On the after update event of each check box, put in the following code via the VBA editor:

me.textbox = FindRisk

Where textbox is the name of the control that you wish to display the risk level.

 

[John Big Booty]

John, I still dont have the running total. Any ideas?

Try putting a Me.Refresh command in the on click event of each of the Check boxes.

 

[John Big Booty]

Try putting a Me.Refresh command in the on click event of each of the Check boxes.

Scratch that, it should not be required.

Have you got an unbound text box with the first piece of code I proposed?

=(ABS([PistolCheckBox])*10+ABS([RevolverCheckBox])*10+ABS([RifleCheckBox])*10+ABS([ShotgunCheckBox])*15+ABS([KnifeCheckBox])*5

This will give you a total for each record and will update as you check or uncheck, your check boxes.

Have you changed the names in the code to reflect the names of the check boxes on your form?