3 seperate folders to send PDF files

[steve111]

Hi
the code below sends 3 reports to a folder that the vba creates if needed.
I would like to change it so the 3 reports go to different folders
and creates a folder with the customer name in each of the below folders if needed

at present it looks table with one folder address is as I dont know how to make it look up the 3 different records in that table so I would like to just put the 3 folder address in the code

1. ' varFolder = "C:\Users\User\Documents\ invoice"
2. ' varFolder = "C:\Users\User\Documents\cofc"
3. ' varFolder = "C:\Users\User\Documents\despatch

Private Sub cmdPDF_Click()
  
   On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current invoice."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varFolder As Variant
     If Not IsNull([Invoice].[Form]![id]) Then
    
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
          MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
            varFolder = varFolder & "\" & Me.[CustomerName]
            MkDir varFolder
            strFullPath = varFolder & "\" & "Invoice Number" & " " & [Invoice].[Form]![InvoiceNo] & ".pdf"
           
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
             
            
            DoCmd.OutputTo acOutputReport, "Invoice report", acFormatPDF, strFullPath
            
             
         strFullPath = varFolder & "\" & "C of C  No" & " " & [Invoice].[Form]![InvoiceNo] & ".pdf"
          
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "C OF C report", acFormatPDF, strFullPath
        
        
         strFullPath = varFolder & "\" & "Despatch No" & " " & [Invoice].[Form]![InvoiceNo] & ".pdf"
          
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "Invoice delivery note", acFormatPDF, strFullPath
                 
                    
            
        End If
    Else
        MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
    End If
 Exit_Here:
    Exit Sub
    
Err_Handler:
    Select Case Err.Number
        Case FOLDER_EXISTS
        Resume Next
        Case Else
        MsgBox Err.Description
        Resume Exit_Here
    End Select
 End Sub

and help appreciated

steve

 

[winshent]

Use an array...

dim varFolder(2) as string

varFolder(0) = "C:\Users\User\Documents\ invoice"
varFolder(1) = "C:\Users\User\Documents\cofc"
varFolder(2) = "C:\Users\User\Documents\despatch

 

[steve111]

hi , forgive me as I am new to this VBA but where in my code would I put that information and do I need to delete anything

steve