best control/method for item selection on Access form

[Frances]

Hi All,

* PREMISE *
I'm creating an Access form with 150 items subdivided into 20
categories. Multiple categories (and items) can be selected so my
user wants checkboxes. All of the options need to be visible at the
same time so no dropdowns (combo boxes) and no scrolling lists (list
boxes). I will use tabbed sheets with logically grouped categories
(and their respective items).

95% of the records will have 1-2 items selected from 1 category and 0
items selected in the remaining 19 categories.
5% of the records will have 4-5 items selected from 2-3 categories and
0 items selected in the remaining 17-18 categories.


* PROBLEM *
What's the best control or best method to represent these items on an
Access form while being space-conscious on the backend?

Combo/List boxes create one column per control. Checkboxes create one
column per checkbox. That's a lot of extra columns in a table when
95% of the columns will have no selections.

To eliminate blank columns in the database, I've thought of using
unbound checkboxes (I think checkboxes be unbound) which, when
selected (on mouse down), will set a flag or variable in VB which will
then set the value into the corresponding category field-name (or
table) in the database. Rather than have the control set the value
directly, VB would do it.

I'm not sure that's the best method or the simplest method (in
following the KISS theory). Does Access even allow this kind of thing
or can it only be done with VB? I would welcome all suggestions.


Thanks,
Frances

[dogwalker]

Re: best control/method for item selection on Access form

it sounds like you have a typical order entry
situation where each order has one or more items.
and each item belongs to a category.
i think i would just use a datasheet type form
where you picked the category first on each
item line followed by the item.


"Frances" <whattuki@yahoo .com> wrote in message
news:bc1da336.0 310021258.202a3 4a@posting.goog le.com...[color=blue]
> Hi All,
>
> * PREMISE *
> I'm creating an Access form with 150 items subdivided into 20
> categories. Multiple categories (and items) can be selected so my
> user wants checkboxes. All of the options need to be visible at the
> same time so no dropdowns (combo boxes) and no scrolling lists (list
> boxes). I will use tabbed sheets with logically grouped categories
> (and their respective items).
>
> 95% of the records will have 1-2 items selected from 1 category and 0
> items selected in the remaining 19 categories.
> 5% of the records will have 4-5 items selected from 2-3 categories and
> 0 items selected in the remaining 17-18 categories.
>
>
> * PROBLEM *
> What's the best control or best method to represent these items on an
> Access form while being space-conscious on the backend?
>
> Combo/List boxes create one column per control. Checkboxes create one
> column per checkbox. That's a lot of extra columns in a table when
> 95% of the columns will have no selections.
>
> To eliminate blank columns in the database, I've thought of using
> unbound checkboxes (I think checkboxes be unbound) which, when
> selected (on mouse down), will set a flag or variable in VB which will
> then set the value into the corresponding category field-name (or
> table) in the database. Rather than have the control set the value
> directly, VB would do it.
>
> I'm not sure that's the best method or the simplest method (in
> following the KISS theory). Does Access even allow this kind of thing
> or can it only be done with VB? I would welcome all suggestions.
>
>
> Thanks,
> Frances[/color]

[Pieter Linden]

Re: best control/method for item selection on Access form

> * PREMISE *[color=blue]
> I'm creating an Access form with 150 items subdivided into 20
> categories. Multiple categories (and items) can be selected so my
> user wants checkboxes. All of the options need to be visible at the
> same time so no dropdowns (combo boxes) and no scrolling lists (list
> boxes). I will use tabbed sheets with logically grouped categories
> (and their respective items).[/color]

<SHUDDER>
That's HIDEOUS. about the only way you can do this at least
reasonably flexibly/efficiently is to use comboboxes or listboxes.
Who specified this requirement? Is it real? Glad I won't have to use
it, if you have to do it as described. I hope you won't have to
modify it... it's gonna be ugly.

[Lyle Fairfield]

Re: best control/method for item selection on Access form

whattuki@yahoo. com (Frances) wrote in
news:bc1da336.0 310021258.202a3 4a@posting.goog le.com:
[color=blue]
> Hi All,
>
> * PREMISE *
> I'm creating an Access form with 150 items subdivided into 20
> categories. Multiple categories (and items) can be selected so my
> user wants checkboxes. All of the options need to be visible at the
> same time so no dropdowns (combo boxes) and no scrolling lists (list
> boxes). I will use tabbed sheets with logically grouped categories
> (and their respective items).
>
> 95% of the records will have 1-2 items selected from 1 category and 0
> items selected in the remaining 19 categories.
> 5% of the records will have 4-5 items selected from 2-3 categories and
> 0 items selected in the remaining 17-18 categories.
>
>
> * PROBLEM *
> What's the best control or best method to represent these items on an
> Access form while being space-conscious on the backend?
>
> Combo/List boxes create one column per control. Checkboxes create one
> column per checkbox. That's a lot of extra columns in a table when
> 95% of the columns will have no selections.
>
> To eliminate blank columns in the database, I've thought of using
> unbound checkboxes (I think checkboxes be unbound) which, when
> selected (on mouse down), will set a flag or variable in VB which will
> then set the value into the corresponding category field-name (or
> table) in the database. Rather than have the control set the value
> directly, VB would do it.
>
> I'm not sure that's the best method or the simplest method (in
> following the KISS theory). Does Access even allow this kind of thing
> or can it only be done with VB? I would welcome all suggestions.
>
>
> Thanks,
> Frances[/color]

Am I correct in thinking that internally JET optimizes True/False fields so
that 8 such fields take up one byte of storage? If I am, (hmmmm ... was it
TC who suggested this?) then the back end would use only 19 bytes per
record for storing your 150 "checks" although the actual table structure
would show 150 fields.

If we are talking bits then assuming the twenty categories have a maximum
of eight items one could use a byte for each category and use bitwise
arithmetic to show and store each item as an on or off bit. This is sort of
the same thing, but here the developer might be more in control. So this
way we could have twenty fields of one byte.

I suppose too, we could have one binary field of length twenty, with each
byte then being used as above.

If we wanted to be bizarre we could use colours I guess. Let's see ... 4
bytes 3 of which are actually used. So 24 of the items could be shown in
one color and we could show the 150 ... well let's make it 168 in a
heptagon with the colors of each of the sides indicating on and off of
related items. Would make one hell of a form, No?

Don't like that? Well then maybe we could use gradient shading as sort of a
Gestalt???

But who would want to be bizarre?

Ok ... going to bed now.

No wait ... maybe Rap music?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Frances]

Re: best control/method for item selection on Access form

Thanks, Pieter, for your response.

pietlinden@hotm ail.com (Pieter Linden) wrote in message news:<bf31e41b. 0310021752.5da7 e4d0@posting.go ogle.com>...[color=blue][color=green]
> > * PREMISE *
> > I'm creating an Access form with 150 items subdivided into 20
> > categories. Multiple categories (and items) can be selected so my
> > user wants checkboxes. All of the options need to be visible at the
> > same time so no dropdowns (combo boxes) and no scrolling lists (list
> > boxes). I will use tabbed sheets with logically grouped categories
> > (and their respective items).[/color]
>
> <SHUDDER>
> That's HIDEOUS. about the only way you can do this at least
> reasonably flexibly/efficiently is to use comboboxes or listboxes.
> Who specified this requirement? Is it real? Glad I won't have to use
> it, if you have to do it as described. I hope you won't have to
> modify it... it's gonna be ugly.[/color]

I agree, it is HIDEOUS! After spending two weeks trying to figure out
the best way to handle this, I'm seeking the assistance of the
contributors of this newsgroup (who, BTW, are awesome!). I'm hoping
they may have some ideas or even someone who has come across this
before.

I do agree with the user in that checkboxes are preferred to
list/combo boxes. The reasoning is, there will be a group of data
entry clerks and they may "get lazy" and start picking the first item
within a category rather than drop down or scroll through each
potentially relevant category to pick the item that best fits. If
they see everything laid out on the screen, checkbox style, it
eliminates the need to scroll and "hunt" for the item that best fits.

However, I have to take into consideration the limitations of my
development application, in this case, Access.


\Frances

[Albert D. Kallal]

Re: best control/method for item selection on Access form

You got some good feedback here.

I would suggest a normalized table design. Continues forms in ms-access are
quite nice.

This means a related table, and a sub-form.

As for continues forms..yes, they are the solution here.

You are probably better off to offer a combo box, and let the user enter one
new value in a sub-form at a time. (don't try and show 150 things at once).

You can use combo boxes in a continues form. Here is some screen shots of
what I mean:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kallal@msn.com

http://www.attcanada.net/~kallal.msn

[Ben Yanis]

Re: best control/method for item selection on Access form

Although not especially search efficient, I would have all the fields
unbound; and build up a string value based on the choices. This will
allow you to have one field and keep your table managable.

For example, if someone chooses items 1, 2 and 3, the string could
look like:

"item1;item2;it em3"

This wouldn't be too hard to parse, and would make the table structure
friendly. The searching time wouldn't be ideal, but it would be
acceptable in most cases.

As for the control layout; I would use cascading lists. It would be
much cleaner. If you can win the argument do so; if not; give them
the checks they demand.

-Ben

whattuki@yahoo. com (Frances) wrote in message news:<bc1da336. 0310021258.202a 34a@posting.goo gle.com>...[color=blue]
> Hi All,
>
> * PREMISE *
> I'm creating an Access form with 150 items subdivided into 20
> categories. Multiple categories (and items) can be selected so my
> user wants checkboxes. All of the options need to be visible at the
> same time so no dropdowns (combo boxes) and no scrolling lists (list
> boxes). I will use tabbed sheets with logically grouped categories
> (and their respective items).
>
> 95% of the records will have 1-2 items selected from 1 category and 0
> items selected in the remaining 19 categories.
> 5% of the records will have 4-5 items selected from 2-3 categories and
> 0 items selected in the remaining 17-18 categories.
>
>
> * PROBLEM *
> What's the best control or best method to represent these items on an
> Access form while being space-conscious on the backend?
>
> Combo/List boxes create one column per control. Checkboxes create one
> column per checkbox. That's a lot of extra columns in a table when
> 95% of the columns will have no selections.
>
> To eliminate blank columns in the database, I've thought of using
> unbound checkboxes (I think checkboxes be unbound) which, when
> selected (on mouse down), will set a flag or variable in VB which will
> then set the value into the corresponding category field-name (or
> table) in the database. Rather than have the control set the value
> directly, VB would do it.
>
> I'm not sure that's the best method or the simplest method (in
> following the KISS theory). Does Access even allow this kind of thing
> or can it only be done with VB? I would welcome all suggestions.
>
>
> Thanks,
> Frances[/color]

[Pieter Linden]

Re: best control/method for item selection on Access form

> As for the control layout; I would use cascading lists. It would be[color=blue]
> much cleaner. If you can win the argument do so; if not; give them
> the checks they demand.
>
> -Ben[/color]

LOL... good point, Ben. (Do ya mean you can't just threaten them?
You have to actually *show* them how your design is better?!) you
might want to build a quick demo just so they can play with it and
then let them decide... Just make sure it works, or your argument will
be toast.

[Michael Tueller]

Re: best control/method for item selection on Access form

On Fri, 3 Oct 2003 22:16:49 +0200, Albert D. Kallal wrote
(in message <ROkfb.11513$6C 4.10808@pd7tw1n o>):
[color=blue]
> http://www.attcanada.net/~kallal.msn/Articles/Grid.htm[/color]

Albert, I have a follow-up question about you search form:

Do you Requery the listbox after each keystroke (with the OnChange-Event
of the search TextBox) or after the user is hitting Return (or another
Keystroke)?

I'm asking because I have built a lot of such "clairvoyan ce" search-forms
in a current project and I don't know enough about the performance issues
with such a form in a FE-BE-setting (I assume a 100T network with just
a Switch between clients and the BE-Fileserver and a not too complicated
Query for the listbox, although I have a few ones that do some formatting
of pulled fields ->i.e. concatenation of first/second name).

In other words: Is the lag of requerying the list box big enough to avoid
the OnChange-method and use the AfterUpdate-Event of the SearchField?

(Of course all this depends on the complexity of the db/Queries/tables etc,
but maybe you could write down your experiences with these kind of
search-forms).

Thanks,
Michael

[Frances]

Re: best control/method for item selection on Access form

pietlinden@hotm ail.com (Pieter Linden) wrote in message news:<bf31e41b. 0310040824.7af5 3491@posting.go ogle.com>...[color=blue][color=green]
> > As for the control layout; I would use cascading lists. It would be
> > much cleaner. If you can win the argument do so; if not; give them
> > the checks they demand.
> >
> > -Ben[/color]
>
> LOL... good point, Ben. (Do ya mean you can't just threaten them?
> You have to actually *show* them how your design is better?!) you
> might want to build a quick demo just so they can play with it and
> then let them decide... Just make sure it works, or your argument will
> be toast.[/color]

Thanks for your suggestions!


\Frances