Count checkboxs in database 1

[srodolff]

Greetings!

I have a simple access database where each record contains a checkbox field. On a record lookup form, I would like to place a textbox that contains the current count of records with selected checkboxes. I would like to use VBA code for the textbox.

Any simple suggestions?

Thanks.

P.S. If I have a save record button, how can I refresh this value after the save button is pressed?

 

[hneal98]

You can put a textbox on your form and right click on it and base it on a simple query that would count the 1's in your table. The check box is represented by a 1 when it is checked.

 

[RoyVidar]

Hi srodolff!

Think I'll rather present a non vba way.

If it's records currently in your search form, the textbox's controlsource might look like this:

[tt]=Sum(Abs([YourFieldName])[/tt]

If it's all records in the table:

[tt]=Abs(Dsum("[YourFieldName]&quot,"[YourTableName]&quot[/tt]

HTH Roy-Vidar

 

[srodolff]

Is there any reason Dcount wouldn't work?

Something like this:

=DCount(&quot;[Memory Upgrade Complete]&quot;,&quot;Complete Data&quot;,&quot;[Memory Upgrade Complete] <> null&quot

[Memory Upgrade Complete] is a checkbox.

Please correct my syntax if it is wrong.

BTW, what textbox function should it be placed in?

Thanks a lot.

 

[RoyVidar]

Hi!

In the control source property of the text control.

Dcount will work, with a little tweaking of the syntax. Table and field names containing spaces must be bracketed. Testing for <> null here, would give the number of fields not having neither the value True nor False, so in other words, it'll probably count all records, checked or not.

This

[tt]=DCount(&quot;[Memory Upgrade Complete]&quot;,&quot;[Complete Data]&quot;,&quot;[Memory Upgrade Complete] = True&quot[/tt]

or

[tt]=DCount(&quot;[Memory Upgrade Complete]&quot;,&quot;[Complete Data]&quot;,&quot;[Memory Upgrade Complete] = -1&quot[/tt]

should count only the records with chechmark.

But since Yes/No fields kan be read/evaluated as numbers (True = -1, False = 0), preference seems to go towards summing absolute value of those.

HTH Roy-Vidar

 

[srodolff]

Roy-Vidar,
Your suggestion was very helpful. I have one last issue:

When I put the DCount command in the control source, the count is correctly reflected when the form is opened.

I have a button on the form that is called &quot;Save&quot;. This button 1. Clears the form by acNewRec. 2. Blanks out the input field (This field is not part of the table) and 3.Sets the focus to the input field. I would also like this button to refresh the DCount.

How can this be done?

I really appreciate your help with this.

Thanks,
Steve

 

[srodolff]

Roy-Vidar,
I figured it out. Instead of putting DCount in the control source, I put it in Default Value. I then added a Forms! entry to the Save button that contained the DCount statement.

Now the correct total appears when the form is opened and anytime the &quot;Save&quot; button is pressed.

Thank you for your help.

Steve

 

[RoyVidar]

Hi, thanx for the star and the kind words! Happy you solved it. I was now just seconds away before posting another reply, but luckily hit refresh first;-)

Roy-Vidar