Trying to find a way to sort a continuous form from a column in one of the combo boxes. I can add an unbound field in the continuous form and call it TeamSortOrder and make the control source the second column of one of the combo boxes. For example, =[TeamFunction].[Column](2)
This unbound [TeamSortOrder] field does show the second column from that combo box, but I can't find any way to sort the continuous form by this field. This VBA isn't working.
I've got a table called CompanyTeams and one of the fields is called TeamFunction. I'd like to sort by TeamFunction, but not by actual name in that field. So I created another table called TeamSortOrder that has the TeamFunction as well as a numerical field called TeamSortOrder. So basically, I'd like to show the CompanyTeams in the Continuous Form but sorted by the TeamSortOrder table.
I've got a table called CompanyTeams and one of the fields is called TeamFunction. I'd like to sort by TeamFunction, but not by actual name in that field. So I created another table called TeamSortOrder that has the TeamFunction as well as a numerical field called TeamSortOrder. So basically, I'd like to show the CompanyTeams in the Continuous Form but sorted by the TeamSortOrder table.
Hi. Thanks for the additional information. So, let's try a different example.
SELECT Rank, LastName FROM Soldiers
Now, let's say a Lieutenant is superior to an Ensign or a Corporal, for example, that means we can't sort the Rank column alphabetically. Using your example, we may create a table with a sorting order for ranks. In that case, the above query might now look like this:
SELECT Soldiers.Rank, Soldiers.LastName FROM Soldiers
INNER JOIN RankSortTable
ON Soldiers.Rank=RankSortTable.Rank
ORDER BY RankSortTable.RankSort
To apply the above to your form, your combo column must contain the name of the field where you stored the sorting order for your team functions.
Well that works very well for the sorting - does exactly what I'm looking to do. But now I can't add a record or make any changes in the continuous form anymore. When ever I try to make a change, I get a tone. So looks like I'm going to have to go back so I can gain use of the form again.
Thanks.
Make sure in table TeamSortOrder that TeamFunction is a primary key. If not your query become non-editable. The tone is because you are probably getting a message saying that the recordest is not editable.
according to my notes, you can sort a form by the column of a combo that's not in the RecordSource using this for Order By:
[Lookup_ControlName].[ColumnName]
isn't Access totally amazing?
EDIT 13 July corrected the reference
WHERE
ControlName is the Name property of the control and "Lookup_" is added to the beginning of the reference
ColumnName is the field name or calculated field name in the SQL
It works! for form.OrderBy
You can also add DESC to sort in descending order, and combine the sort with other fields in record source, and even other lookup columns. For instance:
These examples use different columns of the same combobox named ContactID. Here is what it looks like:
And what isn't showing is the Bound Column, ContactID
Here's the SQL:
Code:
SELECT c_Contact.ContactID
, ([MainName]+", ") & [NameA] AS LastFirst
, ([NameA]+" ") & [MainName] AS FirstLast
FROM c_Contact
ORDER BY c_Contact.MainName, c_Contact.NameA;
In this example:
MainName is the Last name of a human or the company name of a company
NameA is the first name of a human (or, possibly if desired, the division name of a company)
the form itself is based on a cross-reference table between contacts and category with Long Integer foreign keys. There is an Ordr column when a contact is in more than one category, for order of importance
Here is the design view of the form:
Each of the orange command buttons in the form header run VBA to change the form's OrderBy clause and set OrderByOn to True. The last column is a textbox with an expression that displays a column from the ContactID combo box -- but still the form can sort by it!
Thanks for all the help.
I had originally created the table with an autonumber ID primary key. Deleting that field and making the TeamFunction the primary key fixed the non-edible issue. Now the form sorts perfectly and is editable.
Trying to find a way to sort a continuous form from a column in one of the combo boxes. I can add an unbound field in the continuous form and call it TeamSortOrder and make the control source the second column of one of the combo boxes. For example, =[TeamFunction].[Column](2)
This unbound [TeamSortOrder] field does show the second column from that combo box, but I can't find any way to sort the continuous form by this field. This VBA isn't working.
I just tested @LarryE's method and RunCommand acCmdSortAscending sorts a form by the first visible column of the combo or listbox. Therefore, @Danick, there is no need not to use the AutoNumber primary key and Long Integer foreign key.
If you want to sort by multiple fields, do them in reverse order
EDIT ... but there is a better way -- see next post
I modified my previous post about being able to sort a form by the column of a combobox ... my notes were almost right -- you CAN do it!
Now Post #9
shows you how to sort a form by any column of a combobox, not just the first visible column as the macro method with RunCommand does -- and you can add other fields to the sort.
Please do not structure the data improperly because you want the interface to sort a form by the column of a combo and you didn't know how to do that.
