Combo Boxes Not Allowing Dynamic Sort

[sealstone]

I have a combo box on a form that is based on an ID but I am displaying a title. I want to sort by that title and another date field. I am doing that in the form's record source. However, the client is also requesting that they be able to sort "on the fly" by different columns in this form (which is a subform in datasheet view). Error messages happen when one tries to dynamically sort, and occasionally the form goes to read only. Any workarounds?

[zmbd]

Sealstone:

1) You will have to provide the code you are using to do attempt your results.

2) Your title indicates a combo-box wherein your post seems to indicate a standard bound record set. We'll need you to clarify the actual design of your form.

3) Telling us that you get errors without any details does not help us help you. Please provide the error NUMBER and EXACT Text along with what conditions/settings were in place at the time of the error. Keep in mind that we're not in possession of your database; thus, we'll not be able to, in many cases, recreate your error in-house (Read as in each expert's house... we don't all sit in one place in the world :) - kinda neat if you think about it, I'm in the US others are in GB and Denmark and so many other places).

4) You will need to focus on ONE part of your issue in this thread... other issues need to be split into their own thread; thus, take you're most importaint problem and let's start with that here.

[sealstone]

I have a main form of owning churches for consignment music. In my subform I list the music titles they own. I have a "Music ID" column in this subform which is a pointer to a table of unique music titles. This is the record source it is based on (a saved query):

SELECT DISTINCT Music.[Music ID], 
   Music.Title, 
   [Composer/Arranger] & "--" & [publisher] 
      AS [Comp/Arr/Pub], 
   [Type] & "/" & [Voicing] 
      AS [Type/Voicing], 
   Music.[Price per item], 
   Music.[Product Type]
FROM Music
ORDER BY Music.Title;

I order this form by the date this music is available and this title (which appears in the Music ID field). To do this I modified the record source of the subform to the following SQL statement:

SELECT [Catalog List].*
FROM [Music List] 
   INNER JOIN [Catalog List] 
      ON [Music List].[Music ID] = 
         [Catalog List].[Music ID]
ORDER BY [Catalog List].[Date Available],
    [Music List].Title;

All of this works fine. The problem is the client now wants to be able to sort dynamically as the need arises in the subform on any column. When the [music id] combo box column is sorted dynamically with the sort button, the subform becomes read only.

I am not filtering here, only sorting. I am using Access 2010.

[NeoPa]

I've had a similar problem recently with a subform. The one control, that causes the issues when sorted manually, is for a field set up in the table as a ComboBox field. There are various warnings, all over the web, about using such a design for fields, but I took over the database with this already set.

I believe, without yet having proved it unequivocally (as my efforts are towards designing the form to support proper sorting and filtering, such that the operator never needs to use Access's facility for either.), that this was what was confusing matters there.

See if that is involved in your design.