How can I make data entry easier?

[Bagheera]

I am creating a catalogue of information resources and need to classify each
according to the subject(s) it covers.

I have created two tables, tblResources and tblSubjects, and a table that
joins them. I can easily create a form for the resources with a subform to
list the subjects with a drop-down box from which to pick.

However, with a very long list of subjects, it's a bit unwieldy. I know that
the people who will be undertaking the classification would love check boxes
on a form they can tick.

Is there a simple way of setting this up for them? Would I have to write
code for each box which, if ticked, would add a record to the joining table?
I don't know how to do this so, if this is the answer, further advice would
be welcome.

Help will be much appreciated. Many thanks and Happy Christmas.

 

[Mark Andrews]

Unfortunately Access does not have a nice listbox with checkboxes (IT
SHOULD). Anyone from Microsoft reading this please add it to Access ASAP.
You can use a third party control for this (but that is not always desired).
You could use a multi-select list box to allow users to select multiple
subjects and then create the records after they make the selections.
Example:
- user presses button to open subject selection form
- user select multiple subjects
- user presses OK
- on load and OK your code works with the tblSubjects table to read or
manage the records in that table.
This step is where the extra work on your part is.

You could display the subjects as a comma delimited string to the user on
their main entry page.

Of course the subform way is much easier to implement. Perhaps there is
other ways I'm not thinking of?

My two cents,
Mark

 

[Banana]

I also agree that subform will be easier to implement.

However- it is also to "fake" a subform into looking like a listbox with
a checkboxes- a couple of formatting changes then a bit of clever SQL
will do the trick.

To create the "fake" checkbox, we need to have something to bind the
control to. Perhaps this SQL will do the trick (assuming we want
ResourceID to be the "checkbox":

SELECT Nz(ResourceID,0), SubjectID
FROM theJunctionTable j
LEFT JOIN tblResorces r
WHERE r.ResourceID = <current resource id>


Throw in a piece of VBA code on the checkbox's click event to perform a
insert/deletion into the subform's recordset.

The idea here is that this column, "Nz(ResourceID,0)" will give you a
column to bind the checkbox to, and whenever there is no matching
ResourceID for a given SubjectID, the checkbox will be empty, and
checked for the matched SubjectID.

The click event would then handle the insert/deletion and we deal with
subform's recordset directly to avoid the hassle of requerying & keeping
our position.

I've implemented something similar to that idea, but this is entirely
off the my memory so I may be missing something, but you should know
this is possible.

HTH.

 

[Banana]

SELECT Nz(ResourceID,0), SubjectID
FROM theJunctionTable j
LEFT JOIN tblResorces r
WHERE r.ResourceID = <current resource id>

I apologize. It should actually be:

SELECT Nz(ResourceID, 0), SubjectName
FROM tblSubject s
LEFT JOIN theJunctionTable j
ON s.SubjectID = j.SubjectID
WHERE j.ResourceID = <current resource id>
ORDER BY SubjectName;

 

[Mark Andrews]

I would go with Banana's idea over my original idea (probably looks a little
better with the checkboxes). However both ideas require you to write code
to insert and delete records based on the selections.
Mark

 

[Bob Quintal]

Unfortunately Access does not have a nice listbox with checkboxes
(IT SHOULD).

YOU DO NOT NEED IT!!!! just use a listbox with the multiselect
property set to simple. You click on any part of the row, and its
colors invert. you then process the list using a loop to get the
selected values.

If either you or the Original poster want a demo, email me and I'll
send a small database (28K zip)


Anyone from Microsoft reading this please add it to

 

[Mark Andrews]

I would say that many user find selecting multiple items a bit more
difficult with a multi-select list box (without checkboxes). Most
commercial apps use a list box with check boxes. The rest of my post
basically said yes you can use a multi-select list box (without checkboxes).
However the other post of simulating a check box list using a continuous
form might be the best approach.

I wouldn't use a third party control unless you are already installing other
third party controls.

Just my opinion,
Mark

 

[Bagheera]

A demo would be absolutely great. I tried to email you but couldn't find your
real email address. Please could you let me know the non-spam suffix and I'll
resend.

Many thanks.

 

[Bob Quintal]

A demo would be absolutely great. I tried to email you but
couldn't find your real email address. Please could you let me
know the non-spam suffix and I'll resend.

Many thanks.

(e-mail address removed)
I've altered my email address.
PA is y