Users want Check Boxes for entry

[Melvis]

Hi,

I am in the process of making a database to track application rejections for
a colleague. What happens is people apply for tags, and they need a way to
track why the rejections were rejected. They do not track the applications
that go through.

The table structure seems easy to me, with an Aapplication table, a
Reason_for_rejection table, and an Application_rejected_reasons table.
Normally for this type of table schema, I would use a subform for them to
choose the reasons. But what the supervisor is asking for is like a paper
form with check boxes for each reason. She thinks this will be faster and
more efficient for her employees when they are entering the rejections into
the database.

What I am wondering is if there is any way to accomplish waht she wants
without pulling out all my hair. Could I use an array of check boxes? If so,
how? This way I could at least loop through the check boxes to see which ones
are checked rather than writing code for each one. Also, there is the
possibility that the reasons could change in the future, so I would need the
ability to populate the labels from the Reasons table. Man, this sounds
freaky!

Any help you could possibly give me on this would be greatly appreciated.
Thanks...
~MATT

 

[Allen Browne]

If you use a numeric primary key for your Application_rejected_reasons
table, then you could interface the foreign key field with an option group.
The group lists all the reasons, and the field stores the number of the
seleted box.

The major drawback of this interface is that when new reasons are added
later, you have to redesign the form so the option group contains the extra
boxes.

 

[Melvis]

Thanks for the quick reply!

I get what you're saying, but I need to make this so they can maybe add a
couple reasons without programming, but can definitely CHANGE the reasons at
any time.

Also, I can't use an option group because there can be more than one reason
for an application getting rejected.

~MATT

 

[Sprinks]

Hi, Melvis.

Sure, simply create an Option Group control. By default, Access places
buttons for each choice, but if the supervisor prefers checkboxes, you can
delete the buttons and their labels, and replace them with checkboxes.
Simply place the first checkbox within the option group frame, copy it to the
clipboard, and paste it as many times as you need.

Bind the Option Group to the field in your table that will store the code of
the rejection reason (the FK to the RejectionReasons table), and manually
change each label and the Option Value property of each checkbox to its
appropriate foreign key value.

Note that this strategy is only effective if only a single reason is to be
entered. If multiple reasons may be checked, looping through the checkbox
controls is your best option, perhaps though a Post command button:

Dim blnPosted As Boolean ' Flag to avoid duplicate postings of these records
Dim ctl As Control
blnPosted = False

If Not blnPosted Then
' For all controls on the form
For Each ctl in Me.Controls

' Determine if the control is one of your checkboxes
If ctl.ControlType = acCheckBox Then

' Use the Tag property to distinguish THESE checkboxes from others you may
' have on the form
If ctl.Tag = "x" Then
If ctl.Value = True Then
' Insert record in your Reasons detail table
End If
End If

End If
End If
blnPosted = True

 

[Allen Browne]

I'm not sure your boss has thought this through.

If an application is rejected for reason 1 (say, Not Skilled), and later
reason 1 changes, then any existing records will also change to whatever the
new reason is. All existing records will then be wrong.

Given the difficulty in adding new reasons, and the meaninglessness of the
existing data if the reasons are changed, someone needs to rethink this
requirement.

 

[Melvis]

The gist of the reason would not necessarily change, but some changes would
be made to the wording, such as changes in prices for services and such...

 

[Khai]

Couldn't he dynamically add those checkboxes to the form? There's a reason
table, which means there's a set number of reasons, that can dynamically
change on the adding/deleting of reasons in that table.

Run through the boxes, add boxes at positions mathematically apart?

I did this with a vb project I worked on, and just took the few minutes to
figure out the spacing, and voila.

 

[Melvis]

This would be great if I knew how to add controls programmatically... <sigh>
hehe

 

[Allen Browne]

It's easy enough to use the Open event of the form to OpenRecordset into the
Application_rejected_reasons table, and then loop through the checkboxes
setting the Caption of the attached label.

This example assumes checkboxes named "chk1", "chk2", etc. where the number
matches the primary key number of the reason in the table:

Dim rs as DAO.Recordset
Dim strCtrlName As String
Set rs = dbEngine(0)(0).OpenRecordset("Application_rejected_reasons")
Do While Not rs.EOF
strCtrlName = "chk" & rs!ID
Me(strCtrlName).Controls(0).Caption = rs!Reason
rs.MoveNext
Loop
rs.Close
Set rs = Nothing