Sort Combo lookup box

[tmulrooney]

I have a form with a combo box at the top. This box has two columns. Next to the combo box I have an option group. The option group has two choices. Option 1 (By Name) Option 2 By Number. I would like for one of this to be default sort order. I would like the user to be able to click the other choice, which then changes the list order of the combo lookup. Does this make sense or is there a better way?

Thanks,
Tim

 

[dcx693]

It makes sense. You can set the default value for your option group either through VBA code or more simply in the control's property sheet in design view (look under the "Default Value" property on the "Data" tab).

You will then need some code in the option group's After Update event to change the sort order of the combo box. Unfortunately, combo boxes are not easily sorted. They do not have an "Order By" property like forms do.

There are at least two ways to sort them though: (1) Have the box's Row Source Type set to "Table/Query". Have the "Row Source" be a SQL statement. Based on the option group selection, append or change the ORDER BY portion of the SQL statement feeding the Row Source.
(2) Again, have the box's Row Source Type set to "Table/Query". But this time have the "Row Source" be a stored query with a field that references your form's option group. Then based on what you choose in the option group, it will control the sort order of the query.

 

[tmulrooney]

I kinda understand the two options. Can you give me a little more detail on the first option. The option gives a value of 1 for by number and a value of 2 for by name. So How do you in the source line change the order by property?

This is my current source text:
SELECT DISTINCTROW Projects.ProjectID, Projects.ProjectNo, Projects.Projectname FROM Projects ORDER BY Projects.ProjectNo

What would I change? My option group is called sorting, if you need that info.

Thanks.

 

[dcx693]

OK, as you can probably guess, the part of the SQL statement that controls order is the part that says:
ORDER BY Projects.ProjectNo
You will need to edit that part in order to have it say what you want.

In VBA code, you get the row source of a combo box by saying something like:
strSQL=Me.cboProjectID.Rowsource
That assumes your combo box is called "cboProjectID".

Then, based on the choice in your option group, you take a look at the strSQL and adjust it accordingly. If it currently says SELECT DISTINCTROW Projects.ProjectID, Projects.ProjectNo, Projects.Projectname FROM Projects ORDER BY Projects.ProjectNo , then you can edit the ORDER BY statement to be ORDER BY Projects.Projectname using the typical VBA string manipulation formulas like Instr(), Replace(), and/or Mid().

Once you have an updated strSQL with the correct sorting in it, assign it back to the combo box rowsource like this:
Me.cboProjectID.Rowsource=strSQL

 

[tmulrooney]

I think I am understanding it. So my code has told my combo box, go get the data. Then it says to pull my data in the format it is defaulted to, as in Order By Projects.ProjectsNo

Now I am going to insert a line that says something like:
My user has check the option group option two. The option group then stores a value of "2". I need a statement that says If 2 , then Replace Projects.projectsno with Projects.projectname?

Then end it with the code that returns it back to the combo box.

Do you know on the net where I can read about the VBA manipulation formulas and how to use them?

THanks.

 

[dcx693]

You've got the right idea!

You can start looking through the built-in help for Access to get some info about VBA coding. I really don't know of any good sites for getting started with VBA, though there are a few with advanced tips. Search the forum and you might turn up something.

Write back if you need more help.

 

[Pat Hartman]

In order for the combo to expand as you type, you need the rowsource to be ordered by the first visible field. So, if your first visible field is the ID, then the rowsource query should order by id. If the first visible column is the name, then the rowsource query should order by name. So in addition to modifiying the rowsource, you would also need to alter the columns property to control which column is visible when the combo is closed.

I would (and have) just use two separate combos. The user choses to use one or the other depending on which piece of data he has.