How can I make data entry easier?

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.

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

"Bagheera" wrote in message
...
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.

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.


Mark Andrews wrote:
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

"Bagheera" wrote in message
...
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.

Banana wrote:
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;

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

"Banana" Banana@Republic wrote in message
news:4B2BAA14.2030700@Republic...
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.


Mark Andrews wrote:
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

"Bagheera" wrote in message
...
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" wrote in
:

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
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

"Bagheera" wrote in message
...
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.






--
Bob Quintal

PA is y I've altered my email address.

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

"Bob Quintal" wrote in message
...
"Mark Andrews" wrote in
:

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
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

"Bagheera" wrote in message
...
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.






--
Bob Quintal

PA is y I've altered my email address.

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" wrote:

"Mark Andrews" wrote in
:

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
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

"Bagheera" wrote in message
...
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.






--
Bob Quintal

PA is y I've altered my email address.
.

=?Utf-8?B?QmFnaGVlcmE=?= wrote
in :

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.



I've altered my email address.
PA is y



--
Bob Quintal