Counting Check boxes

[RichO]

I have a form set up with a subform. In that subform, each record has a check box. The check box designates whether or not to include that record in the report. This causes a blank report to open if no records are checked.

How would I go about bypassing the open report command if there are no records selected?

 

[Mile-O]

Put this code on the relevant form and you can then test it like so:

If CanReportOpen Then
    DoCmd.OpenReport "MyReport"
Else
    MsgBox "No options selected.", vbExclamation
End If

Private Function CanReportOpen() As Boolean
    On Error Goto Err_CanReportOpen
    Dim ctl As Control
    For Each ctl In Me
        If ctl.ControlType = acCheckBox And ctl = True Then
            CanReportOpen = True
            Exit Function
        End If
    Next
Exit_CanReportOpen: 
    Set ctl = Nothing
    Exit Function
Err_CanReportOpen:
    CanReportOpen = False
    Resume Exit_CanReportOpen
End Function

 

[RichO]

Thanks for the code.

Right now I get "no options selected" everytime regardless of the check boxes.

Is the CanReportOpen function supposed to just be placed into the VBA code window or does it need to be called from an event procedure?


"For Each ctl In Me"

When the function is referring to "Me" is that only looking at the main form or the subform as well? There are no check boxes in the main form.

The main form and subform are linked to separate tables that contain hundreds of records in a one to many relationship. Is the code given set up to look only at the current record(s) in the form, because that is what it needs to do.

Thanks for your help.

 

[Mile-O]

When the function is referring to "Me" is that only looking at the main form or the subform as well? There are no check boxes in the main form.

It only looks at the form the code is on.

 

[RichO]

I moved the CanReportOpen function to the VBA code window for the subform. The command button to print the reports is on the main form. This command button prints an overall report and then an additional report based on what boxes are checked.

Since I moved the function code to the subreport, nothing happens at all when I click the reports command button, apparently because it can no longer find the function.

What would be the solution?

 

[ChrisO]

G’day RichO

Option Explicit
Option Compare Text

[color=green]'   This code should be placed on the main form with a sub-form control called
'   frmCustomersSubForm, change as required. It sums a Boolean field called
'   TestCheck in the sub-forms recordset, again change as required.
'
'   It should work OK if the Record Source of the sub-form is a Table or Query but will
'   not work if the Record Source of the sub-form is a SQL statement.
'
'   Here is how it works: -
'
'   A Boolean field is stored as a 16 bit number but should only ever have a value of
'   False(0), XOR True (Not False(-1)) in VBA.
'
'   Therefore we can sum the values of Boolean variables. If the sum of all the fields
'   is zero then no individual variable is True, else the sum is Not False.
'
'   However, a recordset may not be populated and DSum would return a Null under that
'   circumstance. Hence the Nz function is employed to convert any Null to False.
'
'   Therefore, if all Fields are False IOR there are no fields then a valid comparison
'   can be made with False.
'
'   But the reverse is not true. We can not use the result of DSum and reliably compare
'   it to True. If one check box is True then DSum will return -1, if two check boxes
'   are True then DSum will return -2. If we compare the result of DSum(-2) with True(-1)
'   the result of the comparison would be False.
'[/color]
Private Sub cmdTestIt_Click()

    If Nz(DSum("TestCheck", Me.frmCustomersSubForm.Form.RecordsetClone.Name), False) = False Then
        MsgBox "Report can't open."
    Else
        MsgBox "Report can open."
    End If

End Sub

If you believe that was difficult to read then you are not wrong.
If you believe that was easy to write then you are also incorrect.

If anyone is interested, the above domain aggregate function, DSum, takes 10 milliseconds for 5890 records on an 850MHz PIII.

Regards,
Chris.

 

[RichO]

Thanks Chris. I won't be in the office again until Monday but I'll try it out then.

 

[RichO]

I tried Chris' code but I wasn't exactly sure how to do it because the check boxes are in the subform and the report is open from a command button on the main form. This is what I did:

I inserted this into the subform code window:

Private Function CanReportOpen() As Boolean
CanReportOpen = Nz(DSum("chkSchedule", Me.tb_Promo_Artist_Quotes.Form.RecordsetClone.Name), False)
End Function

The name of the subform is "tb_Promo_Artist_quotes" - It wasn't me that named it


And I added this code to the command button:


If CanReportOpen = True Then
DoCmd.OpenReport "Promotion Schedules", acPreview, , "Promo_ID = " & Me.Promo_ID

End If

The command button opens 2 reports, the conditional report being the second one. Now when I click the command button, nothing happens at all. The first report does not even open and the command to open that report precedes this code.

I'm lost...

 

[ChrisO]

RichO

The code should go in your command button click event on the main form.
There is no need for a separate function in the sub-form.

Private Sub YourCommandButton_Click()

    [color=green]'   Open first report here.[/color]

    If Nz(DSum("chkSchedule", Me.tb_Promo_Artist_quotes.Form.RecordsetClone.Name), False) = False Then
        MsgBox "No data for Report."
    Else
        DoCmd.OpenReport "Promotion Schedules", acPreview, , "Promo_ID = " & Me.Promo_ID
    End If

End Sub

Hope that helps.

Regards,
Chris.

 

[RichO]

OK, I tried that but I get a message box...

"The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

What next?

 

[ChrisO]

G’day RichO

Where did the error occur i.e. on what line did the debugger stop?

Regards,
Chris.

 

[ChrisO]

G’day RichO

Because of the time difference we could be here a month of Sundays.

To try to fast track, don’t you just love the lingo, I have included a small attachment that does what I think you want. There are no reports, just the logic for determining if the second will be displayed.

Hope that helps.

Regards,
Chris.

 

[RichO]

Thanks for the sample, I'll try it Thursday when I'm at the office.

Regarding the error, I don't know where it comes from because I'm having problems getting the debugger to work which you will see I have posted a thread on.

Thanks again.

 

[RichO]

Yes, ChrisO, this is truly a head scratcher for me, maybe you as well.

I dropped your code directly into my existing form and I get the same error.

I studied both my form and your form and found that "chkSchedule" on my form was referring to the NAME of the check box whereas on yours "chkSchedule" was the control source, so I changed that. Same error. I also noticed that tb_Promo_Artist_Quotes was the source object on my form whereas it was the NAME on your form, so I changed that as well. Still the same error.

The only other difference that I can find between your form and mine is that the record source of yours is a table and the record source of mine is a query.

This is a strange one.

 

[RichO]

BTW I did run the code debugger and the error occurs on the "If Nz(Dsum..."
line, kind of as we figured.

 

[ChrisO]

G’day RichO

Well the only thing I can suggest at this stage is to make a copy, remove any data and post just the form, sub-form, query and table(s).

PS
A97 please.

Regards,
Chris.

 

[RichO]

This database is in Access 2000. What would I need to do as far as Access 97 goes?

 

[ChrisO]

On the tools / database utilities / menu you will need to convert to previous version.

 

[RichO]

No such luck in an attempt to zip the forms. There are several tables & queries used by this form as well as functions from a VBA module. Well, back to the drawing board.

 

[ChrisO]

No such luck in an attempt to zip the forms. There are several tables & queries used by this form as well as functions from a VBA module. Well, back to the drawing board.

Well RichO if that’s the way you want it then so be it.

It’s all up to you.

Regards,
Chris.