Subform combobox sorting question

[Ms Kathy]

I have a combobox on a subform with a recordsource from a query. The query includes 2 tables - Toppings and Toppings_Items. I am assigning multiple toppings (subform) to an item on the form. The hiccup is that I can't get the combobox to sort by the part/topping description. It is sorting by the part#. This is not very helpful during data entry because we don't know the part #s, rather we are familiar with the description (although we want to see the part number). I have tried sorting the query by description field; and I found something online that suggested using "order by" in the record set - I also tried this. I just can't seem to get it to sort by this field. Note that the description field is the only field coming from a the Toppings table, all the other fields in the query are from the Toppings_Items table. The tables are linked.

I using Access 2007. And the other users of the database are using Reader. I've attached an image of the subform. Any help would be appreciated. (I am very much a beginner.)

 

[shoji]

Open the property sheet of the combo box and find "Row Source". You will see the query name there. Instead of the query name, write

SELECT * FROM qryMyquery ORDER BY Descriptions;

where "qryMyquey" is the name of the query. This should take care of the sorting.

Shoji

 

[Ms Kathy]

It says the record source does not exist. So I tried placing brackets? Still get the same message . . .

 

[shoji]

Hmm. How do you get the data for the combo box?

 

[Ms Kathy]

Well, I looked at that Record Source property before I changed it and it referred to the table. So I tried using the line you gave me but changing the name from the query to the table. Is this what you're asking?

 

[shoji]

Yes.

If you put the table name back, the combo box shows the list of the table, right? But which field would you like to show in the combo? So, if you want to show "Description", just replace the table name with

SELECT Description FROM MyTable ORDER BY Description;

 

[shoji]

Oh, if the combo box's value must be PartID, then you should write

SELECT PartID, Description FROM ...

and on the combo property sheet, make sure of the following:

Bound Column: 1
Column Count: 2
Column Widths: "0;2" 2 is the width of the column, just as an example.

 

[Ms Kathy]

I'm a little lost. Trying to remember how I did this. I've attached images of the combobox on the form; and also the properties of the field.

 

[Ms Kathy]

I finally found the answer!:

http://answers.microsoft.com/en-us/...-a-forms/f98d4f8c-23d0-488e-a6cb-089a85041eeb