Uploading multiple data files

Can someone please tell me how to get this upload to work?  I have put the code below (in my database is it a module).  The issue is, I get the file uploaded 4 times (even though I only want it once) and if I have more than one file pending upload, it only process the first file.

Is it possible the issue is from the switchboard and not this module?

Any advice is greatly appreciated.  
THANKS!
Public Results As Integer
Option Compare Database
 
Function ImportJPMCDebitTransactions()
    Dim strFile As String
    Dim strFolderName As String
    
    Dim fso, fldr, s
    Set fso = CreateObject("Scripting.FileSystemObject")
 
 
    
strFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process\"
strProcessedFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - Processed\"
strFile = Dir(strFolderName & "*.csv")
strFileCurrentlyProcessing = strFolderName & strFile
Set fldr = fso.GetFolder(strFolderName)
 
Do While (Len(strFile) > 0)
DoCmd.TransferText acImportDelim, "JPMC DEBITS Import Specification", "JPMC Debits", strFileCurrentlyProcessing
'set directory to look for next text file
   strFile = Dir
Loop
 
fso.CopyFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process", "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - Processed"
fso.DeleteFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process"
fso.CreateFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process"
 
End Function

Open in new window

LERNWebmasterAsked:
Who is Participating?
 
frankyteeConnect With a Mentor Commented:
since you are using the filesystem object you dont need to use the below line
      strFile = Dir(strFolderName & "*.csv")
also
      'set directory to look for next text file
         strFile = Dir
is wrong, it should actuallly return an error if your variable strfile was declared as a string

i've also declared the variables in used in your function within the function to keep it as local variables. you may change if required.
you should also declare your variable types explictly, your code below actually declares them as variants
    Dim fso, fldr, s

see my code below
Function ImportJPMCDebitTransactions()
'http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q__24127058.html
    'ee question to fix
    Dim strFile As String
    Dim strFolderName As String
    Dim strProcessedFolderName As String
    Dim strFileCurrentlyProcessing As String
    
    Dim fso As FileSystemObject, fldr As folder, s
    Dim f As file
    Set fso = CreateObject("Scripting.FileSystemObject")
 
     
    strFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process\"
    strProcessedFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - Processed\"
    
    '========================================================================
    'strFile = Dir(strFolderName & "*.csv") 'remove
    'Do While (Len(strFile) > 0) 'remove
    '========================================================================
    Set fldr = fso.GetFolder(strFolderName)
    For Each f In fldr.Files
        If Right(f.Name, 4) = ".csv" Then
            strFileCurrentlyProcessing = strFolderName & f.name
            DoCmd.TransferText acImportDelim, "JPMC DEBITS Import Specification", "JPMC Debits", strFileCurrentlyProcessing
        '========================================================================
        'below is wrong
        'set directory to look for next text file
        'strFile = Dir
        '========================================================================
        End If
    Next f
    
     
    fso.CopyFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process", "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - Processed"
    fso.DeleteFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process"
    fso.CreateFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process"
 
End Function

Open in new window

0
 
LERNWebmasterAuthor Commented:
It was so close!  I just made a little bit of modifications and off it went!

Thanks for your help!!!
Function ImportJPMCCreditTransactions()
Dim strFile As String
    Dim strFolderName As String
    Dim strProcessedFolderName As String
    Dim strFileCurrentlyProcessing As String
    
    Dim fso, fldr, s
    'Set fso = CreateObject("Scripting.FileSystemObject")
    Set fso = CreateObject("Scripting.FileSystemObject")
      
    strFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - to process\"
    strProcessedFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - Processed\"
    
    Set fldr = fso.GetFolder(strFolderName)
    For Each f In fldr.Files
        If Right(f.Name, 4) = ".csv" Then
            strFileCurrentlyProcessing = strFolderName & f.Name
            DoCmd.TransferText acImportDelim, "JPMC Credits Import Specification", "JPMC Credits", strFileCurrentlyProcessing
        End If
    Next f
    
     
    fso.CopyFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - to process", "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - Processed"
    fso.DeleteFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - to process"
    fso.CreateFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - to process"
 
End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.