Display Field Name as Text Listing if Value True from Checkbox Fields

[stevensjn]

Hi everyone!

I have 40 or so columns in a table using the yes/no data type. Each
record in the table represents an audit of a paper form. Those items
filled incorrectly or incompletely on the paper form receive a check
on an access form during the audit process.

I would like to create a feedback form based on the audit record that
displays a listing of fields that received the check boxes, or rather,
were incomplete or incorrect on paper form that was audited.

On the feedback form, I only want to display something like this:

"Your form had the following errors:

ErrorFieldOneName
ErrorFieldFiveName
ErrorFieldTwentyFourName
ErrorFieldThirtyTwoName
"
In this listing I would like to use bunch of text boxes to represent
each possible field with the correct wording for a name for them as
the caption of that field's label. For example, for the field in the
audit table called "CallNum" I would like to make a hidden text box of
the same name with a label and caption of "Call Number".

Then if the record's CallNum field was checked, make that text box
visible and push it to the top of the report detail area, leaving
those fields that weren't checked, hidden from view.

Any ideas of the best approach I could use? I'm confused by the fact
that I have check boxes but want to show the field's name, not its
value. Also the CanShrink property of a text box doesn't seem to apply
to a check box.

Any help for this would be greatly appreciated! I'll keep digging
around but I can't seem to find an example of this sort of thing in
the group.

Thank you!

Jake

 

[Jeff Boyce]

Jake

What you've described sounds like how you'd handle your situation ... with a
spreadsheet! Using "repeating fields" (check#1, check#2, check#3, ...) in a
relational database like Access means you won't get the best use of the
relationally-oriented functions and features.

And you'll have all those "empty" checkboxes/fields when there's no issue.

And you'll have to modify your table, your form(s), your queries, your
reports, your macros, your code, ??!?? each time there's a change in the
number of checks you need to make. This is a maintenance nightmare!

A well-normalized design would allow you to add new "checks" without ANY
changes (other than adding a new item to a table). And, as a bonus, you can
print out the description/name of the check, instead of the value of a
checkbox. Here's a possible structure:

tblForm
FormID (unique identifier for each form)
... (other form-specific info)

tlkpThingsToCheck (a "lookup table" of, well, things to check)
ThingsToCheckID (unique identifier)
ThingTitle (this might be what you now use as the fieldname)
ThingDescription
... (other ThingsToCheck-specific info, perhaps DateAdded)

trelAuditError
AuditID (unique ID)
FormID (foreign key, from tblForm)
ThingsToCheckID (foreign key, from tlkpThingsToCheck table)
DateChecked

ANY row in the trelAudit table gives you a ThingToCheck that was identified
as an Audit Error. ?You want to know all the audit errors for FormID = 17
.... run a query! ?You want to know how many Forms had an error on
ThingsToCheck item = "No Signature" ... run a query!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[stevensjn]

Wow, that's a good point! I do understand the value in having the data
stored that way, and my report would be super easy to create, but it
leads me to the next question(s).

How would you develop a form that handles all the correct data
insertions? 40 insert statements? Sub forms? Off the top of my head I
can't think of a way to create a form with that structure other than
with the multiple inserts into the one to many table.

It sure was easy creating the form for my table. I wish I could show
you the db and discuss the issue with you to make sure my description
is clear.

Jake

 

[stevensjn]

Wow, that's a good point! I do understand the value in having the data
stored that way, and my report would be super easy to create, but it
leads me to the next question(s).

How would you develop a form that handles all the correct data
insertions? 40 insert statements? Sub forms? Off the top of my head I
can't think of a way to create a form with that structure other than
with the multiple inserts into the one to many table.

It sure was easy creating the form for my table. I wish I could show
you the db and discuss the issue with you to make sure my description
is clear.

Jake

Jeff if you know of any example database that uses something like that
I could adapt pretty easily!

 

[Jeff Boyce]

A couple approaches come to mind...

When you have a one-to-many relationship (Form-to-AuditErrors), you can use
a main-form/subform design.

The main form displays the Form records (unfortunate potential confusion
here ... Microsoft Access uses "form" to describe an object ... and you are
using the same word "Form". Access may get confused about what you are
referring to, since this is a reserved word. You might want to consider
changing the name of this thing.).

The subform displays the records from the trelAuditErrors table, and you can
use a form (Access form, not yours) set to Continuous to display each/all
AuditErrors, one per record in the subform. To add a new AuditError (in the
subform) for a main form Form (I am sooooo confused!), you can use a
combobox in the subform and select one of the items from the
tlkpThingsToCheck table. Access handles adding the proper FormID if you've
set this up as a mainform/subform design.

Another approach would be to use something like you see when you run one of
the wizards from the database window, say, for a new query. You get two
listboxes, the one on the left listing "available" items, the one on the
right listing "selected" items, and the buttons between for moving choices
back/forth. Behind these buttons/listboxes, the code that runs adds records
to (in your case) the trelAuditErrors table (or deletes them, as appropriate
to the listbox and button click). While this is a somewhat more
complex-to-code approach, users tend to find it somewhat more intuitive...
they "pick" the AuditErrors from a list of all possible, and "add" them to
this Form's record.

Hopefully that gives you a couple ideas. You could try searching online for
examples, using "mainform/subform" or "paired listboxes" as search terms.
Microsoft probably has examples in its Knowledge Base, too.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[stevensjn]

Thanks Jeff,

Unfortunately my target audience requires a form layout that matches
the paper form they're used to working with (for many reasons, all
bad), so I will be sticking with my previous table design. Perhaps I
could reach a similar result by transposing a result set of
trelAuditError records, but I have to get this report done very
quickly. Recreating both parts from scratch is something I may have to
do at a later time.

For now I need to only show fields with values on a report instead of
100 empty fields (yes 100!). A seemingly simple idea.

Regards,

Jake

 

[Jeff Boyce]

Jake

I'm not sure, based on your description, but it sounds like you/your users
are operating under the assumption that your Access forms have to match the
structure of your Access tables (or vice versa).

NOT!

Regards

Jeff Boyce
Microsoft Office/Access MVP