Sorting by combobox problem

[wsurritte]

I'm having a little sorting problem that I would really appreciate some help with: I have a datasheet form on tbl_clocks, with a combo box on the field location_id. That combo box will display the actual location from qry_vision_company_location; no real problem there. The problem that I'm having is I want to sort the datasheet by the location name, and if I do the sort in the record source query, it sorts by the ID instead of by the name. The only way I've found around this is to click "ascending" once the form is already open, but that is not a good solution.




I would really appreciate any suggestions on resolving this.

Thanks,
William

[RuralGuy]

Are you using Lookup Fields per chance? http://www.mvps.org/access/lookupfields.htm

[wsurritte]

No, I'm storing the ID in the table, and just setting a Row Source property on a combobox in the form. i discovered once using PHP that Access stores a string when you use the lookup wizard, and thus the performance is terrible, so I quit using the lookup wizard.

[RuralGuy]

If you join the two takes in the RecordSource query then you can sort on any field in either table without actually displaying the field.

[wsurritte]

@RG: I just tried your suggestion; it still is ordering them by location_id even though I listed the name field as ascending.

I'm not telling it anywhere to order by that id; I haven't the slightest clue why it's doing this.


Thanks,
William

[RuralGuy]

When you open the Query directly in design view and go to Datasheet view, does it sort correctly? Are you using a static or dynamic query for the RecordSource of the form?

[wsurritte]

No; the query is sorted by the ID there too. Forgive my ignorance, but I don't know the difference between a static query and a dynamic query; if you are meaning a named query under the query's section on the left, then no - the query is just the record source of the form.

Thanks,
William

[RuralGuy]

Save the query as a named query. Then you can play with it better and YES, an SQL statement as the RecordSource of a form is a Dynamic Query and not quite as efficient as one that has been saved under its own name which is then static. Does the query show sorting on the ID value?

[wsurritte]

RG,

After saving it as a static query, `location_id` was sorted asc, so I joined it to the other table, and created the sort field, and now I can sort by location name. If I want to "sub-sort" by say `serial_number`, I would simply need to set it to Ascending also right? I was thinking that access would sort by the order you select the fields to sort.

Thanks so much,
William

[RuralGuy]

The SubSort order is the left to right order of the fields in Design view of the query.

[wsurritte]

RG,

Thanks so much for your assistance, I really appreciate it. My access database is now cooperating

Best,
William

[RuralGuy]

Excellent! Glad we could help.