It is not unusual to have multiple small lists that to be available to users from combo boxes. This, in itself, is not a real problem except that I do not like hard coding values in combo boxes and I do not like creating multiple tables to hold just a couple of values. Even if there are quite a few values for one list, if those values are not going to change drastically during the use of the application then it seem like over-kill to create one table per list. With the input and suggestions of others I have come up with a method that I implements often in my database applications to provide significant functionality to users for the purpose of managing multiple lists which are defined in a single table.
The attached database file, which is 2002 format but can easily be run in 2007, demonstrate the methods I use when implementing my multi-list management system. Information is included in the application, just click the Help buttons.
The demo database has the following features:
I have found the functionality demonstrated in the attached database to be very useful. I just hope that some of this is beneficial to some of you here on the forms.
The attached database file, which is 2002 format but can easily be run in 2007, demonstrate the methods I use when implementing my multi-list management system. Information is included in the application, just click the Help buttons.
The demo database has the following features:
- Use a form to manage multiple lists in a single table
- Display a unique list of List Types from the multi-list table
- Display the list of values in a list box for the selected List Type in a combo box
- Allow users to manage the values in any list
- Allow users to add values to any of these list
- Use the “OnNotInList” event
- Use the Double Click event of the combo box
- Use a menu option from the Main Menu of the application
- Allow users to manage the sort order of each list
- Allow users to simple deactivate an item from the list and not have to delete it
- Demonstrates the use of a combo box to specify the values displayed in a list box
- Demonstrates the use of combo boxes to display different value lists from the same table
- Demonstrates the use of multiple filtering and sorting options
- Demonstrates building a custom SQL statement to be used a the record source fo a list box based on selection and use of options on the form
- Demonstrates the use of a list box for selection of a specific record to edit or delete
I have found the functionality demonstrated in the attached database to be very useful. I just hope that some of this is beneficial to some of you here on the forms.