Making list box editable after unchecking "Allow Full Menus" in Access options

[Shem K]

Hi guys.

On a pilot run, a user pointed out to me that they could not add items on a list box. Usually, I set my list box Limit To List data control to "No" and and Allow Value List Edits to "Yes", to allow the users control over the list items. However, these options are unavailable when I uncheck Allow Full Menus in Access Options (A practice that helps keep users from other Access menu items that would cause their curious minds to fiddle with some functions e.g. "unintentionally" editing table relationships).

The only way the List Boxes work is if the Allow Full Menus option is checked.

Is there a workaround on this? I only know of creating separate forms to contain and edit list items, but the List Box is an easier approach.

[Rabbit]

You could create buttons and put them near the listbox and code those functions yourself.

[Shem K]

Hi Rabbit. Thank you very much for your suggestion.

That's a viable option. However, I'm at the very shallow end when it comes to writing VBA. I however managed to look into another option.

I followed the procedure in the MS Access 2007 Help article "Customize the Ribbon" and managed to hide the crucial tabs while my list boxes remain editable.

Please see Microsoft's link:
https://support.office.com/en-sg/art...a-4e25bc9413de

Hope this helps anyone in a more or less similar pickle.

Kind regards,

[zmbd]

Shem did you try anything like this:

Private Sub Form_Load()
    With Me.Combo3
        .AllowValueListEdits = True
        .LimitToList = False
    End With
End Sub 

Where [Combo3] would be the name of your control. This should enable your users to edit and add to the listbox. However, I can't think of a reason to ever let an end user have free-range of a listbox... I'm sure there's a good reason. I have a double click event that opens an edit form to edit or add items to the underlying recordsource so that I can validate the entry.

[Shem K]

Hi Z.

Yes I have, but not in code. I used set the controls on Data tab of the Properties sheet of the list box to "Yes" for AllowValueListEdits and "No" for LimitToList.

I have a few list boxes that require editing by opening a form that feeds into their record source. In this case, Client Name is edited at the List of Clients form. The list boxes that I opted out of this have a less significant value, and I was trying to minimise the number of tables in the database, for instance, a list box for Currency that denotes the currency of the monetary (numeric) value of a record. I wanted to use data edit forms as a last option.