(simple?) normalization - parent-child subform question

[Steve Linberg]

Hello everyone.

I'm kicking a lot of rust off my MS-Access knowledge after many years
away, and trying to implement something I think should be simple, and I
am hoping can be done without coding, as it would seem to be a common
need for properly normalized data.

As a simple example, I want to create a database that tracks people and
colors they like, and a form that allows this to be simply controlled.
There's a table of people, a table of colors, and a child table with
people and colors. Very generically, in other words, the tables would
basically be this:

tblPeople:
ID autonumber
txtName text

tblColors:
ID autonumber
txtColor text

tblPeopleColors:
PersonID number
ColorID number

If Joe likes blue, red and green, there are 3 records in tblPeopleColors
with Joe's ID, and the IDs for the colors blue, red and green.

What I want to do is build a form that simply controls this in Access.
It would use tblPeople as its main record source, and for each person,
it would show their name, and then all of the colors in tblColors
(probably in a listbox?), selected or not according to whether there's a
record in tblPeopleColors for that person and each color.

Selecting or unselecting colors would, when the record is closed, result
in records being created or deleted from tblPeopleColors as needed to
make tblPeopleColors reflect the state of the selected colors, one for
each record, adding or deleting as needed.

I wrote some VB to do this manually, hooking into the form's
Form_Current and Form_BeforeUpdate events to populate the selected
states of each row in the listbox and write out the differences if the
form was dirtied, but this seems awfully tedious and I have to do it
often enough that I wonder if there isn't a simpler way.

Is there a way to do this with a subform and not have to manually create
and delete records as the selections of colors are changed? The key
thing is that the list has to show all of the available colors from
tblColors, not just those that are selected, and hopefully be smart
about when to update and when it doesn't need to.

Any advice appreciated. Thanks.

 

[tina]

could you get by with *two* listboxes? one listing the colors the person
likes (on the right), and the other listing the "rest of" the colors (on the
left). you could have two command buttons between them: select a color in
the "likes" listbox and click the "left arrow" button to move that color to
the "rest of " listbox. select a color in the "rest of" listbox and click
the "right arrow" button to move the color to the "likes" listbox.

it's not so hard to set up - a Delete query, an Append query, and a little
requerying of listboxes at strategic moments...

hth

 

[Steve Linberg]

Yes - functionally I could do this, but interface-wise it's not what the
client wants. What they really want is a list of checkboxes to reflect
various boolean states for clients (on mailing list, donor, retired, etc
- all yes/no values) - and the states that are possible are stored in a
table because they vary from client to client using the app, so the app
needs to look them up and build a control with them.

I thought maybe I could create checkboxes on the fly and put them
(disposably) on the form when it opens, but it turns out you can't do
that (in any way I know) without actually putting the form into design
view in front of the client and making changes to it, which exposes the
"underside" more than the client wants, again. They want it all on a
single (hopefully simple) form.

I was hoping since this was a way to correcly use normalization, that
some kind of method would be supported in Access with subforms. What
I've done so far is write some code to populate a listbox with the
boolean items and use the selected/unselected state, and manually do the
record writing/deleting as described below, which works, but the problem
now is that the client wants some of the boolean fields to be
admin-only, viewable but not active for non-admin users, and there's no
way to selectively disable individual items in a listbox that I know of.

From a database perspective, the subform (if this were possible) would
need to open with a JOIN as part of its query, since it would need to
show all of the options and let the user pick from them, but the
"picking" process itself would be indicating records to create (for
checking/selecting) or delete (for unchecking/deselecting) when the main
form closes, and indicate the initial state according to whether there
is a record for each row in the JOIN.

It sounds complicated, but I feel like it shouldn't be - with proper
normalization, this is a very common need in databases, and I was hoping
Access would be able to handle it internally without code somehow.

- Steve

 

[Klatuu]

Using Check boxes to make selections on dynamic data is a really bad idea.
As you have found, you can't create contorls on the fly. There is another
issue. There is a life time limit of controls for a form. Each time you add
a control, it adds to that count. Deleting a control does not subtract from
that count, so at some point, you will blow the limit and your application
will cease to function.

The suggestion tina provided is probably the very best way to do that.
Clients will often ask for things that don't make sense from a technical
perspective as well as not understanding how people use a computer and ask
for things that slow navigation and entry. It is your job to educate them.
Show them that clicking on a list is no harder than clicking a check box and
is actually better visually.

Your database design is okay.

 

[Dirk Goldgar]

Yes - functionally I could do this, but interface-wise it's not what
the client wants. What they really want is a list of checkboxes to
reflect various boolean states for clients (on mailing list, donor,
retired, etc - all yes/no values) - and the states that are possible
are stored in a table because they vary from client to client using
the app, so the app needs to look them up and build a control with
them.

I thought maybe I could create checkboxes on the fly and put them
(disposably) on the form when it opens, but it turns out you can't do
that (in any way I know) without actually putting the form into design
view in front of the client and making changes to it, which exposes
the "underside" more than the client wants, again. They want it all
on a single (hopefully simple) form.

I was hoping since this was a way to correcly use normalization, that
some kind of method would be supported in Access with subforms. What
I've done so far is write some code to populate a listbox with the
boolean items and use the selected/unselected state, and manually do
the record writing/deleting as described below, which works, but the
problem now is that the client wants some of the boolean fields to be
admin-only, viewable but not active for non-admin users, and there's
no way to selectively disable individual items in a listbox that I
know of.

From a database perspective, the subform (if this were possible)
would need to open with a JOIN as part of its query, since it would
need to show all of the options and let the user pick from them, but
the "picking" process itself would be indicating records to create
(for checking/selecting) or delete (for unchecking/deselecting) when
the main form closes, and indicate the initial state according to
whether there is a record for each row in the JOIN.

It sounds complicated, but I feel like it shouldn't be - with proper
normalization, this is a very common need in databases, and I was
hoping Access would be able to handle it internally without code
somehow.

I agree with you -- it should be a built-in user-interface object of
some sort, but it doesn't exist in current versions of Access. I've
implemented it using a work table -- loading the work table in the main
form's current event, and unloading it when the form closes or changes
to a new record. But I wish there were a simpler way.

 

[Steve Linberg]

I agree with you -- it should be a built-in user-interface object of
some sort, but it doesn't exist in current versions of Access. I've
implemented it using a work table -- loading the work table in the main
form's current event, and unloading it when the form closes or changes
to a new record. But I wish there were a simpler way.

That's what I was afraid of. Oh well. Guess I'll just carry on
manually the way I'm doing, but it sure makes normalizing the databases
a little more painful - I could hack my way around this by just adding a
lot of boolean fields to the main table, but it leaves the users in the
position of modifying the table stuctures if they want to add more
variables or change any, and I don't want them that.

Looks like listboxes is how it's going to have to stay, with custom code
handling record moves, and I'll have to separate the admin and non-admin
variables into separate listboxes as well so I can turn some of them off
as needed. :/

Thanks for all the responses.

 

[Dirk Goldgar]

That's what I was afraid of. Oh well. Guess I'll just carry on
manually the way I'm doing, but it sure makes normalizing the
databases a little more painful - I could hack my way around this by
just adding a lot of boolean fields to the main table, but it leaves
the users in the position of modifying the table stuctures if they
want to add more variables or change any, and I don't want them that.

Looks like listboxes is how it's going to have to stay, with custom
code handling record moves, and I'll have to separate the admin and
non-admin variables into separate listboxes as well so I can turn
some of them off as needed. :/

Thanks for all the responses.

FWIW, there are major UI changes coming in the next release, "Office
12", so keep an eye out for possible enhancements in there.