troubleshooting Question

Need help renumbering sequence of files in a listbox in reverse order using Access VBA?

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
Microsoft Access
15 Comments1 Solution506 ViewsLast Modified:
Please note image below of where I have files highlighted and I want only those highlighted to be renumbered when someone clicks on Z-A.  I don't want to resort this listing but rather have the last file prefix change to _001_ and then second to last in the listing to be _002_

thus the following becomes what is shown in the code window:
__001__AB3-P1 - LEVEL P1 FLOOR PLAN OVERALL.pdf            __001__AB3-PM - LEVEL PM FLOOR PLAN OVERALL.pdf
__002__AB3-P1G - LEVEL P1 FLOOR PLAN - AREA G.pdf            __002__AB3-P5 - LEVEL P5 FLOOR PLAN OVERALL.pdf
__003__AB3-P1H - LEVEL P1 FLOOR PLAN - AREA H.pdf            __003__AB3-P4 - LEVEL P4 FLOOR PLAN OVERALL.pdf
__004__AB3-P1J - LEVEL P1 FLOOR PLAN - AREA J.pdf            __004__AB3-P3 - LEVEL P3 FLOOR PLAN OVERALL.pdf
__005__AB3-P1K - LEVEL P1 FLOOR PLAN - AREA K.pdf            __005__AB3-P2 - LEVEL P2 FLOOR PLAN OVERALL.pdf
__006__AB3-P1L - LEVEL P1 FLOOR PLAN - AREA L.pdf            __006__AB3-P1S - LEVEL P1 FLOOR PLAN - AREA S.pdf
__007__AB3-P1M - LEVEL P1 FLOOR PLAN - AREA M.pdf            __007__AB3-P1R - LEVEL P1 FLOOR PLAN - AREA R.pdf
__008__AB3-P1N - LEVEL P1 FLOOR PLAN - AREA N.pdf            __008__AB3-P1P - LEVEL P1 FLOOR PLAN - AREA P.pdf
__009__AB3-P1P - LEVEL P1 FLOOR PLAN - AREA P.pdf            __009__AB3-P1N - LEVEL P1 FLOOR PLAN - AREA N.pdf
__010__AB3-P1R - LEVEL P1 FLOOR PLAN - AREA R.pdf            __010__AB3-P1M - LEVEL P1 FLOOR PLAN - AREA M.pdf
__011__AB3-P1S - LEVEL P1 FLOOR PLAN - AREA S.pdf            __011__AB3-P1L - LEVEL P1 FLOOR PLAN - AREA L.pdf
__012__AB3-P2 - LEVEL P2 FLOOR PLAN OVERALL.pdf            __012__AB3-P1K - LEVEL P1 FLOOR PLAN - AREA K.pdf
__013__AB3-P3 - LEVEL P3 FLOOR PLAN OVERALL.pdf            __013__AB3-P1J - LEVEL P1 FLOOR PLAN - AREA J.pdf
__014__AB3-P4 - LEVEL P4 FLOOR PLAN OVERALL.pdf            __014__AB3-P1H - LEVEL P1 FLOOR PLAN - AREA H.pdf
__015__AB3-P5 - LEVEL P5 FLOOR PLAN OVERALL.pdf            __015__AB3-P1G - LEVEL P1 FLOOR PLAN - AREA G.pdf
__016__AB3-PM - LEVEL PM FLOOR PLAN OVERALL.pdf            __016__AB3-P1 - LEVEL P1 FLOOR PLAN OVERALL.pdf

I thought of first dumping the files highlighted in a temp table... then use some functions to parse out the prefix_existing to replace but not sure how to reverse the prefix order per each row?

I've also provided a starter sample on it here:
https://filedb.experts-exchange.com/incoming/ee-stuff/8163-File-Order.mdb
Private Sub cmdSortZA_Click()

  If Any_Highlighted(Me.lstFiles) = True Then
    'Call Construct_Site
    'GoTo Exit_This
    
    Dim sSQL As String
    sSQL = "DELETE * FROM tblFilesSort"
    CurrentDb.Execute sSQL
  
    Dim i As Integer
    For i = 0 To lstFiles.ListCount - 1
        If lstFiles.Selected(i) = True Then
          
          'Add to temp table...
          sSQL = "INSERT INTO [tblFilesSort] (FileNameAfter, FullFileName) VALUES ('" & lstFiles.ItemData(i) & "', '" & Full_FileName(lstFiles.ItemData(i), "tblFiles", "FileNameAfter") & "')"
          CurrentDb.Execute sSQL
          
        End If
    Next
  
    Stop
    
  Else
    MsgBox ("File Section (Right Side) - Nothing highlighted to transfer!")
    
  End If
  
Exit_This:
  
  
End Sub


Public Function Prefix_Value_Existing(sFile As String)
  
  Dim i As Integer
  Dim j As Integer
  Dim sValue As String
  
  sValue = "unknown"
  
  For i = 1 To Len(sFile)
  
    If Mid(sFile, i, 2) = "__" And j = 0 Then
      j = j + 1
    Else
      If Mid(sFile, i, 2) = "__" And j = 1 Then
        sValue = Left(sFile, (i + 1))
        Exit For
      End If
      
    End If
      
    
  Next
  
  Prefix_Value_Existing = sValue

End Function
example.png
ASKER CERTIFIED SOLUTION
als315

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros