Hopefully here is something that will assist you in accomplishing the results
you want. The over all concept is to create a user defined function that
will calculate the "score" based on the options selected. You will need code
to accomplish this.
I have taken a more simple approach to this. There are more involved
methods for doing this, but since you said that you do not have much
experience, this may be easier for you to understand.
First, with you form open in design view, display the properties of your
form. Select the "Event" tab. The "On Current" event will be the first
event listed. Place your cursor in the text box for this event then click
the down arrorw box at the end of the row and select "[Event Procedure]" from
the list. Next, click the button with the three dots (to the right of the
down arrow).
You will be taken to the VBA window and your cursor should be in the Current
event of the form:
Private Sub Form_Current()
End Sub
Make sure that the following two lines are at the very top of this screen:
Option Compare Database
Option Explicit
Just below these lines copy and place the following code:
'start of code below this line
Function CalcScore()
Dim lngCurScore As Long
Dim lngItem1Val As Byte
Dim lngItem2Val As Byte
Dim lngItem3Val As Byte
Dim lngItem4Val As Byte
'set a value for the first check box
If Me.chkbxItem1 = True Then
lngItem1Val = 5
Else
lngItem1Val = 0
End If
'set a value for the next check box
If Me.chkbxItem2 = True Then
lngItem2Val = 4
Else
lngItem2Val = 0
End If
'set a value for the next check box
If Me.chkbxItem3 = True Then
lngItem3Val = 2
Else
lngItem3Val = 0
End If
'set a value for the next check box
If Me.chkbxItem4 = True Then
lngItem4Val = 3
Else
lngItem4Val = 0
End If
Me.txtScore = lngItem1Val + lngItem2Val + lngItem3Val + lngItem4Val
End Function
'end of code to copy
In this code I have only placed code for 4 check boxes. However, you can
add code for additional check boxes by simply copying the "If ..." statement
for each check box and renaming the item to reflect the name of your
controls. You will also need to declare a variable (Dim lngItem4Val As Byte)
and changing the number in the variable name to reflect your item name. You
will also need to add the variable to the statement where all of the other
variables are being added together to produce the total.
Now make the following changes in the code above:
Change "chkbxItem1" to the actual name of your first check box.
Change "chkbxItem2" to the actual name of your second check box.
Change "chkbxItem3" to the actual name of your third check box.
Change "chkbxItem4" to the actual name of your fourth check box.
Change the "txtScore" to the actual name of the text box on your form where
the score is to be displayed.
Now, go back to your form. Select your first check box. In the Properties
window, locate the "After Update" event. Place your cursor in the text box
for this event then click the down arrorw box at the end of the row and
select "[Event Procedure]" from the list. Next, click the button with the
three dots (to the right of the down arrow).
You will be taken to the VBA window and your cursor should be in the After
Update event of the first Checkbox (in this example my first check box is
named "chkbxItem1") :
Private Sub chkbxItem1_AfterUpdate()
End Sub
Now place the name of the user defined function in the Sub routing for the
Current event of the form which you create when you started to create the
code above.
Private Sub Form_Current()
CalcScore
End Sub
Place the name of the user defined function in this sub routine:
Private Sub chkbxItem1_AfterUpdate()
CalcScore
End Sub
Now repeat this for each of the check boxes that you have created code for.
You should have an "If" statement for each check box that you want to
evaluate and you should have code for the AfterUpdate event for each of the
check boxes.
You will now need to look at each of the "If ..." statements and change the
value that is assigned to the variable for each check box control based on
the options selected. Looking at the first "If..." statement:
'set a value for the first check box
If Me.chkbxItem1 = True Then
'if the checkbox is true give the score 5 points
lngItem1Val = 5
Else
'if the checkbox is false add nothing to the score
lngItem1Val = 0
End If
The next line added all of the values together to create the total score:
Me.txtScore = lngItem1Val + lngItem2Val + lngItem3Val + lngItem4Val
The "txtScore" is just the name of the text box where the score is to be
dislayed. To the right of the equal sign is each of the variables being
added together to create the score.
I do hope that you will be able to use this to move along with your project.
I you need additional help, just post back here.
--
HTH
Mr B
askdoctoraccess dot com
Beginning Access user here. Had a question that one would think
would
be easy, but I am unable to figure out how to do it.
I have setup my table with fields. I have created a form and have
those fields linked to controls on the form. The form looks the way
I
want it to.
The structure is that of a Quality Assurance form. It's got eight
sections and a number of questions under each section. These
questions are simply checkboxes that are checked if the person being
QA'd did it or left unchecked if not.
I'd like to be able to assign point values to each checkbox question,
such as if the box is checked, it's worth 4 points; if not, it's
worth
0 points.
I'd like the form to add up the points gained in each section, then
the form's point total at the bottom of the form.
It sounds like this should be straightforward, but it probably isn't.
I'm not adverse to scripting or whathave you.
Any input would be good.
Thanks.
