I have a pop-up form (that opens up from frmOrderInput) with a listbox on it (lstCI) and it displays documents that relate to the value in field 'OrderID' (which is the Order number).

My code (as seen below) was originally written using a constant path to view the documents


Const cPath = "S:\Internal Sales\DatabaseFiles\OrderRelated\"

Dim sPath As String
Dim sFileList As String


Dim sFileName As String

sFileList = ""



sFileName = Dir(cPath)

Do While sFileName <> ""
If sFileName Like Forms!frmOrderInput!OrderID & "CI*" & "*" Then
sFileList = sFileList & sFileName & ";"

End If



sFileName = Dir
Loop

Me.lstCI.RowSource = sFileList
Me.lstCI.Requery


However, due to performance issues (with the high number of files in "S:\Internal Sales\DatabaseFiles\OrderRelated\" directory)
i have been forced to redesign the system & create subfolders on my server that hold documents ranging from one Order number to another...e.g "S:\Internal Sales\DatabaseFiles\OrderRelated\45000-47500" rather than in main folder

i understand that a constant is exactly that, a constant, however i need to change this code so the path is now variable and so that the listbox is capable of pointing towards the required file in the correct subfolder.
I hope i haven't confused anyone, can anyone help me re-code this??

I would put that into a table and then use a DLookup when you need it. Then you don't have to code it anymore, just change the location in the table.

Hi Bob,

Thanks for the suggestion, but this would not be practical for me, given that there are about 19000 PDF's currently stored in S:\Internal Sales\DatabaseFiles\OrderRelated.

Is there are way to transform the use of the const into some sort of variable string?

how do you name those PDFs are they related to some records if yes then are there names are taken from some text in records

Yes, the PDF's (which are customer invoices) are related to the Order ID and named using a combination of the value in the OrderID field & 'CI' which stands for 'customer invoice'. The PDF is produced by clicking a checkbox on frmOrderInput;

this is an excerpt from my 'on click' event code on frmOrderInput:

If Me.Check25970 = True And Me.Check25976 = True Then
Me.Check25976 = False

Select Case Me.OrderID

Case 40000 To 42000
blRet = ConvertReportToPDF("rptInvoice", vbNullString, "S:\Internal Sales\DatabaseFiles\OrderRelated\40000-42000\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
End Select
End If

As you can see, the PDF is stored in a location depending on the OrderID range.

You can use the same criterea which you are using on saving PDF i-e use case statement to obtain the folder name and you can use order id to determine file name