Can this code be changed from Constant to Variable?

[papic1972]

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??

 

[boblarson]

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.

 

[papic1972]

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?

 

[khawar]

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

 

[papic1972]

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.

 

[khawar]

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