Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: Adjust Ordering of a Combobox Manually

    Morning all!

    I have a slight problem (who would've guessed, eh?)...

    I have a combobox, that's bound to a union query I have. The query basically returns an entire list of all the product style codes for the items manufactured here. Of which there are about 100 different codes.

    Of these 100 codes, only about 7 are commonly used.

    At present this combobox has autocomplete (the predictive input thing, can't remember if that's what it's called or not), limited to only those entries, and is ordered alphabetically.

    What I would really love, is to have the 7 commonly used items at the top of the list, ordered alphabetically, then the rest of the codes following them, again order alphabetically.


    Is that clear? And is this possible?


    I've never come across anything to suggest it is, but am pretty against using two comboboxes, as I feel that will confuse my end users (who aren't the most PC literate people going).


    Any advice, suggestions, links, etc, greatly appreciated, as ever.
    Looking for the perfect beer...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could add a column to the table(s) from where the combo fetches its values. This column would be numeric and indicate the rank of the row in the combo. The query that acts as the RowSource would then have an ORDER BY clause on this column.

    Another solution would consist in using a function with a return value of type String to assemble a list in whatever order you want, then use this function as the RowSource for the combo.

    You could also create a specialized function for the combo (see RowSourceType: User-defined Function in Access help for details), but this solution is very complex.
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Because the admin staff will have the ability to add/remove/edit these product style codes, and new ones will be being added on a weekly/monthly basis, I think keeping up with numbering will be pretty difficult, as within a couple of years the total could be rather large.

    I effectively want to divide the combobox into two sets... One for common and one for irregular. I'm looking into the help file now, is that going to allow for this kind of setup, automatically handling any additions?

    If I can specify the 7 codes that should appear at the top to begin with, using a function, it would also allow me to provide the admin team with the ability to adjust the commonly used codes through a form, without them writing code, and without requiring me to make manual adjustments, which would be ideal...
    Looking for the perfect beer...

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Reading through the help text and working through the examples, I have a couple of ideas about how I can use it to achieve the desired results...

    One thing though, can I use SQL in the 'acLBGetValue' section of the function?

    I assume it works as any other function, and it shouldn't be a problem, but knowing it's going to be a problem before starting could save a massive headache.
    Looking for the perfect beer...

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The possibility to add new rows into the table does not matter, nor does the number of rows.

    Let's suppose you add a column, like this:
    Name: Rank
    Data type: Number (Long Integer)
    Allow Null: No
    Default value: 100

    For the 7 rows you want to see at the top of the combo, you manually assign a value from 0 to 6 (or from 1 to 7) to their Rank. All other rows receive the value 100 for the Rank column.

    The Rank column is not accessible to the user (it's hidden if users can open the table(s), which is not recommended) or a matching control is not present if users open a form to add new values. That way, any new row added to the table(s) will receive a default value of 100 in the Rank column.

    Let's suppose that, except for the 7 top values, you want the rows in the combo sorted alphabetically. The Order By clause of the query feeding the combo becomes: ORDER BY Rank, Name

    That way, the 7 top values will appear first (according to the value of the Rank column), while all other rows will be sorted by the second column specified in the Order By clause of the query, as they all share the same Rank value.
    Have a nice day!

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Snap Sinndho

    Thats What I ending up doing for one of my customer.
    it funny the clients ask how does the computer know I wanted to pick that when it should be in middle of the list box now it does the top 5 items and that blow her socks off she keeps telling me she trying prove the computer wrong
    Last edited by myle; 08-26-11 at 07:47. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @myle: Seems logical to me. No need to search for complex (and often unmanageable) solutions: "Make Your Life Easy"
    Have a nice day!

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    The only reservation I have about using that method, is that I can't think of a way of automatically ordering the 7 common codes.

    If I could give those 7 a rank of 1, and all the rest a rank of 100, ordering by alphabetical order those with rank 1, then ordering those with rank 100 alphabetically afterwards that would be great... But can SQL handle that kind of ordering?

    I'm working my way through programming a user defined rowsourcetype property as I type this. It's fiddly, but coming along slowly but steadily... (also, great learning 'tool'. I like to get more to grips with a language and more indepth with the constants that lie beneath the surface)

    At the moment, where my union query draws from, I have made an extra field called 'Common Codes', which I'm feeding into the 'Case acLBGetValue' section of the function, then once the 'row' variant is greater than the number of non null records held in the 'Common Codes' field, it can then begin populating the combobox from the union query instead. Which should work alright, and that way a user can simply update the 'Common Codes' field in any way they like (add/remove/edit), and the combobox will still order them alphabetically without requiring manual adjustment...


    If you know of a way to do a simple double ORDER BY, so that if a member of rank 1 is removed, and two more members added, the alphabetic ordering won't get messed up, that'd be great!
    Last edited by kez1304; 08-26-11 at 08:00.
    Looking for the perfect beer...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure to follow you. The results of an ORDER BY clause will remain the same, whatever the changes you made in the data set. You just need to requery the combo after changing the source table(s), what you'll have to do anyway.

    If the 7 rows you want to see first in the combo have a ranking order of 1 (Rank=1) and all other rows have a ranking order of 100 (Rank=100) the seven [Rank=1] rows will appear first (ordered alphabetically), then all other rows will follow (also ordered alphabetically). Adding or removing a row won't change the order in which the rows are sorted and displayed (except if you change the value in the Rank column).

    You can even exclude rows from appearing in the combo, if you want:
    Code:
    Combo.RowSource = "SELECT SomeTable.Name FROM SomeTable WHERE SomeTable.Rank > 0 ORDER BY SomeTable.Rank, SomeTable.Name;"
    Have a nice day!

  10. #10
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    That's good to know... Think I'm having one of those blonde moments to be honest...

    One thing that is an issue in regards to the ranking system is that I can't have it on the table itself, as the rowsource comes from a union query...

    The codes are currently seperated into 10 different fields in the same table, for various reasons that I won't go into. But they must remain in these specific fields.

    If I'm to assign ranking to the union query, how should I go about this?
    Looking for the perfect beer...

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by kez1304 View Post
    Think I'm having one of those blonde moments to be honest...
    Don't we all have?

    What prevents you from adding the Rank column to every table that is used in the UNION query? It's lightweight (only 4 bytes) and is not subject to change often.
    Have a nice day!

  12. #12
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    The union joins the contents of fields from a single table. Looking at it now I'm not sure why I used a union, when a normal SELECT would have done... Actually I think because of the way the fields get populated, it ended up with null values in random places in random fields, so it would strip out the rest of that record, even if it had values in it...

    Aaaanyway, because the table looks like (at a glance):

    Code:
    +-----+--------+--------+--------+
    | xxx | CNTxxx | CRUxxx | ORGxxx |
    +-----+--------+--------+--------+
    | ERD |  GED   |  TTED  |  NBTS  |
    | HGA |        |  XYB   |  SSNP  |
    I can't really assign ranks to it... Bit of a pain really.
    Looking for the perfect beer...

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can create a pseudo-column in the query:
    Code:
    IIf ([Name] IN ( 'ERD', 'GED', 'TTED', 'NBTS', 'HGA', 'XYB', 'SSNP' ), 1, 100) AS Rank
    Have a nice day!

  14. #14
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I thought I'd be able to do it using an IIF statement... Although I always try and avoid using them, they just remind me of headaches from nested IF() statements in excel, back in my youth.

    Anyway, as I was working on the user-defined function, I came up with..: (for thread completion's sake)

    Code:
    Option Compare Database
    
    Function SmartStylesList(fld As Control, id As Variant, _
        row As Variant, col As Variant, code As Variant) _
         As Variant
        
        Dim strSQL As String
        Dim rs As DAO.Recordset
        Static numOfCodes As Long
        Static maxCommonRecs As Long
            
        Select Case code
            Case acLBInitialize            ' Initialize.
            
                '------------------------------------------------------------------
                'Finds the number of items currently in the style codes union query
                '------------------------------------------------------------------
                strSQL = "SELECT COUNT([xxx]) As NumOfStyleCodes " & _
                         "FROM ref_styleUnion "
                         
                Set rs = CurrentDb.OpenRecordset(strSQL)
                
                numOfCodes = CLng(rs![NumOfStyleCodes])
            
                rs.Close
                Set rs = Nothing
                '------------------------------------------------------------------
                '------------------------------------------------------------------
            
            
                '------------------------------------------------------------------
                '    Finds the number of items currently used as Common Codes
                '------------------------------------------------------------------
                strSQL = "SELECT DISTINCT [Common Codes] " & _
                         "FROM ref_knownCodes " & _
                         "WHERE [Common Codes] IS NOT NULL " & _
                         "ORDER BY [Common Codes]"
                
                Set rs = CurrentDb.OpenRecordset(strSQL)
                
                rs.MoveLast
                maxCommonRecs = rs.RecordCount
                rs.MoveFirst
                            
                rs.Close
                Set rs = Nothing
                
                'Requires adding the Common Codes total so there are enough
                'rows to cover every entry to the box.
                numOfCodes = numOfCodes + maxCommonRecs
                '------------------------------------------------------------------
                '------------------------------------------------------------------
                
                
                SmartStylesList = True
                
            Case acLBOpen                   ' Open.
                SmartStylesList = Timer         ' Unique ID.
                
            Case acLBGetRowCount            ' Get rows.
                SmartStylesList = numOfCodes    ' Use static variable.
                
            Case acLBGetColumnCount         ' Get columns.
                SmartStylesList = 1
                
            Case acLBGetColumnWidth         ' Get column width.
                SmartStylesList = -1            ' Use default width.
                
            Case acLBGetValue               ' Get the data.
                
                'If the current row is less than or equal to the number
                'of Common Codes currently specified for use...
                If (row < maxCommonRecs) Then
                
                    strSQL = "SELECT DISTINCT [Common Codes] " & _
                             "FROM ref_knownCodes " & _
                             "WHERE [Common Codes] IS NOT NULL " & _
                             "ORDER BY [Common Codes]"
                
                    Set rs = CurrentDb.OpenRecordset(strSQL)
                
                    'Populate the box with the Common Codes.
                    rs.Move (row)
                    SmartStylesList = rs(0)
                                    
                    rs.Close
                    Set rs = Nothing
                
                'Then, fill the rest of the rows with the rest of the known
                'codes.
                Else
                
                    strSQL = "SELECT [xxx] " & _
                             "FROM ref_styleUnion " & _
                             "ORDER BY [xxx]"
                
                    Set rs = CurrentDb.OpenRecordset(strSQL)
                    
                    'Populate the box with the rest of the Codes.
                    rs.Move (row - maxCommonRecs)
                    SmartStylesList = rs(0)
                    
                    rs.Close
                    Set rs = Nothing
                    
                End If
                 
        End Select
        
    End Function
    Which allows me to simply update one field on one column to alter the favourites I want listed. I was debating putting a vbNullString for one of the rows too, to seperate the common and non common data, but I don't think it's really necessary... being able to put a solid bold horizontal line would be nice, but I don't think combobox's allow for that kind of customisation..?

    Either way, I figure this is easier for future generations to update, and makes for having (to me anyway) a more comprehensible method of viewing the database.

    Can comboboxes have a dark line somehow? Is there a constant somewhere that controls such things, do you know?
    Last edited by kez1304; 08-26-11 at 10:19.
    Looking for the perfect beer...

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm glad you could find a solution, albeit a complex one.

    If you mean a dark line between each row in the list part of the combo, it's almost impossible to do with Access. I know of a solution but it works in VB only.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •