Best Way to Allow Maintenance of Drop-Down Lists?

[Jen]

My database has several combo boxes with "Not in List" events, so the user
can easily add new items to the drop-down list if necessary. However, she has
requested that she be able to maintain these lists by getting rid of old
selections that are no longer needed.

Of course, if I was doing this in my own database I would just go into the
table and make the change! But I want to provide her with a way to maintain
these lists without needing to know how to get into the back-end of the
database.

The simplest way I thought of was to add a new switchboard for maintenance
of lists. From here, she would get a selection of the lists available, and
could click to open the underlying table for a list, then she could delete
out the unwanted item(s). The obvious pitfall here is that if any of the
items she wants to delete are attached to other records, it won't allow her
to delete them - she would have to know how to find the other attached
records, update them to something else, etc. before she could delete the
unwanted item. This isn't rocket science, but I wondered if there's a better
way?

Does anyone have suggestions for how to allow users to perform this type of
back-end maintenance without needing to know a lot about Access?

Thanks,
Jen

 

[Allen Browne]

This article explains 4 ways to let the user modify the list of items in a
combo:
Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

Your suggestion of adding switchboard items to expose the lookup lists also
makes sense, though you probably want to provide a way to get there from the
combo itself as well.

The issue of not being able to delete items that are already in use is not a
simple one. In some cases you will have filter options on your form that
will let the user easily filter the records that use a particular look up
value. I tend to use this kind of interface:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Jen]

Thanks Allen - this is helpful. At least it seems I'm not crazy and that this
isn't necessarily a simple fix!

If anyone has suggestions of how they've done something similar, I'd love to
hear them!

Thanks,
Jen

 

[Albert D. Kallal]

Take a look at this screen shot:

http://www.kallal.ca/ridestutorialp/editlists.htm


Note how I have 8...count them 8 pick lists in this applicaton.

I am WAY too lazy of a developer to build 8 forms to manage this.

So, I built the above list box to select the "list" to edit
with the follwing collums:

ID ListDescription tableName DescriptionField

I assum that *all* of my pick lists are two collums (pk "id" + description).

Then, I simply launch the *same* form, but simply "set" the recordset of the
form...

DoCmd.OpenForm "frmEditPickList", , , , , , Me.listPickList

In the on open event of the form, I go:

Dim strSql As String
strSql = "select * from GuiPickList where id = " & Me.OpenArgs
Set rstParms = CurrentDb.OpenRecordset(strSql)

' set forms data source...

Me.Descript.ControlSource = rstParms!DescriptionField
Me.lblEdit.Caption = rstParms!ListDescription

strSql = "select * from " & rstParms!tableName & " order by " &
rstParms!DescriptionField
Me.RecordSource = strSql

So, I simply pass the table name via open args, and then open up a reocrdset
so I can get at the other field values (too hard to "pass" them all..so,
just pass the id of the table).

One form thus edits 8 pick lists, and I can add more pick lists without
having to write more code. And, you can re-use this for every application
you write. Huge time saver...

 

[Dale Fye]

I use a form similar to Albert's, but I also include a "Sort_Order" column in
my pick lists, so that I can change the sort order and not depend on an
alphabetical list. In many of my applications, I actually change this sort
order based on the most recent or most frequent selection(s) from the list.

The other thing that neither Allen or Albert mentioned is that deleting
items from these lists may create orphaned records in your datasets, or, if
you have relationships setup with cascading deletes, could actually result in
losing data from a more critical table.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[George Nicholson]

I usually have a Yes/No "Inactive" field in my static list tables. Rather
than deleting items from the list, they just get checked as Inactive.

I then incorporate 2 sets of queries for my combos:
- qcboList_Active (combo rowsource between Combo_Entered and
Combo_Exit)
- qcboList_All (combo rowsource all other times)

This limits what appears when the combo drops down to "Active" items, but
will still display "Inactive" items while scrolling through records.

 

[Albert D. Kallal]

The other thing that neither Allen or Albert mentioned is that deleting
items from these lists may create orphaned records in your datasets, or,
if
you have relationships setup with cascading deletes, could actually result
in
losing data from a more critical table.

Great point! In fact, if you setup RI with this, and no cascade deletes,
then you *can* safely try to delete..and if there are records that use the
pick list value...you not be allowed to delete.....

For a very long time I could not see any reason to enforce RI for those
general pick lists...after all, if you add new items, there really is not
any relational benefit here. However, enforced RI does give you a check at
deletion time...

 

[Bob Quintal]

I usually have a Yes/No "Inactive" field in my static list tables.
Rather than deleting items from the list, they just get checked as
Inactive.

I then incorporate 2 sets of queries for my combos:
- qcboList_Active (combo rowsource between Combo_Entered
and
Combo_Exit)
- qcboList_All (combo rowsource all other times)

This limits what appears when the combo drops down to "Active"
items, but will still display "Inactive" items while scrolling
through records.

And if you have a sort order column like Dale suggests, you can
filter on negative sort orders being inactive, which gives the best
of both worlds.