Help with Recordset Case Statement

[dssjon]

I am a newbie to Access and VBA and I'm having trouble finding how to determine whether a file is a zip or txt from a recordset, and calling the appropriate function for said files. Does anyone see anything wrong with this? Help is greatly appreciated.

Sub Test_2(strTableName As String)

Dim objRecordset As Recordset
Dim strUnzipOrMoveFile_RETURN As String
Dim strUnzipFile_RETURN As String

Set objRecordset = CurrentDb.OpenRecordset("SELECT * FROM " & strTableName)

Do While Not objRecordset.EOF
    
    Select Case Right(objRecordset.Fields("Source"), 4)
        Case ".zip"
            'Unzip it
            strUnzipOrMoveFile_RETURN = UnzipFile(CVar(objRecordset.Fields("Source")), CVar(objRecordset.Fields("Destination")))
        Case ".txt"
            'Move it
            strUnzipOrMoveFile_RETURN = moveFiles(CVar(objRecordset.Fields("Source")), CVar(objRecordset.Fields("Destination")))
    End Select
    
    objRecordset.MoveNext
        
    Loop
End Sub

 

[Crowley16]

I don't think you can use complicated evaluations in a case, try using if's instead...

if right(obj.fields("name"), 4) = ".zip" then
elseif ...
else
endif



--------------------
Procrastinate Now!

 

[dssjon]

Okay, I've changed the code to an If else statement but it's still broken. Is this reading the file extensions from the recordset properly?

 

[AlexCuse]

I have used this with a case before:

Select Case Left(PlanCode, 2)

And it works perfectly. I have not tried it using fields from a recordset though.

What exactly do you have stored in your table? Is it simply file names, or file names with directory attached? I have a hunch there is a problem in there somewhere.

Hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[dssjon]

I have source paths with file names and destination paths for example

under source field: c:\test\files.zip
under destination field: c:\test\movefileshere\

 

[Crowley16]

try outputting the contents of your recordset to see that you've got sensible results in there...

--------------------
Procrastinate Now!

 

[dssjon]

When I call unzipfiles or movefiles by themselves they work as intended. It's only when I try to use them in the case statement..

 

[AlexCuse]

Can you post the code for UnzipFiles and MoveFiles functions? I don't see why you are assigning their output to strUnzipOrMoveFile_RETURN

Ignorance of certain subjects is a great part of wisdom

 

[dssjon]

Indeed. I'm a newbie so I know there are probably glaring problems in my code. Here are the functions:

Function UnzipFile(varFileName_SOURCE_COMPLETE As Variant, varFolderName_DESTINATION_COMPLETE As Variant) As String
'Arguments should be like "C:\TestFile.zip", "C:\TestFolder"
'Function should be used like this:
'Dim strUnzipFile_RETURNVALUE As String
'   strUnzipFile_RETURNVALUE = UnzipFile("C:\TestFile.zip", "C:\TestFolder")
'   If Left(strUnzipFile_RETURNVALUE, 7) <> "Success" Then ...

On Error GoTo Function_Execution_Failure
    
Dim objFSO As New FileSystemObject
Dim objShellApplication As Variant
Dim objFiles_SOURCE As Variant
Dim objFolder_DESTINATION As Variant

    'Check if source file exists
    If objFSO.FileExists(varFileName_SOURCE_COMPLETE) = False Then
        UnzipFile = "Error: file " & CStr(varFileName_SOURCE_COMPLETE) & " does not exist, aborting operation."
        GoTo Function_Execution_Failure
    End If
    
    'Check if destination folder exists
    If objFSO.FolderExists(CStr(varFolderName_DESTINATION_COMPLETE)) = False Then
        'If not - create it
        objFSO.CreateFolder (CStr(varFolderName_DESTINATION_COMPLETE))
    End If

    'Create the required Shell objects
    Set objShellApplication = CreateObject("Shell.Application")
    
    'Create a reference to the files and folders in the ZIP file
    Set objFiles_SOURCE = objShellApplication.NameSpace(varFileName_SOURCE_COMPLETE).Items()
    
    'Create a reference to the target folder
    Set objFolder_DESTINATION = objShellApplication.NameSpace(varFolderName_DESTINATION_COMPLETE)
    
    'UnZIP the files
    objFolder_DESTINATION.CopyHere objFiles_SOURCE, 256
    
    'Keep code waiting until compression is done and file is moved from cache to destination
    Do Until objFolder_DESTINATION.Items.Count = objFiles_SOURCE.Count
        Wait
    Loop
    
Function_Execution_Success:
    
    'Assign 'Successful' return value
    UnzipFile = "Successfully extracted:" & vbCrLf & CStr(varFileName_SOURCE_COMPLETE) & vbCrLf & "to:" & vbCrLf & CStr(varFolderName_DESTINATION_COMPLETE)
    'And skip to function's final part
    GoTo Quit_Function
    
Function_Execution_Failure:

    'Assign 'Failure' return value
    UnzipFile = "Error: " & Err.Description
    
Quit_Function:

    'Release objects
    Set objFSO = Nothing
    Set objShellApplication = Nothing
    Set objFiles_SOURCE = Nothing
    Set objFolder_DESTINATION = Nothing
    
End Function

and also

Function moveFiles(varFileName_SOURCE_COMPLETE As Variant, varFolderName_DESTINATION_COMPLETE As Variant) As String
    Dim fso
    Dim sfol As String, dfol As String
        sfol = varFileName_SOURCE_COMPLETE ' change to match the source folder path
        dfol = varFolderName_DESTINATION_COMPLETE ' change to match the destination folder path
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    
        fso.MoveFile (varFileName_SOURCE_COMPLETE), varFolderName_DESTINATION_COMPLETE
    
    If Err.Number = 53 Then MsgBox "File not found"
End Function

 

[AlexCuse]

First thing you can do here is try placing a message box inside both of those fucntions (right at the top) to make sure your code is actually running them.

<aside>

I would consider making inputs for the functions strings, to get rid of all the unnecessary casting (because really, what is fso going to be able to work with besides a string?) and changing the return type to boolean (so it returns simply true or false).

Then, you could use your function like this:

If MoveFiles(MyFile, MyDest) Then
MyVariable = "True"
Else
MyVariable = "False"
End if

This is much more efficient than detecting a particular output string.

See how you get on, and if your code is actually making it into those functions or not. THat will help determine how to proceed.

GOod Luck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[dssjon]

Thanks for the advice. I will try it out.