combo box question

[fingermouse]

Hi,

Ive only been working in Access for a week so please be gentle

This will probably be a silly question but here goes.....


Im trying to create a search filter using a combo box in my access form.


I have a table with multiple records. One of the table fields is called 'report type'.


This field has 3 distinct categories across 280 records - 'internal', 'risk based' and demand led'.


However, when I try to create a combo box using the wizard, The drop down list provides 280 options, which is the same as the total amount of records.


How do I get my combo box to only have 3 options, whereby selecting one of the categories will return all records per category?


Thanks,
Cal

 

[welshgasman]

Typically the categories would be in a table of their own, so 3 records.
The combo would select from that and then populate the ID (not the text) to your table.

However in your situation, change the 'Select' to 'Select Distinct' providing that is the only field you wish to show.?

Then you would filter on whatever the category is.

https://docs.microsoft.com/en-us/office/vba/api/access.form.filter(property)

 

[Norie]

Cal

Go into the properties of the combobox and find the RowSource property.

You should see a query there, something like this probably.

SELECT [Report Type] FROM [ATable] ORDER BY [Report Type]

To do what you want you need to change that query to return unique values for the [Report Type] field.

You can do that by adding DISTINCT.

SELECT DISTINCT [Report Type] FROM [ATable] ORDER BY [Report Type]

 

[fingermouse]

Thanks for replying all, much appreciated.

So this is what I get in the row source property box using the wizard to create the combo box:

SELECT [independent Assurance].[ID], [independent Assurance].[report type] FROM [independent Assurance];

If I add in the word DISTINCT:

SELECT DISTINCT [independent Assurance].[ID], [independent Assurance].[report type] FROM [independent Assurance];

I still get 280 options in the dropdown? I dont know why its putting the ID field in there, as im not selecting this field in the wizard.

 

[Norie]

If you don't want/need the ID field remove it from the query.

SELECT DISTINCT [independent Assurance].[report type] FROM [independent Assurance] ORDER BY [independent Assurance].[report type];

P.S. You can alter the query, and look at it results, in the QBE view by clicking the little ellipsis (...) to the right of the RowSource property field.

 

[fingermouse]

If you don't want/need the ID field remove it from the query.

SELECT DISTINCT [independent Assurance].[report type] FROM [independent Assurance] ORDER BY [independent Assurance].[report type];

P.S. You can alter the query, and look at it results, in the QBE view by clicking the little ellipsis (...) to the right of the RowSource property field.

Thanks Norie - I copied this exactly into the rowsource properties box:

SELECT DISTINCT [independent Assurance].[report type] FROM [independent Assurance] ORDER BY [independent Assurance].[report type];

But now the combo box has no options to choose from in form view.

When I click the (...) and click 'run', it is returning the 3 options only in the query builder table, but this isnt pulling over into the combo box.

 

[welshgasman]

When you use the wizard it selects the ID (key) and the description for the combo.
If you are now going to use the description, that field cannot be used for text.

What I would do in your case is
Create a table tblCategory
In it have a fields
CategoryID AutoNumber
CategoryDesc Text (50)

Then have your select as before just a little different
SELECT CategoryID, CategoryDesc from tblCategory ORDER BY CategoryDesc


HTH

 

[fingermouse]

Thanks welshgasman - would that mean everyime I wanted to create a combo box, it would need its own table?

 

[welshgasman]

Not necessarily.

I have created a general lookup table in the past, that holds different types like
Email addresses
Categories
DayNames

You would then need a field for the type of lookup, perhaps a field for numeric values rather than text. It can be simple or as complicated as you like. The more generic, the more complicated I would say.? Perhaps one table for text values and one for numeric values.?

If you wanted a combo for say customer names, then you would use the customer table, so no extra table required there. Do you see the logic.?

As you are starting off, I would keep it simple and have separate tables. Nothing wrong with that.

However if you are absolutely sure, those are only the three you need, you can hard code them in the combo properties. the problem with that is if you need a 4th, you need to change the programs that use it.?

Using tables, the app is 'data driven' and it becomes easy to add new categories. You might still need to change code though, if the 4th category requires something completely different in the way of processing.?

I would be looking at the Northwind sample database to see how this all works.?

 

[Micron]

FWIW, you can create a query for your combo that works, then either
- copy/paste the sql (from query sql view) into the rowsource property for the combo (in its property sheet), or
- just select the query as the combo's row source. Make sure you understand the bound column property for when there is more than 1 field being shown in the combo. Same holds true for list boxes. Also, review DISTINCT and DISTINCT ROW, because when returning more than one field, you might need one over the other. You cannot use both at the same time. You'll find these in the property sheet of a query as "Unique Values" and "Unique Records".