Solved

Count of checkboxes selected in subform

Posted on 2013-12-08
14
574 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>> 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
Comment Utility
BagTotalTxt is correct in both places. My error in the name in the last post
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
Changed, still getting same result
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
>>>= 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
Comment Utility
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
Comment Utility
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
Comment Utility
Oh - I missed your last post.  You've got it working - Excellent :-)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

772 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

9 Experts available now in Live!

Get 1:1 Help Now