Solved

Count of checkboxes selected in subform

Posted on 2013-12-08
14
589 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:Technodweeb
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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now