UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Unbound Combobox Sort, Office 2007    
 
   
Aquadevel
post Apr 6 2012, 01:23 PM
Post #1

UtterAccess VIP
Posts: 6,898
From: Earth...




Hi My 'Utter Friend's,

I'm using the following code to fill a unbound combobox on a form:
and having an issue trying to get list in decending order.

CODE
[/code]
Dim sPath As String
    Dim sFileList As String
    Dim sFileName As String
    
    sFileList = ""
    sFileName = dir(cPath)
    Do While sFileName <> ""
        sFileList = sFileList & sFileName & ";"
        sFileName = dir
    Loop
    
    Me.cbSelectFile.RowSource = sFileList
    Me.cbSelectFile.Requery
[code]


Which works fine, however client can have anywhere from 5 to 100 files added per day to the list.
I am using 'KeyDown', so that they can type in and get to the file they want faster, but I'd like to also have the like in decending order.
The file names are numeric, like 8234.pdf, 8235.pdf, etc.

Thanks,

Go to the top of the page
 
+
theDBguy
post Apr 6 2012, 01:28 PM
Post #2

Access Wiki and Forums Moderator
Posts: 48,092
From: SoCal, USA



Hi Aqua,

Check out Brent's MakeCSV() function in the Code Archive.

It has a couple of routines for sorting an array that I think you may be able to use for your combobox.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
ScottGem
post Apr 6 2012, 01:46 PM
Post #3

UtterAccess VIP / UA Clown
Posts: 25,091
From: LI, NY



Why not just use the FileDialog property to brows the folder?
Go to the top of the page
 
+
Aquadevel
post Apr 6 2012, 03:57 PM
Post #4

UtterAccess VIP
Posts: 6,898
From: Earth...




Hi Scott,

Because it has to be "user proof"
There are really 2 reports printed for each JobID and they can NEVER be 'accidently' swapped, which a user using FileDialog could get distracted and allow.

I have everything working great, the Access reports are fine, the 2 PDF's are fine and renamed to the JobID and I have the email thru Access using smtp
setup so there is NO way a user can select or ever swap the reports.

I'm just trying to make this easier for the clients users, as they can have anywhere from 3-100 reports a day.
so desending order would be better IMHO.

Thanks,

(IMG:style_emoticons/default/uarulez2.gif)
Go to the top of the page
 
+
philben
post Apr 6 2012, 07:21 PM
Post #5

UtterAccess Member
Posts: 22



Hi,

This function can do the job :

CODE
'Input : RowSource (only one column per row!)
Public Function RowSourceSort(ByVal RowSource As String) As String
   Const cSeparator As String = ";"
   Dim valRef As Double, g As Variant, ref As String, aRow() As String
   Dim h As Long, i As Long, j As Long, k As Long, l As Long, n As Long

   aRow = Split(RowSource, cSeparator)
   n = UBound(aRow) + 1
  
   'Init shellSort gap sequence
   g = Array(1, 4, 10, 23, 57, 132, 301, 701, 1750, 4376, 10941, 27353, 68383, 170958)
   l = LBound(g)
   k = l
   If n < g(UBound(g)) Then
      While g(k + 1) < n: k = k + 1: Wend
   Else
      k = UBound(g)
   End If

   'sort
   While k >= l
      h = g(k)
      For i = h To n - 1
         ref = aRow(i)
         valRef = Val(ref) 'String to numeric
         j = i
         Do While valRef > Val(aRow(j - h))   '> : descending order
            aRow(j) = aRow(j - h)
            j = j - h
            If j < h Then Exit Do
         Loop
         aRow(j) = ref
      Next i
      k = k - 1
   Wend

   RowSourceSort = Join(aRow, cSeparator)
End Function


Example :
CODE
Me.cbSelectFile.RowSource = RowSourceSort(sFileList)


This post has been edited by philben: Apr 6 2012, 07:31 PM
Go to the top of the page
 
+
Aquadevel
post Apr 6 2012, 09:21 PM
Post #6

UtterAccess VIP
Posts: 6,898
From: Earth...




Hi Phil,

U're the man of the day, works great!!!!!!!!!!! (IMG:style_emoticons/default/notworthy.gif) (IMG:style_emoticons/default/uarulez2.gif) (IMG:style_emoticons/default/notworthy.gif)

Thanks, to every one this made my weekend!!!!!!!

Now I can sleep!!!

(IMG:style_emoticons/default/uarulez2.gif)
Go to the top of the page
 
+
theDBguy
post Apr 6 2012, 11:03 PM
Post #7

Access Wiki and Forums Moderator
Posts: 48,092
From: SoCal, USA



Hi Aqua,

Congratulations on finding a solution that works for you. Just FYI, it's basically the same sorting routine I linked you to earlier.

Good luck with your project.
Go to the top of the page
 
+
Aquadevel
post Apr 6 2012, 11:17 PM
Post #8

UtterAccess VIP
Posts: 6,898
From: Earth...




Hey DB,


Yea, but I got caught up with 'things in the fire here', if ya follow my drift.

Thanks for all past & future help!!!!

(IMG:style_emoticons/default/notworthy.gif)
Go to the top of the page
 
+
theDBguy
post Apr 6 2012, 11:20 PM
Post #9

Access Wiki and Forums Moderator
Posts: 48,092
From: SoCal, USA



Hi Aqua,

No biggie. I just noticed that it took almost a full six hours before somebody posted the same code I linked to earlier. If you weren't too busy, you probably would have fixed the problem sooner.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Go to Top  ·  Lo-Fi Version Time is now: 24th May 2013 - 01:16 PM