Full Version: Counting check boxes in a sub form?
UtterAccess Forums > Microsoft® Access > Access Forms
James
Hi All,
I have seen a thread on the above subject but can't find it for love nor money. What I am looking to do is find the simplest way to have a single text box count how many of my check boxes are 'checked'. For some reason i can't get it to count them, I've tried everything I know and I'm now stuck!
Any help would be great,
James.
Jack Cowley
Do you have more than one checkbox field per record in your table? In other words does your table look like:
blMyTable
MyTableID (PK and auto)
Checkbox1
Checkbox2
Checkbox3
..etc...
If your table looks like this then your data is not normalized and you need to normalize the structure before you go further with the db.
hth,
Jack
James
Hi Jack,
Thanks for getting back to me. The layout is this:
Pallet No - Individual pallet number - Number
RAN Number - Number of the invoice relating to that pallet - Number
Delivered - Did the pallet get delivered to the company - Yes/No
It is the delivered field that I am trying to count. They are all on a continueus sub form, 1 set of information per detail section.
Does that help?
James
Jack Cowley
Continuous forms present problems as they are just one form repeated many times. You can use DCount() or other code on your main form to display the count of True fields. In an unbound control on the main form:
CODE
=DCount("[Delivered]", "NameOfTable/QuerySubformIsBasedOn", "[PalletNumber] = " & [Forms]![SubformControlName].Form.Delivered)

The above is my best GUESS as to what your code might look like. If you need help with the DCount() syntax then look here. The article is about DLookup() but the syntax is exactly the same.
hth,
Jack
James
Thanks Jack, I'll have a play!
Jack Cowley
I just realized that the code I suggested is incomplete as you want to count Delivered = True:
CODE
=DCount("[Delivered]", "NameOfTable/QuerySubformIsBasedOn", "[PalletNumber] = " & [Forms]![SubformControlName].Form.Delivered & " And [Delivered] = True")

This is just a guess so take it with a huge grain of salt.
Jack
James
I think I am being a bit thick! I cant seem to get it to work.
If I put a text box in the form footer, how do I get it to count the amount of 'ticked' check boxes above it, in the forms detail section, my brain hurts!
James
NoahP
You could just use:
Abs(Sum(NameofCheckBoxControlHere))
that takes the absolute value of the sum of all the 'check boxes' and since a check box value is -1 for true and 0 for false, it should be what you need.
HTH
Noah
James
Nice one Noah, spot on. It now totals the check box's in my sub form. Now for the next little task, I've tried to get another text box on the main form to be controled by the text box on the subform. I tried to simpliy build it to say that it should be equal to the sub form text box but it doesn't like it! Do you know why and how I can make it?
Thanks again,
James
PS Nice link on the bottom of your post Noah, it looks like a good read!
NoahP
Glad it worked for you.
That did you use in the Control Source of the text box on your main form?
Noah
James
I am using:
Forms![Pallets and Rans subform]!Text24
Text24 being the name of the text box, all I get is #NAME?
Am I being a bit dim?
Thanks for you time helping a weak and feeble one! LOL
Jack Cowley
Noah -
Thanks for jumping in with a MUCH simpler solution! If only I were as clever as you-know-who... Now all I have to do is remember your solution and the prospects are not good...
Jack
James
Sorry Jack,
didn't mean to forget about you, just got carried away with trying out Noah's suggestions!
compute.gif
NoahP
Try:
[NameOfSubFormControlHere].Form.[NameOfControlOnSubformHere]
Noah
NoahP
You're welcome Jack! I never mean to step on toes, just want to help, if I can. frown.gif
Jack Cowley
Not a problem!!! I am glad Noah jumped in with a far better solution!!!
ack
Jack Cowley
You never step on my toes as I have very quick feet, considering my age! Actually, it is a big relief when I see that you have jumped in!
ack
James
Hi Noah,
This is what I think it's supposed to look like:
=[pallets_and_rans].Form.[text24]
But its not workinig, I'm still getting "#NAME?"?
The control on the sub form is "Text24" and the sub form is called "Pallets and Rans"
Please could you re-point me in the right direction?
Many thanks,
James
NoahP
When you click on the subform, and then open the Properties window, what is the Name property under the Other tab?
oah
James
Pallets and Rans subform
think I know what your going to say but I'll let you say it first .....
James
Noah,
I've realised what I was doing wrong and I've got it to work but it doesn't seen to be saving the value it creates in my main table? Do you know why?
James
NoahP
You shouldn't BE saving the value. You can and should calculate it whenever it's needed on a form as you're doing or in a report. Storing calculated values is virtually never a good idea.
oah
James
Oh ... Ok, when I am reporting on the calculated figures, how do I do this for multipul records all at once?
Cheers
NoahP
Just like you used the unbound text box in the form footer, you can use it in section footers of a report.
oah
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.