Open the Edit List Items Window with a button in Access for ListBox Object

2
votes

I have a List Box in my form.

Listbox

I'm allowing the user to edit the contents which is a feature I'd like to have and the default feature works great for my needs. I know if you right click the list you get the Edit List Items Window too.

Edit List Items

But my issue is most of my users are not Access savvy, so they may not know to right click to open the window. I'd like to make it so the blue edit button will open that window with VBA, but I can't figure out how to call opening that window with VBA.

Is this even possible, if so I'd love to know the call.

1
ms-accesslistboxvba
Users can be trained. You could have a label with bright red text on form that informs users. – June7
It's clear that the default behavior is sufficient, but you probably won't find a direct call to make the pop-up window show. It is possible however to define your own pop-up (and optionally modal) form and set it as the ListItemsEditForm. Then you have complete control including the ability to show the form with the click of a button or other action. – C Perkins

1 Answers

0
votes

You can simulate the click on edit

Private Sub btnValueListEdit_Click()
    Me.myListBox.SetFocus
    DoCmd.RunCommand acCmdEditListItems
End Sub

But this is not recomended!

Have a look at Add items to a Value List from Allen Browne.

  • You can remove items that are actually being used in other records.
  • You can correct a misspelled item, but the records that already have the misspelled item are not corrected.
  • You can add items to your form, but in a split database, other users don't get these items. Consequently, other users add items with other names to their forms, even where they should be the same item.
  • If you answer No after using one of the new items, you now have items in the data that don't match the list.
  • If you answer Yes in an unsplit database, you introduce strange errors as multiple users attempt to modify objects that could be in use by other people.
  • If you answer Yes in an split database, the list of items in one front end no longer matches the lists in the others.
  • Your changes don't last anyway: they are lost when the front end is updated.

Conclusion

  • Use tables, not value lists, to manage lookup data. Create relationships with Relational Integrity.
  • Use these lookup tables (or queries based on them), as the RowSource for your combos. Do not use Value Lists for anything more than the simplest of choices that the user never needs to edit.
  • Use the Not In List event to add data to simple, single-field lookups such as types or categories.
  • If you only use Access 2007 or later, the List Items Edit Form property is a quick and easy way to nominate the form to use for managing the list items.
  • To edit the list in any version of Access, or to control how the editing works, use another event such as the combo's DblClick.