none
Sorting Fields in Forms!!! RRS feed

  • Question

  • Sort Order!! 2007 Access - why all of a sudden is it a problem - I previously used sql strings with two or three sorts for my forms/subforms in access 97 - Perfect.......

    Now notwithstanding The original Table's are set with the required sort order as well  the sqls - IT DON'T WORK! ........... I read that established sort orders do not become default when trying to sort in forms and Reports (reports I can solve) .. but none of the reading on the various settings seems to work for me in forms-  not to mention my not quite understanding how to adapt what I have come across for my needs. IGNORANCE IS NOT BLISS!

    I have at present (and will in future  have different ones) two tables 'MediaType' which I want Ascending and 'When' (a YYYY date Format) which I require in Descending order at the same time! No matter what I do the 'Production' field (1st in line Field) takes precedence ???  - driving me nuts!

    Can anyone please point me to a simple call routine that sets the sort order to the fields above - would be sooooo pleased!

     

    I thank you

     

    db

     

     

     

     

     

     

     


    • Edited by dbdesign Monday, October 31, 2011 1:31 PM
    Monday, October 31, 2011 1:30 PM

Answers

  • So simple and really self evident that I am almost too embarrassed to talk about it - however it won't be the first for myself nor many of us I guess -

    It was as a result of giving the linked name in the main Table (Productions) as MediaType (which is also the TEXT field in the secondary(linked table)) ---

    Where as had I named it as MediaTypeID the same primary field name to which it is linked in the secondary table .... it would have been self evident that it wasn't a text field but a number field - what I wanted of course was to get the sorting on MediaType a text field - Blah Blah -

    I would have been more even persistent in my analysis and problem solving had I not gone to the web to search for answers - stumblings across something to the effect the that sql sorting does not carry  by default across to the relative forms (nonsense of course) - having tried various apparent vba solutions to the problem and failing I decided to to ask trusty MSDN - :( - that's all just an excuse really - !!! - but I probably won't make the same mistake again - Ho Ho!

     

    thanks again

     

    db

    • Marked as answer by dbdesign Tuesday, November 1, 2011 4:03 PM
    Tuesday, November 1, 2011 4:02 PM

All replies

  • We need some more info on the SQL statements you have used as Recordsource for the Forms.

    Please be more specific, post which form with the SQL accordingly.

     

    Thanks,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, October 31, 2011 4:07 PM
    Moderator
  • Thanks Daniel

     

    Yes I guess including it originally would have been smart!

     

    Here it is:

     

    SELECT Productions.MediaType, Productions.When, Productions.RoleName, Productions.SeekRole, Productions.ProductionID, Productions.ClientID, Productions.Production, Productions.Company, Productions.Director FROM Clients INNER JOIN Productions ON Clients.ClientID = Productions.ClientID ORDER BY  Productions.MediaType, Productions.When DESC; (this is not a separate permanent Query)

     

    The forms Concerned are MiscAF with a a series of Tabbed pages one of which reveals MiscProdsF with its SubForm MiscProdsSF - these last two are the relevant Forms

     

    I select the client I wish to deal with In MiscProdsF which reveals MiscProdsSF to which the Sql above relates - the two being linked by ClientID

     

    As it happens you can see within the sql I have addressed 'MediaType' & 'When' 1st & 2nd in the hope that that would force a sort order -though in fact they are not in that order on the form - but whatever I do it makes no difference.

     

    I am prettty convinvced it needs a routine in 'Gotfocus' (or whatever) of the MiscProdsSF - would be pleased to hear otherwise - as long as I can sort it !!

     

    thanks for your response

     

    db






    • Edited by dbdesign Monday, October 31, 2011 5:03 PM
    Monday, October 31, 2011 4:46 PM
  • The SQL sorting is correct, I don't see anything wrong with the Statement.

    Can you post an example of what it should be, and what you see, that would help as well.

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, October 31, 2011 5:24 PM
    Moderator
  • Thanks for your response Daniel - I have sorted it - fairly elementary error on my part -

     

    Best to you

     

    db

     

     

    Tuesday, November 1, 2011 1:05 PM
  • Hi db,

    Thanks for sharing that you got it sorted, but I am curiouse what caused it?

    Would you mind sharing the cause?

     

    Thanks! :)

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, November 1, 2011 1:56 PM
    Moderator
  • So simple and really self evident that I am almost too embarrassed to talk about it - however it won't be the first for myself nor many of us I guess -

    It was as a result of giving the linked name in the main Table (Productions) as MediaType (which is also the TEXT field in the secondary(linked table)) ---

    Where as had I named it as MediaTypeID the same primary field name to which it is linked in the secondary table .... it would have been self evident that it wasn't a text field but a number field - what I wanted of course was to get the sorting on MediaType a text field - Blah Blah -

    I would have been more even persistent in my analysis and problem solving had I not gone to the web to search for answers - stumblings across something to the effect the that sql sorting does not carry  by default across to the relative forms (nonsense of course) - having tried various apparent vba solutions to the problem and failing I decided to to ask trusty MSDN - :( - that's all just an excuse really - !!! - but I probably won't make the same mistake again - Ho Ho!

     

    thanks again

     

    db

    • Marked as answer by dbdesign Tuesday, November 1, 2011 4:03 PM
    Tuesday, November 1, 2011 4:02 PM