"Sort by" issue

[Ashley]

Hello, I am an inexperienced user of Access, and I am building a database. I
have created a combo box for a field in which I would like the resulting
input into the field to be Agent ID numbers.

When you click on the dropdown for the single column, several columns pop up
from a different table, and the fields are as follows: ("SID", "AgentLast",
"AgentFirst", "SupervisorID", "SuperLast", "SuperFirst", "Site")

I have ordered this list by Agent last name, since we most likely will have
the SID (agent ID) and can type it in manually. If we need to look up the ID
but have the person's name, the list of agents is in order by last name.

My problem is this: I need to group the agents by site. For instance, agents
in "AZ" will be listed in alphabetical order by their last name, then agents
in "FL" will follow, etc.. Basically, I need the list to sort ascending by
site, then by last name, but once something is chosen from the list, the
resulting input in the field should be the SID.

Is there a way to do this without changing the order that the information
appears when you click on the dropdown? I noticed when I made the site the
first column, it would sort the way I wanted it to, but it would try to
populate the field with the site.

I hope I was clear. I feel like this is a simple problem to fix, i'm just
missing something.

Thank you for your help! Any suggestions appreciated!

 

[Dirk Goldgar]

Hello, I am an inexperienced user of Access, and I am building a database.
I
have created a combo box for a field in which I would like the resulting
input into the field to be Agent ID numbers.

When you click on the dropdown for the single column, several columns pop
up
from a different table, and the fields are as follows: ("SID",
"AgentLast",
"AgentFirst", "SupervisorID", "SuperLast", "SuperFirst", "Site")

I have ordered this list by Agent last name, since we most likely will
have
the SID (agent ID) and can type it in manually. If we need to look up the
ID
but have the person's name, the list of agents is in order by last name.

My problem is this: I need to group the agents by site. For instance,
agents
in "AZ" will be listed in alphabetical order by their last name, then
agents
in "FL" will follow, etc.. Basically, I need the list to sort ascending by
site, then by last name, but once something is chosen from the list, the
resulting input in the field should be the SID.

Is there a way to do this without changing the order that the information
appears when you click on the dropdown? I noticed when I made the site the
first column, it would sort the way I wanted it to, but it would try to
populate the field with the site.

I hope I was clear. I feel like this is a simple problem to fix, i'm just
missing something.

The columns that are available to display in the combo box are determined by
the control's Row Source property (on the Data tab of its property sheet)
and its Column Count property (on the Format tab). If the Row Source is a
table, then all the fields of the table are potentially available; if the
Row Source is a query, whether a stored query or an inline SQL statement,
then the fields selected by that query are available. The Column Count
property tells how many of those fields will be columns in the combo box --
it will take the first [Column Count] fields to be the columns.

However, not all of those columns may be visible. The combo box's Column
Widths property (on the Format tab) tells how wide each column is. A column
whose width is 0 is still present in the combo box, but invisible to the
user. This is very handy. The value displayed in the combo box when it
isn't dropped down will always be the first *visible* column, based on the
column widths specified. When the user types in a value, this is also the
column to which the user's entry is matched.

The value that gets stored in the combo box's Control Source field, the
field to which the control is "bound", is determined by the control's Bound
Column property (on the Data tab). So if your first column is SID, and you
want to store SID in the bound field, then you set the Bound Column property
to 1.

In your case, as I understand it, you want the users to type in SID, so that
must be the first visible column. And you want that to be the value that is
stored in the bound field, so that column must be the Bound Column. But you
want to sort the dropdown list in a more complex way, so you need to set the
combo's Row Source to a query that sets it the way you want. I suggest
these properties:

Row Source:
SELECT
SID,
AgentLast & ", " & AgentFirst As AgentName,
SupervisorID,
SuperLast & ", " & SuperFirst As SuperName,
Site
FROM
YourRowSourceTableName
ORDER BY
Site, AgentLast, AgentFirst;

Bound Column: 1
Column Count: 5
Column Widths: .5"; 1.5"; .5"; 1.5"; 1"

In the Row Source property, you'll need to replace "YourRowSourceTableName"
with the name of the table from which your agents are drawn, and the whole
SQL statement will really need to go on one line in the property sheet -- I
just formatted it onto multiple lines for clarity.

I combined the agent and supervisort names into calculated fields in
"lastname, firstname" formats, for simplicity. If you need those individual
columns in the combo box for some other purpose, you'll have to change that.

The Column Widths property has only example widths. You'll have to see what
widths work well for you.

 

[Fred]

Dick,

Three of those paragraphs are the most succinct, informative, to-the-point
thing I have ever seen written on combo boxes.

 

[Dirk Goldgar]

Dick,

Three of those paragraphs are the most succinct, informative, to-the-point
thing I have ever seen written on combo boxes.

Thank you very much, Fred. Sometimes I can't tell if my explanations make
sense to anyone else. <g>

 

[Armen Stein]

Dick,

Three of those paragraphs are the most succinct, informative, to-the-point
thing I have ever seen written on combo boxes.

I'll second that - it's a mini-tutorial on comboboxes right there.
Nice job, Dirk!

Armen Stein
Microsoft Access MVP
www.JStreetTech.com