If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Sort by" issue
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! |
#2
|
|||
|
|||
"Sort by" issue
"Ashley" wrote in message
news 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. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#3
|
|||
|
|||
"Sort by" issue
Dick,
Three of those paragraphs are the most succinct, informative, to-the-point thing I have ever seen written on combo boxes. |
#4
|
|||
|
|||
"Sort by" issue
"Fred" wrote in message
... 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 -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#5
|
|||
|
|||
"Sort by" issue
On Fri, 13 Nov 2009 11:43:01 -0800, Fred
wrote: 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 |
Thread Tools | |
Display Modes | |
|
|