Check Box = Value

[the_wildcat]

Okay so I am a newbie at this.. so please be patient.

I am working on a new database for Quality Control. basically, there are a series of Yes/No questions and the customer service rep gets points for each Yes. then they are totaled (for each section) and we get a precentage.

What I need to know is, if the QC Coordinator checks a checkbox (Yes), how do I get it to fill in a value to be totalled? I am assuming that I need to create a variable for each of these "questions' and then if.. then.. else them?

yours
Mike

 

[charityg]

Are the questions equally weighted? What I mean, is question 2 worth the same amount as all of the other questions?

 

[the_wildcat]

Please be patient, since I have never used Access before and have taught myself so far.

In the VBA code I have

Dim intSvcProc As Integer
Dim intCallMgt As Integer
Dim intSvcDelv As Integer

(This is right after Option Compare Database)

Then for each OnClick, there is a sub that adds the amount to each variable.

i.e.:

Private Sub Caring_Responses_Click()
intSvcDelv = intSvcDelv + 25

End Sub

Now I need those variable totals to show in the three totals boxes. But I don't know how to get them to show to verify they are working. I would like to have them update whenever the check box is marked.

 

[charityg]

I think you were trying to head in the right direction, but I see a flaw in the design. You are only adding the value when the box is clicked. What happens when the users "unclicks" the box? It will still add 25 to your variable. I believe you should add hidden textboxes to store the values of each of the check boxes. (Just add a textbox beside the check box and set it's visible property to no). Name it txtCaring_Responses. Set the default value to 0. Then in the onClick event of the checkbox
Private Sub Caring_Responses_Click()
if Caring_Responses=-1 then
txtCaring_Responses=25
else
txtCaring_Responses=0
End Sub

For the datasource of the textboxes that you want to display to totals use:
=[txtCaring_Responses] + [txtOtherCheckbox] +...
where txtOtherCheckbox is the name of another textbox corresponding to a checkbox. Just add all of the values for all of the hidden textboxes. The last thing you may have to do is requery the total boxes in each checkbox click event, but see how far you can get. I know my explanation is probably less than crystal, so let me know if you need an example.

 

[The_Doc_Man]

Well, to do this scrupulously right...

You need three tables:

1. ThePerson - all data about the person who fills in the box.

2. TheQuestions - the complete list of questions to be asked

3. TheAnswers - where the person and question number are linked to ThePerson and TheQuestions by an autonumber primary key and the answer in TheCheckBox is Yes or No (or you can have some other code if Yes/No is too limiting.)

OK, now the number of YES answers is

Select Count(TheAnswers) where TheAnswer!PersID = {the person's ID number} and TheCheckBox = True.

If they have to do this often, include a date field in TheAnswers so you can group by dates or select the correct date or display it or whatever you need to do with it.

If you want to do the questions on-line, it is a matter of a subform that links TheAnswers to TheQuestions so you can record what was answered.

 

[the_wildcat]

Charityg... so far this is what I got

Here's the code from one of my checkboxes

Private Sub Validated___Changed_Address___Phone___PCP_Click()
If Validated___Changed_Address___Phone___PCP = -1 Then
txtValidated_Changed = 15
Else
txtValidated_Changed = 0
End If

End Sub

When I check the box, txtValidated_Changed text box becomes -1, if it is unchecked it remains 0. Is this the way it should work?

And do I requery the total box, or refresh it? should I use DoCmd.Requery?

Thanks for all your help. I truly appreciate it!

Mike

 

[charityg]

It looks like you have the txtValidated_Changed bound to a checkbox field. The textbox should be unbound (not contain a datasource). It will receive its data on the fly from the code.

You should requery the total box like this:

Private Sub Validated___Changed_Address___Phone___PCP_Click()
If Validated___Changed_Address___Phone___PCP = -1 Then
me!txtValidated_Changed = 15
Else
me!txtValidated_Changed = 0
End If

txtMyTotalBox.requery

End Sub

Now I'm trying to figure out how you will handle multiple records, and reassigning the values when you move to another record?

 

[the_wildcat]

Thanks Charity

Well I have a Button to Save Record... couldn't I put something in there that will blank all the fields or set them to 0?

Yours
Mike

 

[charityg]

What I'm wondering, is how you can retrieve all of those values in a previous record. I'm kind of thinking out loud (or silently because I'm just typing)

The more I think about it, the more I realize that a better way to achieve your goal is to just enter the values assigned to the checkboxes as the default values in the corresponding hidden textboxes. And then in your totals box use:
=IIF(chkbox1=-1,txtbox1,0)+IIF(chkbox2=-1,txtbox2,0)+IIF(chkbox3=-1,txtbox3,0)+... and so on.

Which basically translates to If the box is checked, then add the value from the checkbox, else add 0.

 

[the_wildcat]

Hey Charity:

I tried that but it does not update like the other way did.
Something I'm missing?

Yours
Mike

 

[the_wildcat]

Got it Charity - Thanks for the help!!!

Now to just create the reports and the Querys and this will be done.

Right on schedule too... my roomie said he needs it for tomorrow

You are a godsend!!

 

[siculster]

I know this is an old query, but where would I put the IF statement from charityg?

 

[charityg]

In the datasource property for the totals textbox.

 

[siculster]

but what if I want to use this textbox as a value because it will still appear as zero in the table??