Changing a sorted Combo box value to be the first value

[southboy]

Hello,
I'm new to the Microsoft Access forum and I have a problem I could use some help on. It is probably a simple solution but I can't seem to get it. I have searched through out the forum with no luck.

I have a combo box that list record names and when you click on that record name, it brings that record up in another form. The list is sorted and the record name I click on the most is the last one in the list because it starts with a "W". Is there a way I can have that "W" record name show up first in the list but keep the rest of the list sorted as is? Changing the record name is not an option.

Thank you for help in advance.

 

[PHV]

Simply use a calculated field in the ORDER BY clause of the RecordSource's SQL code, like this:
ORDER BY IIf(Left([record name],1)='W',0,1), [record name]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[southboy]

PHV,
I tried your suggestion and changed the ORDERBY clause in the SQL of the query used by the record source and it gave me a prompt with the record name. After I click ok, it listed the records with no sort order and the "Work Trivia" record was in the middle of the list. There are other records starting with a "W", the "Work Trivia" record is the third "W" record and also the last in the 25 record combo box list.
Any other suggestions would be appreciated.

 

[PHV]

Any chance you could post the actual SQL code ?
You may try this (with your real field name):
ORDER BY IIf([name of field]='Work Trivia',0,1), [name of field]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

You could southboy, simply have your combobox, on FORM_Load & Combo_afterUpdate, equal that value.

ComboBox.Value = "Work Trivia", or whatever the bound column is, for that record.

Or a union query...


SQLW = "SELECT '0' As txtRecordName,'Work Trivia' As Record FROM tbl Records UNION
SELECT txtRecordName, txtRecordName As Record FROM tblRecords WHERE txtRecordName Not In ('Work Trivia') ORDER BY
txtRecordName"

You make the first column bound, but hide it..
ColumnWidths = 0";1.5"

cboRecords.Rowsource = SQLW

using this logic, you can still have any column bound you want, just keep the last 2 columns as is, for sorting & viewing.

 

[Dryheat]

How about setting the 'default' value of the combo box to "Work Trivia" from the properties window?

 

[southboy]

Thank you all for helping me out here.

Zion7, I tried the ComboBox.Value = "Work Trivia" in the FORM_Load & Combo_afterUpdate but it caused an error on the form load. I haven't tried the Union query yet, but I will.

Dryheat, I also tried your suggestion before I attempted the forum and all it will do is show that record in the combo box as the default, you can't click on it and when you bring the list up it is still listed as last. Thanks for your input though.

PHV , your suggestion worked perfectly. The "Work Trivia" record is now the first listed in the combo box and the rest are still sorted alphabetically after that. Its just how I wanted it. Thank you for that quick and easy solution.

Thanks again for all your help.
southboy

 

[SeeThru]

For the Combobox.Value, make sure you are setting it to the actual value saved in the combo box - you may have a hidden first column that is the actuall bound column.