X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Multiple Checkbox GUI In A Normalized Structure    
post Dec 1 2006, 02:38 PM

Retired Moderator
Posts: 10,493
Joined: 12-January 01
From: Lexington/Louisville KY USA

Many times when helping someone you’ll discover one or more tables in a database that contain multiple Yes/No fields. You can pretty much count on this meaning the database is not normalized. The usual dialog when the OP (original poster) is told they need to fix their structure is something like:

OP: “But I want to use check boxes on my forms!”

Responder: “That’s fine, but you still need to fix your structure. The easiest way to do this is with a continuous subform with combo boxes from which the user makes the selections.”

OP: “My users need to use checkboxes, not make selections from combo boxes!”

Responder: “Ok. That’s completely possible, but it takes some VBA code to accomplish.”

OP: “I don’t know anything about code. Having different fields is much easier. I don’t need to change anything.”

Responder: “What happens when you have to add another checkbox field? You’ll have to change the table and every query, form and report that uses that table if you add another field. What if you need to filter for only those persons with two of the items selected? It’s much easier to query one field than to work out criteria for 20 fields in one table.”

It boils down to the fact that how you want forms to look and how they interact with the end user has ABSOLUTELY NOTHING to do with normalizing the database. You can still use checkboxes, or a variation on that theme (like a multi-select listbox) with a normalized database.

The attachment demo’s three different methods of doing so:

1)frmPeopleUsingMultiSelectListbox uses a multi-select listbox and VBA code
2)frmPeopleUsingTempTableAndCheckBoxes uses a subform bound to a temporary table along with VBA code to append and remove choices
3)frmPeopleUsingUnboundCheckboxes uses multiple unbound checkboxes that are ‘filled’ at run time to the correct items from the available list and then VBA to append and remove selections as needed
Attached File(s)
Attached File  InsteadOfMultipleYesNoFields.zip ( 130.95K )Number of downloads: 2084
Go to the top of the page
post Jul 4 2011, 02:47 PM

Posts: 167
Joined: 25-February 10

I was trying to implement the feature from frmPeopleUsingMultiSelectListbox, and I noticed a problem.
Here is one way to replicate it:
1. Open frmPeopleUsingMultiSelectListbox
2. Move to the second record (Barney Rubble).
3. Select Alabama and Delaware.
4. Close the form and reopen.
5. Delaware is no longer an option for any person (not even for Barney!).
Alabama is still an option, presumably because it had already been selected (for Fred).
It appears that data re getting correctly inserted into tblPeopleStates, so I suspect the problem has to do with the OnCurrent code or row source for the list box. Any ideas?
Go to the top of the page
post Jul 4 2011, 03:08 PM

Posts: 167
Joined: 25-February 10

The fix is simple:
hange the row source for the unbound listbox to:
SELECT tblStates.StateID, tblStates.StateName FROM tblStates;
There is no need for including the tblPeopleStates, nor any WHERE criteria with PeopleID. Just let everyone have the full list of States in the listbox, and the OnCurrent code does the work or reselecting the States that are assigned to them.
Go to the top of the page
post Mar 17 2014, 10:31 AM

Posts: 16
Joined: 17-March 14

Hi Noah, and foxtrot.
I have been faithfully following instructions, customising away, and this little db is great. But I am a little stuck on finding the best way to create the search function, and from that, the reports.
Ocan create a form using unbound combo boxes to select the StateID, which then can trigger a query, and then open a report which displays the data. My problem is my Users want an 'AND' type search, rather than an 'OR' type search. So, they want the search to return People which are only linked to both Alabama and Illinois, not People which are linked to Alabama as well as People that are linked to Illinois.
Hope that explanation makes sense. I am an intermediate level access programmer, can follow code, but scare easily.
Thanks in advance for any suggestions.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    10th December 2018 - 01:20 AM