Create a Selectable List for a Combo Box.

Question

In am in the process of creating an Access 2016 database. In this database I am creating a form with a pull down menu to select items. Once the item is selected it will create a query based on the item selected. The pull down menu is getting its list from a query which the data cannot be changed and the reason for this is it bringing up a list of selected items on a table (each item is used in the table several times so the list is created to show each one individually). The problem is that if you create a combo box or list box from a query that does not allow changes to the data you are not allow to select anything in the box (all the items will show up but you cannot select any of them). I cannot use the main table as each item can be shown thousands of times which would make the box completely unusable. 

Anyone have any ideas how to resolve this issue?

Answer 1

Hi Thomas, I think the root of your problem is that it is not correctly set up to begin with. You should have an Item table with ID. It should hold unique items. The ID for the item can then a field in your main table.

The 'pull down menu' (I'm guessing you mean combobox) record source should be the item table

Answer 2

Hi Thomas, I'm an independent adviser and will try to help.

You are doing something wrong because that is not true. A combobox should have its Controlsource as a field in the form's Recordsource. I've done what you described many times. Let me give you n example.

Lets say you have a field in your Product table for a Category. Now lets say, your list of Categories uses a query like this:

SELECT DISTINCT Category FROM Products ORDER BY Category;

Now the ControlSource of this will be Category field in the table. There should be no reason you can't select a category from the Combobox.

BTW that would not be good design. You would have a lookup table for Categories instead.

To be able to help you I would need too know the Recordsource of your form, the Controlsource of the combobox and the Rowsource of the combobox.

If you need more help please supply that information.

Answer 3

I figured it out. 

The Combo Box or List box must have the Row Source field set up as Select.

Example: SELECT [qry_Non_Input_Game_List].[Game Field] FROM qry_Non_Input_Game_List ORDER BY [Game Field]; 

If you set it up to directly access the query, it will not allow you to select anything as no changes can be made to the query.

Answer 4

you could use:

select DISTINCT somefield from sometable

This would only get unique values.