We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Access ADP Form Sort on ComboBox Text

bo_dong
bo_dong asked
on
Medium Priority
1,170 Views
Last Modified: 2012-08-13
Here is BenchmarkCategory table. Notice the BenchmarkCategoryName is not sorted alphabetically when sorted by BenchmarkCategoryID.
 
BenchmarkCategory Table  

Here is another table Benchmark with a BenchmarkCategoryID column. I built a form on the table and added a ComboBox displaying the BenchmarkCategoryName. Because users would like to see Names instead of IDs.

Benchmark Table Sorted on ID  

My question is how to sort the ComboBox column based on the names, not on IDs? So it looks like the below picture, not the above picture?

Benchmark Table Sorted on Name  

I did the above picture by changing the RecordSource on the Benchmark table to

select * from Benchmark c order by (select p.BenchmarkCategoryName from BenchmarkCategory p where p.BenchmarkCategoryID = c.BenchmarkCategoryID) desc

But this hack doesn't work in SubForms. Using Profiler, I saw ADP sending broken queries to SQL Server.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Try this as the RecordSource:
select Benchmark.* from  Benchmark c left join BenchmarkCategory p on p.BenchmarkCategoryID = c.BenchmarkCategoryID order by p.BenchmarkCategoryName

Author

Commented:
My question is how to make picture 2 look like picture 3 without changing RecordSource?
CERTIFIED EXPERT

Commented:
Did you try the Sort propery of the subform. Set it to:
(select p.BenchmarkCategoryName from BenchmarkCategory p where p.BenchmarkCategoryID = c.BenchmarkCategoryID) desc
If that doesn't work I'm afraid I'm out of options. If Access send wrong queries to SQL Server I'm afraid it is a bug in Access and that might be quite hard to work around.

Author

Commented:
Howdo you set Sort property on the subform? Using OrderBy? Can you write SQL query in there? Using RecordSource? I already know that. Maybe RecourdSource is the only way. Thanks for trying.
CERTIFIED EXPERT

Commented:
Yes sorry. The property is called OrderBy. There is also OrderbyOn and this has to be set to true. But I think you can only set the OrderByOn through VBA. I always thought this was very confusing so I always ended up setting the entire RecordSource. You'll enter the part of the SQL that you would normally enter after the "ORDER BY" clause in the SQL (without the Order By part).
CERTIFIED EXPERT

Commented:
Hi,

I would recommend that you do not use the Filter or OrderBy properties of Access forms in an ADP. Both properties results in getting all the data from server and then doing the filtering and ordering in the local machine. That causes big performance disadvantages and if you are in a continous form with many records (for example more than 50,000) it sometimes crashes Access completely.
You are in an ADP - so you should always use the power of SQL Server which is always faster in filtering and sorting. Try to use ServerFilter (And ServerFilterOn) in forms and ORDER BY only on the server. So create a view on the server which does the sorting (in SQL Server 2000 add "TOP 100 PERCENT" to create a view with ordering, since SQL Server 2005 you need an absolute value, like "TOP 1000000" or some similar expression). Instead, you can also use stored procedures which don't need the TOP clause, but with stored procedures you cannot use the ServerFilter property in Access forms.
You should never use any direct SELECT command like changing the RecordSource with a VBA SQL string or something like that - this produces always dynamic SQL which is not so fast like saves views/SPs on the server. (By the way: You should also never use "SELECT *", create views which only contains exactly the columns you need for your purpose, no more.)
Next thing is that you don't need such hack - simply JOIN the two tables together so the text column is added to the query result, then you can order by this column. In Access forms you can add a resync command (like "SELECT * FROM MyTable WHERE MyIDColumn=?" (here "*" is OK and needed by Access, also the question mark) so Access can synchronize the data correctly, and you can also specify the name of the table which should be updated with the form (because you have more than one table). It normally should also work if you add both columns, the ID column and the text column of the foreign table to the view to keep it updateable.

Forget the things you learned with MDB/ACCDB, in an ADP you must always think of "SQL Server rulez", forget to manipulate RecordSets with VBA, use a stored procedure on the server instead, forget to write SQL commands in VBA strings, use stored procedures with parameters or views if you don't need parameters. Switch a form between stored views of the server, if needed, don't change RecordSources or RowSources with VBA SQL strings. You have a SQL Server now, don't waste the time with letting your slow frontend do the job, let the server do the job, it's always faster.
If you need a parameter based on a control on the form, use the property "Input Parameters" of the form and set the RecordSource fix to a stored procedure.

Cheers,

Christian

Author

Commented:
Christian,

Thank you for the lengthy reply. I agree with your general observation to fully utilize SQL server and use Stored Procedures whenever possible. I spent 99% of my time in SQL server and C#. That’s why VBA and Access is all foreign and a step backward to me.

Back to my example, Benchmark table may have many columns with IDs, like BenchmarkCategoryID, TypeID, CountryID, MarketID… Each ID column has a table with lookup names. The form has a ComboBox displaying names for each of the ID columns. User can sort on any ID columns and expecting it sorted alphabetically, but will be surprised to see the sort is on the underline ID. My solution to this user request is to build a SQL string sorting on the selected column names and feed that to the form RecordSource. Remember this feature needs to work on any table with ID columns, not just on one table. So creating views with all look up tables joined together for each of hundreds of tables in the database is not an optimal solution.

Any better ideas?
CERTIFIED EXPERT

Commented:
Hi,

you're right, from this view VBA IS a step backward (if not more than one step...:-)).

The problem is that Access was mainly made to work with MDB/ACCDB and Access databases. If you create a lookup table using ACCDB and try to do the same, the sorting will be based on the displayed value and not on the underlying ID. This also works if you create a linked table to SQL Server tables and lookup comboboxes.
Unfortunately MS seem to think that ADP is not worth to develop with such comfort, so here you must do it on your own. But this is not so bad as it sounds because you are not forced to use the Access methods of filtering and sorting because they are really not stable in ADPs, I often had problems with these functions and I can only again recommend not to use them. Try opening a continous form with 100,000 records and then (while the record navigator doesn't show the complete number of records) immediately try to filter or sort data. You will get an Access crash in some cases, depending on network speed, size of table and data and so on.

You now could create a SQL string which changes the RecordSource always the sorting is changed (with an ORDER BY hack like you used above) and this would surely work but I would recommend the following way:
1. create a stored procedure which contains a parameter to select the column you want to sort (the column name as string or a column count, whatever you like).
2. enter an IF ELSE... construct to choose the right SQL string depending on the parameter into the SP.
3. create one SQL command per ORDER BY column with the right ORDER BY clause. DON'T use a SQL string to assemble and execute with dynamic SQL because in this case you will lose the performance advantage of SQL Server optimization. It looks ugly to have 20 SQL strings with only different ORDER BY clause and our programmer's heart wants to create a universal solution but in SQL Server this is really the better way.
4. disable "Allow Filter" on the Access form, this also disables the sort menu as it is in the same menu. Add other possibilities to the frontend like one combobox to select the sort column or a button on each column header to set the ordering - whatever you find more comfortable in your case.
5. choose the stored procedure as RecordSource and use the property "Input Parameters" to set the parameter for the stored procedure. The easiest way is to use a VBA function or a hidden field or (in case of the order combobox) the value of the combobox. For example, if you used a column count as parameter and an order combobox this would be:

@intColumn int=Forms!MyForm!MyCombobox

Open in new window


In the "AfterUpdate" event of the order combobox the only code you need would be "Me.Requery".

Of course, this solution allows only one column to be ordered, you cannot create nested orders with this - but that Access also doesn't allow with the standard order menu, it also can only sort on one criterion.
This will of course also disable the possiblity to use custom filters on the form for the user. You can also don't use the ServerFilter property which is much faster because it only works with views and not with stored procedures.

If you want to use custom filters AND custom ordering the only (performant) way I see would be to create 20 views for the same table with 20 different ORDER BY clauses, switch to the right view by changing the RecordSource to the wanted view and using ServerFilter property on the selected view. This creates a lot of additional views and if you ever must change anything in the view you will also have to change all the 20 (less or more depending on your sort columns) views all the time...

You must decide: Do you really want to let the user do ANYTHING on the form and you want an easy life as programmer, then use VBA to set dynamic SQL strings depending on user selection to set the RecordSource - and lose any performance advantage of SQL Server because after custom filtering/sorting you will see that scrolling through the records will get extremly slow (especially if you use additional conditional formatting, the you can drink coffee before a page is completely displayed...) or you want a maximum of performance with a good comfort and the possibility to decide by yourself which columns are allowed to filter/to sort but have a harder time with programming this solution and don't lost overview with many views and so on...

I for myself find MS solutions for filtering and sorting in ADP as not acceptable for a stable solution, crashes, very slow display, that's not what my users expect from a database. I think it's better to lose a little bit comfort but have a stable application with maximum performance.

Cheers,

Christian

Author

Commented:
Christian,
Thank you for the proposed solution. I have posted the same question on all Access forums and to Microsoft employees. You are the first one to understand my question. I will consider your suggestions.
Thanks.
CERTIFIED EXPERT

Commented:
Hi,

I know, I saw your posts and the answers in these forums...:-)
I also found a blog entry from the Microsoft Access team from 2006 where the author said that exactly this problem will be solved with Access 12. This is true, in ACCDB it works...:-)
I guess they simply forgot to also create this solution for ADPs - maybe because it's in an old file format or whatever.

Cheers,

Christian

Author

Commented:
So what’s the replacement for ADP if Microsoft is giving up on it? I need a front-end to SQL Server for users to edit, sort and filter on data. And I don’t want to reinvent the wheels by creating form based solutions in WebForms, WinForms, VSTO, InfoPath, WPF, SilverLight…
CERTIFIED EXPERT

Commented:
When you use an accdb and attach the SQL server tables (external data, more, odbc database) you can go to design of the Benchmark table (click yes on the warning that says you can't edit it) and select the BenchmarkCategoryID field. Then click the Lookup tab at the bottom. Configure this lookup to use the BenchmarkCategory table as you would on a form. Close and save the table. Now open the Benchmark table and sort on the BenchmarkCategory column. This should give you the behavior you are looking for.


MS says you should no longer use ADP's. Search for adp on this page to find out why:
http://technet.microsoft.com/en-us/library/cc178973(office.12).aspx
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Christian,

Thank you for the lengthy reply. Case closed.
CERTIFIED EXPERT

Commented:
@Bitsqueezer: I think you should write an article with the title "adp or accdb to connect to SQL Server". It's a shame that all this useful information will only be read by the few people that read this question.
CERTIFIED EXPERT

Commented:
Hi Nicobo,

thanks, maybe you're right, but the problem is that all this is based on my personal experience, on the other hand there are a lot of people who swear by DAO/MDB/ACCDB technology as the best (following Microsoft, too) and I don't want to start a big discussion what's better, this or that. I for myself have made the decision to follow the ADP way as long as it exists in any new Access version because it's fast and easy, but maybe others made other experiences in their environment - I think this is not enough to write an article.

Cheers,

Christian
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.