suppress null checkboxes on report

[badinjector]

Is there any way to suppress checkboxes that are null (or zero) on a report along with their associated labels...and do it so the dead space is removed (i.e. canshrink)

 

[snayjay]

Throw in this code to clear the checkbox from view... but the deadspace won't be removed.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is CheckBox Then
            With ctrl
                .Visible = False
            End With
        End If
    Next ctrl
End Sub

I don't think you can remove the deadspace except to do so before report generation. Meaning to validate the checkboxes contents prior to the report being viewed and restrict those records with nullboxes from the report criteria. A WHERE CLAUSE on the reports control source maybe. But then that removes your whole record and not just the empty checkbox. Can you give alittle more info on what your report is pulling?

~Snay

 

[dhookom]

I have a tendency to think a question like this suggests an un-normalized table structure. I would probably attempt to normalize the yes/no fields using a union query. Then use a multi-column subreport to display the Yes values only.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[badinjector]

Thanks for the replies. I will check out your code snayjay. Sorry, i'm a bit new to access. And yesdhookom you are right. It is an un-normalized database i have had thrown at me to get some data out. Basically, the database consists of three main tables: tblComptencies, tblCourses and tblMatched. The tblCompetencies contains around 1300 core competentcies(objectives) that need to be achieved from the courses in tblCourses. Each course can meet any objective and any objective can be met in any course. I made a new table tblMatched containing primary key from each of the other two tables since each pair of comp-course can only occur once. The original database had another table (tblMatchedOutcomes) that had a autonumber key, a field that contained the keyid from the tblCompetencies which would look up the text for the competency id, and fields for all the courses available !?!. It looks like there was difficulty adding matched outcomes so it was decided to make a new table with checkboxes for the courses which met the competency. Since the data was entered, i thought i could just spit out a report that would show:
Competency A Text............course1,course2 checkboxes but all checkboxes are showing up and i only want those that are true to appear.Unfortunately, the final report is going to a group who wants to see the report looking like their original listing of all comptencies and a space at the end of the line where the course would be listed.To muddy the waters, the courses come in five areas, all with about 1000-1200 different competencies and 12-15 course that must be matched AND there are about 15 people that must enter their own data (assign competency to any of their courses). Sorry to ask but how would i normalize this data (yes/no) from table with checkboxes to the table with the two primary keys?

 

[dhookom]

Without more information about your table structures, let's assume a table of products that you might buy in a store. Products can be categorized by sporting, housewares, apparel, childrens, shoes, hardware,... A single product like batteries can belong to more than one category such as batteries which might be hardware or sporting or childrens. Athletic shoes might be found in shoes, childrens, apparel, or sporting. If the categories are yes/no field names, then you could normalize with a union query like;

SELECT ProductID, "Sporting" as Category
FROM tblProducts
WHERE [Sporting]=true
UNION ALL
SELECT ProductID, "Apparel"
FROM tblProducts
WHERE [Apparel]=true
UNION ALL
SELECT ProductID, "shoes"
FROM tblProducts
WHERE [shoes]=true
UNION ALL
--etc--
WHERE [LastCategory]=true;

You could then use the union query as the record source of a multi-column subreport. Display the "category" field.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]