Share via

How to create a checklist in access 2016

Anonymous
2016-05-12T17:05:37+00:00

Hello everyone, 

I am still working on my data entry form.  What I want to achieve is to have a subform where the user can see a list of different groups an individual can be a part of and is able to check each group that applies.  For example the Group types are deacon, choir member, staff, Sunday school teacher, etc.  It is possible for one individual to be a part of all four of these groups.   I need to be able to retain the information to run queries for a list of all Sunday School Teachers, etc. 

I have thought about doing a checklist.  I have watched a video by Tony Hines that functions like what I want it to do but the video does not show how to build the tables with the specific data types.  (Some I could guess what type, others I could not).  I was wondering if a checklist is the right way to do this.

I was going to put on checklist on the data entry form as a subform.  My data entry form has tab controls with subforms on each tab control, except the first tab which is based on the individual table.

Thanks

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2016-05-12T17:32:05+00:00

    First, you have to understand that data storage and data presentation are two separate items. 

    First to the storage issue. What you have is a many to many relationship. One member can belong to many groups and one group can have many members. So the proper way to design this is with a junction table like so:

    tjxGroupMember

    GroupMemberID (PK autonumber)

    GroupID (FK)

    MemberID (FK)

    You would also have a unique index on the combination of the 2 FKs to prevent duplication. You would also need a Groups table:

    tluGroups

    GroupID (PK Autonumber)

    GroupName

    Now once you get that, there are several ways to enter the data. The easiest is by using a Continuous form subform bound to the junction table and linked on MemberID. The subform will have one combobox that lists all the groups and you add a record for each group the member belongs to. 

    You could have a checkbox interface, but then you would have to handle getting the data from the form to the table and vice versa in code. If you want more help with that let us know.

    There is another alternative, that of using a Multi-Value field. This is created by using a Lookup field in the table design. This presents a hybrid combobox/checklist interface. The problems with MVFs are that they are not scalable to other database systems. And its harder to do queries on tables with MVFs.

    2 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-05-13T16:01:09+00:00

    Thanks Scott,

        The continuous subform works for me without having to use code. 

    Linda

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2016-05-12T17:52:37+00:00

    I'd go with the conventional form/continuous subform set-up which has been successfully used in a million and one databases.  It's very easy to implement and is code-free.

    If you really do want a 'checklist' type of interface you can use a multi-select list box, but you then have to write code to read/write the values from/to the underlying table.  You'll find an example in StudentCourses.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates a number of possible interfaces for this sort of basic many-to-many relationship type, including a conventional form/subform and a multi-select list box.

    One important thing to note about using an interface other than a subform is that you cannot have non-key attributes of the relationship type like the 'Status' attribute in my demo.  This is also true of the built in 'multi-valued field' interface which Scott mentions.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2016-05-13T15:59:41+00:00

    Thanks for sharing the one drive database.  It helped to see what Scott was talking about.

    Linda

    0 comments