Alphabetize Drop Down List? 2

[Quan9r]

I need to alphabetize a drop down list. The query is sorted A-Z but when you open the form with the drop down list the names are not listed alphabetically.
I'm sure it is a simple solution. I can't figure it out though
Any assistance would be appreciated.

 

[lwilly]

You should be able to go into the row source property and specify ascending or descending.

 

[Rohdem]

Just to expand a little on will5's post, you need to go to the row source property of the combo box. It will either contain an SQL statement or a query name. Either way, if you click on the '...' next to the row source property, you can edit the query that is used by the combo box. Make sure the sort on the query is set to ascending. This will alphabetize the combo box.

Mike Rohde
rohdem@marshallengines.com

 

[LarryDeLaruelle]

An add to Quan9r's question.

Once the sort order has been modified, how do you get the form to display the first record of the new order?

I have a form set up with the combo sorted but the record displayed is the first record in natural (PK) order and the combo box is blank.

I've tinkered with the combo's after update event and with the form's on current event but can't seem to find the right syntax.

Thanks.

Larry De Laruelle
larry1de@yahoo.com

 

[Rohdem]

If I am understanding you correctly, you are allowing the user to modify the row source of the combo box in some way and you want the combo box to reflect this modification. Try:

forms!comboboxname.requery


You can also leave the combo box unbound and set the row source using the 'on enter' event of the combo box...

me.comboboxname.rowsource = qryname or
me.comboboxname.rowsource = "select * from customers"

Mike Rohde
rohdem@marshallengines.com

 

[LarryDeLaruelle]

Not quite. The only thing the user can do with the combo box is select a record. The combo was created with the wizard using the same table the form is based on. Here's the code.

Sub cboSelRes_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ClientID] = " & Me![cboSelRes]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

After creating it I opened the RowSource and added an ascending sort on name.

When I open the form, the combo is empty (no name displayed) but the remainder of the fields on the form reflect the information of the first record in the table in it's natural, indexed order.

What I would like to do is have the combo box show the first record in the ascending sort order and the information on the form be the information for that record.

Hope that makes it clearer. Sometimes trying to explain a problem is more complicated that the problem itself.

Thanks.



Larry De Laruelle
larry1de@yahoo.com

 

[Rohdem]

Is the combo box bound to a field?

Mike Rohde
rohdem@marshallengines.com

 

[LarryDeLaruelle]

No it isn't. Is this a clue?

Larry De Laruelle
larry1de@yahoo.com

 

[lwilly]

With the combo box not bound it will not display a value until one is selected. I assume that the other fields probably are bound controls so they will display the first record.

 

[Rohdem]

I think what you may need to do is create a query that is the record source for your form and has the sorting that you want to use (the row source for your combo box should be the record source for your form).

Mike Rohde
rohdem@marshallengines.com

 

[LarryDeLaruelle]

Well, perhaps I spoke too soon. The combo box has two columns: Column 1 is bound to the key field of the table; column 2 displays the resident names for the user to select.

And, yes, all the other fields are bound to the underlying table.

Is it possible to set the combo to a bookmark(?) property in the form's on open event?

Would I need to open a temporary record set (containing the PK and Name fields), sort it by the name field, move first and then assign the combo the value of that first record's PK?

Or am I trying to make this more complicated than it needs to be?

Larry De Laruelle
larry1de@yahoo.com

 

[Rohdem]

I think you may be confusing row source and control source. What I meant when I asked whether or not the combobox was bound was does it have a field listed in its control source property which is under the 'data' tab? It is probably empty.

If you create a query that is based on your table and sort it by name, then make the form bound to the query, it should display the first record like you want it to. I assume you have a text box on the form that is bound to the field 'name' in your table. If this is the case, then it really doesn't matter if your lookup box is blank when the form opens.

Mike Rohde
rohdem@marshallengines.com

 

[LarryDeLaruelle]

Mike:

I solved the problem by basing the form on a query (instead of directly on the table) sorted in ascending order.

Now the form and the combo box are syncronized to display the first record in alpha-name order rather than by natural order.

Thanks.

Larry De Laruelle
larry1de@yahoo.com