checkbox problem

[Seasider]

I'd really appreciate some help. I am just learning VBA on my own.

I have a form with 3 checkboxes: ActiveStatus, Newsletter, and AnnualReport. I have placed code in the On Click event of the ActiveStatus control to prevent the user from CHECKING either the Newsletter or AnnualReport unless ActiveStatus is already checked. Conversely, if Newsletter or AnnualReport are checked, then ActiveStatus cannot be UNCHECKED. This all works fine.

I also have a subform to the right of these check boxes that should display the COUNT for each checkbox. That is, it shows the total of how many checks there are in the table for each checkbox.

The problem is this - unless at least one of each checkbox is CHECKED in the TABLE (not an individual record) the subform will not display the counts.

I suspect the problem may be in the following code I placed in the ON CLICK event of the ActiveStatus checkbox to prevent the user from unchecking ActiveStatus if one of the other checkboxes is checked:

Private Sub chkActiveStatus_Click()
If Me!Newsletter = -1 Or Me!AnnualReport = -1 then
Me!ActiveStatus = 1
MsgBox ("Other options must be unchecked first") vbInformation, "Checking Status"
Endif
End Sub

Sorry for such a lengthy explanation but I don't know any other way to present the problem.

Thanks

 

[WayneRyan]

Seasider,

You don't say how you derive your count.
I'm thinking that you could use the Nz
function here.

Need more info.

Wayne

 

[Seasider]

Further explanation

Thanks for responding Wayne.

I have 3 separate queries to count the totals for each of the checkboxes - cntActive, cntNewsletter, cntAnnualRpt

The SQL for these is similar. Here is the SQL for the cntActive one:
Select count(contacts.ActiveStatus) as cntActive, Contacts.ActiveStatus
From contacts
Group By Contacts.ActiveStatus
Having (((Contacts.ActiveStatus)=-1));

Then I have another query, CountForMailings, that is based on all 3 of the above queries. The subform, CountMailings, is based on this query.

This all seems like a cumbersome way to get the total counts. Is there a better way?

Thanks for your assistance.

Seasider

 

[WayneRyan]

Seasider,

Your queries are the most efficient method,
but there is always the chance that they
will return null.

A less efficient method would be to use
the DCount function:

Total1 = Nz(DCount(...), 0) which would
return a zero when there are no active
ones.

Wayne

 

[Seasider]

Problem solved

Thanks for your help Wayne. I used a modified version of your idea about using DCount and everything is working great now.

Really appreciate your help as well as all that I have learned from reading other discussions in these forums.

Seasider