How to create a subform with a Checklist of options

[omniaccess]

In outlook, when you go to a schedule item and use categories, you see a list of all current categories with a checkbox next to each one for the user to select applicable categories. After you finish selecting the ones you want, one the main form these categories are then listed one after another with a comma to separate each one.

How do I do this in Access? When I create a list of categories and then connect another table to it with a Yes/No field in it and the matching categoryID, it doesn't end up working quite right.

Plus, how do I then have the list show up on the front like Outlook does with a line of comma-separated categories?

For now, I simply have a subform with a combo box for them to choose a category. Once they choose one category, they can add another one. The problem is that they have to do this one at a time from the combobox instead of having the list of all possible selections and then choosing which ones they want.

Any insight would be very helpful.

 

[TomHW]

One way would be to create a multiselect listbox instead of a combobox, then they could select all of their options at one time.

Tom

 

[omniaccess]

The drawback to using a multi-select listbox is that it is designed for inputting. If you bring up the list later to adjust what is selected.

One other comment about the subform with checkboxes:

I can create the list linked with the check boxes and all the categories show up fine to select. BUT, once the subform is put on the main form and I Master/Child link it to the main form, then the choices do not show up.

 

[TomHW]

With a multi-select listbox, the .ItemsSelected property is both read/write. This means that you can set the list back to some previous selected state if you so choose. But since this does not seem like the way you want to go, lets get some things cleared up so I can better help you out.

You say the subform has a list linked with check boxes. Is this list a listbox? Is this subform just a bunch of checkboxes with labels naming each category, etc?

What is the point of the subform?

Could you please explain the purpose of your fomrs a little better because I have no idea what you mean by "... I create a list of categories and then connect another table to it with a Yes/No field in it and the matching categoryID...." I cannot find mention of a first table to add another to.

Do you want something to happen immediately after each box is checked or is the user supposed to make all of their selections and then click 'done' or something?

 

[omniaccess]

Are you familiar with Outlook? The example of what I want to recreate is the category selection for an appointment.

I can make the continuous-form subform look the same as Outlook, and if I open the form without making it a subform, each line shows properly (a checkbox with a category, all available categories showing). But, when I connect the subform to the main form,

For tables look like this:

tblScheduleAllocatedTable (This table is empty)
(This table keeps what categories are allocated to which schedule item)

AllocatedCategoryID AutoNumber
CategorySelect Yes/No
CategoryIDLink Long Integer
ScheduleIDLink Long Integer


tblCategoryTable (This contains Category List)

CategoryID AutoNumber
CategoryName Text, 20
CategoryDescription Text, 80


Whese two tables are linked as Showing all of 'tblCategoryTable' and showing matching 'tblScheduleAllocatedTable' and I view JUST the subform, everything is fine.

But, when this subform is linked to a schedule item, nothing shows up. It is linked from ScheduleIDLink (Child) to ScheduleID (Parent). I can understand why, because there is no reference in 'tblScheduleAllocatedTable' yet for the schedule item, because no category has yet been selected, and there is no record in 'tblScheduleAllocatedTable'.

I need to find out:

1) Am I doing the connection of the tables wrong?
2) What layout of tables is necessary?
3) Is there some coding to make the whole list show?
4) How to have the same subform show all existing categories and a check next to those previously chosen whenever the categories are viewed again?
5) How to get those categories to then show up on the parent form as 'Business, Personal, Accounting' (again, my reference to Outlook)?

I hope this gives more information to help my situation. If I have not given enough info, please look at the layout in Outlook, because it shows exactly what I am trying to recreate in Access.

Thomas

 

[TomHW]

OK, that clears things up quite a bit. It's good that you realized why nothing is showing up on the linked subform. To fix this problem you need to create a row in tblScheduleAllocatedTable for each row in tblCategoryTable when a new schedule is created. As soon as a scheduleID becomes available do this:

dim strSQL as String

strSQL = "INSERT INTO tblScheduleAllocatedTable" & _
         "SELECT " & Me.ScheduleID & " As [ScheduleIDLink]," & _
         " False As [CategorySelect], CategoryID As [CategoryIDLink]" & _
         "FROM tblCategoryTable;"

DoCmd.RunSQL strSQL

This code will create a row for each category in tblCategoryTable that has the scheduleID pulled from the form you are on (as long as that is referenced by Me.ScheduleID) where no category is selected (Hence the 'False As [CategorySelect]').

Than you can requery the subform and it should show all of your categories with none selected. Or if you run this code after creating a new schedule on another form, the subform will show up correctly when you open it.

After this you can just create code that updates the correct table row when a category is selected or unselected (Set CategorySelect to True or False) although this should already occur if the recordsource of the subform is a join of the two tables. This should keep everything you have already intact. There are other ways to do this but they would require reworking everything and, more than likely, structure changes.


There are a few ways to implement the categories list on the parent form.
1) My suggestion would be to create a Class that implements a Collection and just adds a .List method to its functionality. This way, after a xheckbox is selected or deselected, you could add or remove its category name from the collection and then fill a textbox in the parent form with the value of .List, where .List just returns a string with the name of each category in the collection. This, to me, would be the fastest method.

2) After updating each checkbox, you could create a recordset of each row in tblScheduleAllocatedTable joined with tblCategoryTable where CategorySelected is True and ScheduleIDLink is the ScheduleID you are working with. Then you can iterate through the recordset pulling the CategoryName and adding it to a string. This option seems as though it would be slower though because you are polling the database every time a checkbox is clicked.


There are definitely other ways of accomplishing these tasks, but this is what I have come up with quickly.

I hope it helps,
Tom