Solved

Count of checkboxes selected in subform

Posted on 2013-12-08
14
625 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

733 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