Hide Check Boxes that are not checked in Access 2000/2003

[Kim]

Hello,

I am working with 4 tables and 200+ Yes/No check boxes (fields) for
each client (1 row per client in each table). If I have 600 clients
then each of the 4 tables will have 600 records\rows and 50 to 100
fields\columns. I am trying to find a way to make any unchecked check
boxes disappear using either a form or report instead of being
displayed sporadically throughout the page by writing code to hide
these check boxes.

The first table is a ClientMaster table that contains client info only
and is linked to the other tables using a ClientID primary key. The
other three tables consist of different types of reports a client can
receive from us on a daily or monthly basis. So you might have
Company1 receive a report called 100a, 100d, and a 571b report but not
receive a 100b, 100c or 571a which fall between the other fields. We
want to provide the report selection to the client but we do not want
to provide them a complete listing of all the reports we offer that
they have not selected.

Can you provide any possible solutions? I have tried so many different
things to no avail and appreciate any help you can give. Thanks so
much and have a terrific day!

Kim

 

[mnature]

You could use a query that your forms and reports are based on, that filters
out any records where the checkbox is not checked.

 

[Steve Schapel]

Kim,

Are you able to entertain a change to the design of your database? The
simplest solution here would involve putting the selected subscriptions
into a single field in a related table. This will obviously require a
re-working of your forms and reports, but I would say the effort would
be worth it in the end.

 

[Kim]

I actually tried that first by placing <> 0 or = -1 and IIF statements
but unfortunately by the time I got to the end of the query the row
didn't have any fields left because it elimanated all of them. Not
sure if I was filtering them correctly or not.

 

[Kim]

Actually, I can not change the database because this section is a very
small section of the database but I can change queries, forms, reports
and even make a new table using queries from the existing tables. Can
you explain more about what you mean? Some of the fields I will work
with are ClientID, LPT100a, LPT100b, LPT100c, RPT571a, RPT571b, etc. if
this helps. Thanks for responding back so quickly.

Kim

 

[mnature]

Since a checkbox is a boolean, you should be able to just put "yes" or "no"
for a particular checkbox criteria, in the select query.

On re-reading your original posting, I can see your problem. There should
be one table for clients, and one table for reports. Then you should have a
table that combines particular clients with particular reports. This would
then be the basis for a query that can be used to print out reports that are
specific for each client. If a client requires another report, then you
place that in the combination table, which would link the primary key of a
client to a primary key of a report.

From your comment to Steve, it sounds as if you are the inheritor of a
database that might not be optimized. Creating some new tables to bypass the
irregular nature of your existing tables may be the only way to clean up this
poor design.

 

[Steve Schapel]

Kim,

At the risk of preaching on something you may already know, a field in a
table should relate to a data attribute or category. In your example,
ClientID meets this condition, i.e. it describes an attribute of the
Client (or the Subscription). LPT100a does not do this. LPT100a is the
name of a report or document, i.e. it is data, and as such it is not
applicable to define a field. This is sometimes known as the "fields as
data" trap. I would regard this as a pretty serious fundamental flaw in
the database design. This is the kind of thing that you see sometimes
in spreadsheets and stuff like that, but is not really how databases
work. The data attribute or category in your example, I imagine, is
something like ReportName. So the table should not have a separate
field for each report, it should have a separate *record* for each time
a client subscribes to a report. The table should have these fields:
ClientID
ReportName
.... and anything else pertinent to the subscription, such as for example
SubscriptionDate. So the table will have multiple records for each
Client, one for each document they subscribe to.

Ok, to achieve your purpose, if you are unable to normalise the table
structure, would be to write some code that transforms it into a
normalised structure in a temporary table. In other words, when you go
to produce your report, instead of trying to base the report on your
existing table, make a table based on the concept I gave above, and
write the data you require for your report into this table, and then
base yur report on that.

I can't be explicit, of course, without knowing more details of your
actual project. But here is some skeleton untested air code that might
point you in the right direction...

Dim dbs As Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourQuery")
With rst
Do Until .EOF
For Each fld In rst.Fields
If fld.Name Like "*PT*" Then ' (you will need to do
what is applicable here)
dbs.Execute "INSERT INTO NormalisedTable ( ClientID,
ReportName )" & _
" VALUES ( " & ![ClientID] & ", '" & fld.Name &
"' )", dbFailOnError
End If
Next fld
.MoveNext
Loop
End With

"YourQuery" would refer to a query based on your existing tables that
returns the data you need for your report.

 

[Steve Schapel]

Kim,

After posting, I realised I had forgotten to check for whether the
report field is ticked or not. You only want to append them to the
normailsed table if they are selected. Probably this would work...
If fld.Name Like "*PT*" And fld.Value = -1 Then

 

[Kim]

Thanks Steve! I tried it this morning and it works. I am running it
by my manager to see if she likes it. I will let you know if I have to
start over with something else. Unfortunately, this inherited database
was created with check boxes because a copy of the database is
distributed to all Implementation Managers for their use all year round
and is linked to a backend db of Tables on a LAN server.

Every time they get a new client to implement, the client is provided
with a list of all our reports that they can choose from. After they
decide, the Implem. Mgr. will go into several tabbed forms and click
the checkbox for each report selected along with filling out a
multitude of other client setup information. Then a 10 page Access
report containing all the information selected by the Implem. Mgr. is
generated as a snapshot and passed out to several IT groups in order to
setup the new client into our production & test systems.

In this revision of the db I was requested to create a stripped version
of the snapshot that we could send to the client as a sign-off of all
the items they requested and we are implementing. We knew if we
included all the reports we offer then the Implem. Mgrs. would spend
most of their days filtering questions about what each report does.
Thanks for all your help!

Kim

 

[Kim]

Thanks for the advice. I did inherit this monster. I think you are
both right with the new table idea. I took Steve's advice and used the
code sample he gave to create a new Table with the column names as
records and it worked. Now I am just waiting to see if my manager
approves of the change before I continue. Again, thanks for all your
help and I really appreciate the quick responses. Have a wonderful
weekend!

Kim

 

[Steve Schapel]

Kim,

Thanks for the update. Best wishes for the rest of the project, and
hopefully your manager will approve the improvements you are making to
the system.