How to sort combobox data on the fly?

[WannabePrgmr]

In Access 2003, I am trying to sort the combobox "Model". The "Model" rowsource is chosen based on the "Product Type" choice (i.e. Compressors, Actuators or Valves).

How can I code it so that each choice loaded as the rowsource will be sorted in ascending order.

I have the following code:

Private Sub Product_Type_AfterUpdate()
    Select Case Product_Type.Value
      Case "1. Compressors"
         Model.RowSource = "tbl1"
      Case "2. Actuators"
         Model.RowSource = "tbl2"
      Case "3. Valves"
         Model.RowSource = "tbl3"
    End Select
 
End Sub
 

So an example is if "Compressors" is chosen in "Product Type" and "Model" rowsource = the contents of "tbl1", how can I sort those contents???

I was blindly trying to add into the code something like:

Model.RowSource = "tbl3", SORT BY

But it errors out.

Thanks!

[leach613]

Are you trying to filter or sort one combobox based on the choice of another? Do you actually have 1 table or a table for each product type?

[WannabePrgmr]

There is a table for each product type. So the case statements pull the correct table for "Model" whenever the product is selected in Product Type.

I tried to sort the tables themselves and they do sort in the table view, but not when they are listed in the combobox when they are called.

Thanks

[Jim Doherty]

@WannabePrgmr
A Listbox can be populated by an SQL statement that has an ORDER BY clause you use a field name of your choice in the table to sort by

This will sort ascending

Model.RowSource ="Select tbl1. * FROM tbl1 ORDER BY tbl1.YourChosenFieldname ASC;"

This will sort descending

Model.RowSource ="Select tbl1. * FROM tbl1 ORDER BY tbl1.YourChosenFieldname DESC;"

Obviously my reference to 'YourChosenFieldName' should be the actual name of the field in your table that you intend to sort by

Look at queries to see how they work and then look at how listboxes are populated conventionally using queries or SQL statements. You will realise that this can be controlled in code anyway you wish by manipulating the SQL string

[NeoPa]

May I say that this problem is a result of non-normalised data. It is not recommended to have separate tables for storing data simply dependent on the values in the data. See Normalisation and Table structures.

With a properly designed database such filtering can more easily be managed by the filtering facilities provided. For more on this see Example Filtering on a Form.

[WannabePrgmr]

Thanks Jim Doherty! That did it perfectly! I appreciate the help very much.

NeoPa, thank you for your input. I am learning on the fly and very much jumped in head first so there is a lot I have to learn to correctly set these databases up.

[NeoPa]

You're welcome, and it's fine to ask for guidance after jumping in at the deep-end. We're always happy to help.

One thing I would stress though, is that the first link is a must-read for anyone in your position. You may (or may not actually) find it strange at first, but it is such good grounding for database comprehension that you won't regret any effort put into taking this on board.

Enjoy the process :)