Link to home
Create AccountLog in
Avatar of Bob Stamm
Bob StammFlag for United States of America

asked on

listbox not in ascending order after server change

I have a Excel application and Access FE/BE applications that have the same problem.  My list and comboboxes nolonger display my list in ascending order.  In both applications they are looking at folders on the same server.  If I copy my files to my "C:" drive and redirect my applications to on my own PC the list go back to disply in ascending order.  Any idea what could be occuring?  This seemed to have started after my IT group changed the server.  
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

<This seemed to have started after my IT group changed the server.  >

check the complete path of the "new server", if you are accessing the same file from your code

are using a mapped drive?  better use the UNC path
\\serverName\folder\filename
Avatar of Bob Stamm

ASKER

I am using a mapped drive.  I changed to UNC path but still have same results.  I am using .AddItem to fill listbox.
    SystemLookup.AddItem "Butler, PA (750MHz)"
    SystemLookup.List(3, 1) = "\\agc01.com\agc-crp\AGOCGlobal\public\CS\NETWORKS\BUTLER\BOM\"
    SystemLookup.List(3, 2) = "\\agc01.com\agc-crp\AGOCGlobal\public\CS\NETWORKS\BUTLER\BOM\*.wk1"
    SystemLookup.List(3, 3) = "\\agc01.com\agc-crp\AGOCGlobal\public\CS\NETWORKS\BUTLER\BOM\*.xls"
    
    SystemLookup.AddItem "Butler, PA (862MHz)"
    SystemLookup.List(4, 1) = "P:\CS\NETWORKS\BUTLER\BOM"
    SystemLookup.List(4, 2) = "P:\CS\NETWORKS\BUTLER\BOM\*.wk1"
    SystemLookup.List(4, 3) = "P:\CS\NETWORKS\BUTLER\BOM\*.xls"

Open in new window

RobertStamm,

Is the code you posted VB, Vb.net, or VBA(Access)?

AFAIK there is no  ".List"  property for a listbox in Access VBA.

JeffCoachman

I am having the same issue in two different applications.  The one I listed is in Excel using VBA to fill my listbox.
RobertStamm
What is the list box suppoesd to display, that is not in the correct order?
Can you paste the complete code in the Access application?
JeffCoachman
In Access 2003 I have a combobox that when selected the dropdown will list all the files from in the pointed directory.  Other selections on the form direct the control to the appropriate directory.  This all worked fine at one point.  A couple of my users complained about the dropdown list no longer being in ascending order.  It seemed to occur after our IT group moved us to a new server.  Not sure if that has any bearing on this or not.  I attached a view of the dropdown list.  It seems like files that were created earlier sort in order.  New files come into the dropdown on the bottom of the list in scattered order.
Private Sub cboNodeAdditionWorksheet_GotFocus()
Dim strNextFileInDir As String
 
If Dir("Z:\NodeROIWorkSheets\" & Trim(txtSystem) & "\" & "*.xls") <> "" Then
 
    strNextFileInDir = Dir("Z:\NodeROIWorkSheets\" & Trim(txtSystem) & "\" & "*.xls")
            If Right(strNextFileInDir, 4) = ".xls" Then
                strNextFileInDir = Left(strNextFileInDir, Len(strNextFileInDir) - 4)
            End If
    cboNodeAdditionWorksheet.RowSource = strNextFileInDir
 
    Do
        strNextFileInDir = Dir
            If Right(strNextFileInDir, 4) = ".xls" Then
                strNextFileInDir = Left(strNextFileInDir, Len(strNextFileInDir) - 4)
            End If
            'MsgBox strNextFileInDir
        If strNextFileInDir = "" Then Exit Do
        cboNodeAdditionWorksheet.RowSource = cboNodeAdditionWorksheet.RowSource & "," & strNextFileInDir
    Loop
 
Else
    MsgBox "No Node Worksheets files in path."
End If
End Sub

Open in new window

Combobox.doc
looks like the file is being picked up according to date of creation/modified
Any suggestions on how I can handle this?
create a temptable to store your excel filenames and use a query using the temptable as row source of your combo box
Can you provide an example how to create a temptable.  It might be simple but I have never done that before.
are you doing this in Access?
I am having the same listing issue in Excel and Access.  The example I showed is in Access 2003.
from the database objects window select Tables > new >design view

in the FieldName  type Filename
in the DataType   select Text

save the table, give it a name, say  tblExcelFiles
OK that is my table.  What code in needed to push my results into the table?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
That looks like the model I needed.  I will give this a shot over the weekend.  Thanks for your help.  I am a self taught copy/paste/modify type of programmer.  This will get me going in the right direction.

Bob
capricorn1,
Thank you!!  I built this into my application and this method works great.  Thanks for providing me with the code.
Bob