Subfoirm of Checkboxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gj - 27 Sep 2004 05:18 GMT

I have always wondered how to do the following using standard forms
without VBA:

Staff
[T_ID]
[T_NAME]

Staff Group Memberships
[TG_ID]
[GROUP_ID]

Groups
[GROUP_ID]
[GROUP_NAME]

Say Groups has 15 entries how is it possible to create a subform for
STAFF GROUP MEMBERSHIPS on a STAFF form where all 15 available groups
are listed and the user just needs to click in a box. The subform would
need to show existing memberships where entered.

My need for this is to make it easier for users to enter data. It is far
simpler to click in 5 of the 15 available check boxes in a list than to
select 5 times from a combo box on individual records.

Additionally, in this example the GROUPS could change at any time with
new records being added so maually adding check boxes to a form is out.

Once I work this out the same method will be used multiple times
throghout a number of applications.

GJ

Reply to this Message

gj - 27 Sep 2004 05:25 GMT

Sorry for the munted subject line. Clicked the wrong button

Reply to this Message

Allen Browne - 27 Sep 2004 05:43 GMT

The usual way to interface this is a subform bound to Staff Group
Memberships, with a combo box sourcing records from Groups.

If you want, you can interface it with a pair of list boxes (one showing the
memberships the person does have and the other what they don't have), and 2
command buttons (for Add and Remove). List boxes don't have a
LinkMasterFields/LinkChildFields, so you have to use the Current event of
the form to set their RowSource. The "has" row source is easy. The "has not"
is a frustrated outer join: use the Unmatched Query Wizard to create an
example if you don't know what that is.

The command buttons execute action query statements to Append or Delete the
appropriate record in the junction table. Then Requery both list boxes to
display the change.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have always wondered how to do the following using standard forms without
>VBA:
[quoted text clipped - 27 lines]
>
> GJ

Reply to this Message