Solved

Count of checkboxes selected in subform

Posted on 2013-12-08
14
611 Views
Last Modified: 2013-12-12
I have a form, FBRedemptionMainForm, with a subform, FBredeamProdQuerySubform, with a click box control, Selected.  I need to have a control in the footer of the main form that will total the records that are selected. Do I use the count function? How do I refer to the selected controls in the expression for the count function?
0
Comment
Question by:PetGuy
  • 8
  • 5
14 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39704867
First, your checkbox should be bound to a Y/N field in the underlying table.

Then you can use the following in a textbox in the subform footer (include the = sign):

= ABS(Sum([YourYesNoFIeld]))

Open in new window


NOTE:  "YourYesNoFIeld" should be the name of the underlying Y/N field, not the name of your checkbox control.
0
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39704877
I would suggest using the "On Click" event handler to test for whether the box is checked or unchecked and then increment or decrement a variable accordingly. The control in the footer can be tied to that variable and thus forced to refresh also in the On Click event.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39704878
If you want to display that in the main form, you can set the Visible property of the textbox I described to False, and refer to it from the mainform (again in a textbox control source) like this:

= SubformControlName!Form!txtTotal
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 61

Expert Comment

by:mbizup
ID: 39704905
Slight correction to the above syntax - I'm mixing up periods and exclamation points.

Again suggesting to create the txtTotalSelected textbox in the subform footer (this is the easiest way to handle any filtering that may be applied in the subform).  The control source of a textbox in the subform footer would be:

= ABS(Sum([YourYesNoFIeld]))

Open in new window



Then to refer to it from the main form:

=[FBredeamProdQuerySubform].[Form]![txtTotalSelected]

Open in new window

0
 

Author Comment

by:PetGuy
ID: 39705142
The text field that does the counting in the footer of the subform is working fine. I am having trouble getting the text field in the footer of the main form to reference the the text control in the sub form.

Main form: FBRedemptionMainForm
Subform: FBRedeamProdQuerySubform
Text field with count on Subform:  BagTotalText

Control source value for text field in main form footer:  =FBRedeamProdQuerySubform!Form!BagTotalTxt

Result in main form footer text field: #Name?

What am I doing wrong?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39705149
>>> Text field with count on Subform:  BagTotalText
>>> FBRedeamProdQuerySubform!Form!BagTotalTxt

Check your spelling on both.  Is it BagTotalText or BagTotalTxt (without the 'e' in text)?
0
 

Author Comment

by:PetGuy
ID: 39705188
BagTotalTxt is correct in both places. My error in the name in the last post
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39705193
Whoops... you didn't see the correction to the main form syntax in my post at http:#a39704905, and I missed it in your last post.


>>> FBRedeamProdQuerySubform!Form!BagTotalTxt!Form!BagTotalTxt

The ! following FBRedeamProdQuerySubform should be a "."

= FBRedeamProdQuerySubform.Form!BagTotalTxt

Open in new window

0
 

Author Comment

by:PetGuy
ID: 39705202
Changed, still getting same result
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39705207
The next thing to check is the subform control name: FBRedeamProdQuerySubform

Open your main form in design view, right-click on the very edge of the subform (the 'frame' that houses your subform), and select properties.

In the property sheet, what is shown for the "Name" property?  If this is different from FBRedeamProdQuerySubform, use the name from the property sheet instead.  (sometimes the 'subform control name' differs from the subform name as seen in the navigation pane)

If that doesn't help, can you post a sample of your database?  Just the relevant forms and tables, with any sensitive data masked or removed.
0
 

Author Comment

by:PetGuy
ID: 39705214
>>>= SubformControlName!Form!txtTotal

by = SubformControlName did you mean 1) the name of the subform or 2) the name of the control on the subform?

It is presently #1
0
 

Author Comment

by:PetGuy
ID: 39705221
that did it, it was FBRedeamProdQuery that changed an it worked.  

Thanks for all your help

wish i could get you 1000 points
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39705226
This is what I'm referring to -- sometimes the Name property of the control that 'houses' your subform is different from the name of the subform itself (which you see in the database window or navigation pane).  It is this 'subform control name' that you need in these these references, not the actual subform name (clear as mud, right? :-) )

Subform control name
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39705228
Oh - I missed your last post.  You've got it working - Excellent :-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question