If table criteria is in File Name, Move File HELP

JDeezy

Registered User
I am having some problems. I have a table with certain file name criteria to match in file name but it isnt working correctly. I am using InSTR but I feel like I should be using a different command. Any help would be greatly appreciated.

Code:
If InStr(1, rs![FName], rs1![BuClient]) And InStr(1, rs![FName], rs1![Identifier]) And InStr(1, rs![FName], rs1![Identifier 2]) Then
objFSO.MoveFile strsourcefolder, strDest & "-" & initials & "-" & rs![FName]
Code:
 

Beetle

Duly Registered Boozer
I'm not completely sure what you're trying to do but if you're trying to compare table data to a file name, you should use the Dir function to return the file name to a variable, then run the InStr function against the variable.

Simething like;

Code:
Dim strFileName As String
 
strFileName = Dir("C:\SomeFolder\SomeFile")
 
If strFileName <> "" Then
    If InStr(1,strFileName, rs!SomeField) > 0 Then
        'code to vaporize your enemies goes here
    End If
End If
 

JDeezy

Registered User
Sorry I should have been more specific. I didn’t really know how to ask the question (I was a little brain fried yesterday, Work has me redoing all of their coding) Anyways!

So I have 2 tables basically, 1 with file paths and file names of files in a specific drive, and 1 with client numbers and criteria for the file name. 3 different criteria field with 2 file name identifiers and 1 pathway identifier. I need it to loop through the file path and path identifier first and say if Instr(1,[File Path], [Path Identifier]) and Instr(1,[File Name], [Name Identifier]) and Instr(1,[File Name], [Name Identifier 2]) Then Move File.

I thought I had it just about right but it will run through the code but not move the file with the correct record set criteria that I am adding. For Example, I have certain file where the type is N and O, So if file name include SSN6556 it should move file with O prefix, if its 6556 only it should move it with an N prefix. For some reason it’s not moving that file correctly.

Any help would be tremendous!


Code:
Private Sub cmdMove_Click()
Dim strsourcefolder As String, strDest As String
Dim initials As String, clientNum As String
strDest = "G:\BBST\"
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("Files", dbOpenDynaset)
Set rs1 = db.OpenRecordset("FTP INFO", dbOpenDynaset)
initials = "JC"  'Forms![Move FTP Files]![txtInitials]
Do While Not rs.EOF
    Do While Not rs1.EOF
    strsourcefolder = rs![FPath] & rs![FName]
    
    'On Error Resume Next
    
    If InStr(rs![FPath], rs1![Path Identifier]) > 0 And InStr(rs![FName], rs1![Identifier 2]) > 0 And InStr(rs![FName], rs1![Identifier]) > 0 Then
    Name strsourcefolder As strDest & rs1![Type] & rs1![CLIENT #] & "-" & initials & "-" & rs![FName]
    End If
    
    If InStr(rs![FPath], rs1![Path Identifier]) > 0 And InStr(rs![FName], rs1![Identifier]) > 0 Then
    Name strsourcefolder As strDest & txt5 & rs1![CLIENT #] & "-" & initials & "-" & rs![FName]
    End If
    
    rs1.MoveNext
    Loop
    rs1.MoveFirst
    
rs.MoveNext
Loop
rs.Close
End Sub
 

Beetle

Duly Registered Boozer
When you say it "doesn't move the file correctly", I'm not sure what you mean (it doesn't name it what you expected, it doesn't move to the correct location, etc.?), but here are a couple of thoughts on your code.

1) I would think that you would want to make sure rs is on the first record before you start looping through rs1.

2) You are processing two independent If statements. The problem here is that if the first statement evaluates to True (all three conditions are met, so you rename and move the file), then the second If statement must also evaluate to True as well (if all three values are in the string then two of the three values must be in the string). At that point it's going to attempt to rename and move the same (old) file name again, resulting in a File Not Found error.

Here is a slightly modified version of your code. Not sure if it will help or not.

Code:
Dim strsourcefolder As String, strDest As String
Dim initials As String, clientNum As String
strDest = "G:\BBST\"
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("Files", dbOpenDynaset)
Set rs1 = db.OpenRecordset("FTP INFO", dbOpenDynaset)
initials = "JC"  'Forms![Move FTP Files]![txtInitials]
rs.MoveFirst
Do While Not rs.EOF
    rs1.MoveFirst
    Do While Not rs1.EOF
    strsourcefolder = rs![FPath] & rs![FName]
    
    'On Error Resume Next
    
    If InStr(rs![FPath], rs1![Path Identifier]) > 0 And _
       InStr(rs![FName], rs1![Identifier 2]) > 0 And _
       InStr(rs![FName], rs1![Identifier]) > 0 Then
       
        Name strsourcefolder As strDest & rs1![Type] & rs1![CLIENT #] _
         & "-" & initials & "-" & rs![FName]
    
    ElseIf InStr(rs![FPath], rs1![Path Identifier]) > 0 And _
           InStr(rs![FName], rs1![Identifier]) > 0 Then
           
        Name strsourcefolder As strDest & Me!txt5 & rs1![CLIENT #] _
         & "-" & initials & "-" & rs![FName]
         
    End If
    
    rs1.MoveNext
    Loop
rs.MoveNext
Loop
rs.Close
rs1.Close
Set rs = Nothing
Set rs1 = Nothing
 

JDeezy

Registered User
Nope no change.

I think it might be a problem with the loop but in the table I have if it has criteria 9556 and SSN in the file name to name it O-[Fname] and if it is just 9556 in the file name N-[Fname] but it always names it with the N reguardless.

I got it to work but its slow, I only used one If statement in the private sub and made multiple subs. Then it will call on each one of those subs in order on click. Not how I would have like to done it since it will be looping through around 500 files a day but if you have any other ideas on how to revamp the code I am all ears.
 

Beetle

Duly Registered Boozer
Well, the only difference I see is that in one case you want to prefix the file name with the value from rs1![Type] and in the other case you want to prefix it with the value from txt5. Are you able to confirm that rs1![Type] has the correct value for the record you are currently on in the loop? You could test it during run time with message boxes. Here is another modified version that will display the new file name in a message box before it renames the source folder. Run it on a small sub-set of your records because it will fire a message box on every loop that satisfies one of the two criteria.

Code:
Dim strsourcefolder As String, strDest As String
Dim strNewFolderName As String
Dim initials As String, clientNum As String
strDest = "G:\BBST\"
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("Files", dbOpenDynaset)
Set rs1 = db.OpenRecordset("FTP INFO", dbOpenDynaset)
initials = "JC"  'Forms![Move FTP Files]![txtInitials]
rs.MoveFirst
Do While Not rs.EOF
    rs1.MoveFirst
    Do While Not rs1.EOF
    strsourcefolder = rs![FPath] & rs![FName]
    
    'On Error Resume Next
    
    If InStr(rs![FPath], rs1![Path Identifier]) > 0 And _
       InStr(rs![FName], rs1![Identifier 2]) > 0 And _
       InStr(rs![FName], rs1![Identifier]) > 0 Then
        
        strNewFolderName = strDest & rs1![Type] & rs1![CLIENT #] _
         & "-" & initials & "-" & rs![FName]
         
        MsgBox "Type is " & rs1![Type] & vbCrLf _
             & "New name is " & strNewFolderName
        
        Name strsourcefolder As strNewFolderName
    
    ElseIf InStr(rs![FPath], rs1![Path Identifier]) > 0 And _
           InStr(rs![FName], rs1![Identifier]) > 0 Then
           
        strNewFolderName = strDest & Me!txt5 & rs1![CLIENT #] _
         & "-" & initials & "-" & rs![FName]
         
        MsgBox "txt5 is " & Me!txt5 & vbCrLf _
             & "New name is " & strNewFolderName
        
        Name strsourcefolder As strNewFolderName
         
    End If
    
    rs1.MoveNext
    Loop
rs.MoveNext
Loop
rs.Close
rs1.Close
Set rs = Nothing
Set rs1 = Nothing
 

JDeezy

Registered User
Haha, already beat you to that one. That is why there is a txt5 there because i forgot to change the code. But I tried that but it exports differently then whats in the text box so that is what really confused me.
 
Top