VBA Counting Records Based on Select Case (1 Viewer)

[Oreynolds]

Hi, I have been toying with this module for a while and wonder if anyone can help. I have the following code which looks up some files relevant to a project record and then copies them to various directories so they are correctly catalogued for distribution to a client.

As it stand the module creates all the folders first and then copies the files. It creates the folders irrelevant of whether there are actually going to be any of the relevant file types to add to the folder. As a result in many cases empty folders are created and left.

What I would like to do is count the records for each case first, and then only if there are records for that case then create the folder. I realise I could do this be querying the recordset with a Where clause for each case and then counting the records but this seems long winded and I assumed there would be and easier way using a similar Select Case statement as I have written. Any ideas?

Thanks

'Check to see if above directorys hae been previously created
    If FolderExists(FoldernameDestFIRE) = False Then
        Call MakeDirectory(FoldernameDestFIRE)
    End If
    If FolderExists(FoldernameDestINTRUDER) = False Then
        Call MakeDirectory(FoldernameDestINTRUDER)
    End If
    If FolderExists(FoldernameDestACCESS) = False Then
        Call MakeDirectory(FoldernameDestACCESS)
    End If
    If FolderExists(FoldernameDestCCTV) = False Then
        Call MakeDirectory(FoldernameDestCCTV)
    End If
    If FolderExists(FoldernameDestOTHER) = False Then
        Call MakeDirectory(FoldernameDestOTHER)
    End If
    
    strSQL = " SELECT [Quote Details].QuoteID, [Quote Details].ProductID, Products.ProductName, Products.DatasheetPath, (Mid(Nz(Products.DatasheetPath,""""),(InStrRev(Nz(Products.DatasheetPath,""""),""\"")+1),100)) AS Filename, Quotations.OrderNumber, Quotations.Discipline " & _
    " FROM ([Quote Details] LEFT JOIN Products ON [Quote Details].ProductID = Products.ProductID) LEFT JOIN Quotations ON [Quote Details].QuoteID = Quotations.QuoteID " & _
    " GROUP BY [Quote Details].QuoteID, [Quote Details].ProductID, Products.ProductName, Products.DatasheetPath, (Mid(Nz(Products.DatasheetPath,""""),(InStrRev(Nz(Products.DatasheetPath,""""),""\"")+1),100)), Quotations.OrderNumber, Quotations.Discipline " & _
    " HAVING(((Products.DatasheetPath) Is Not Null) And ((Quotations.OrderNumber)='" & Me.txtOrderNumber & "')) " & _
    " ORDER BY (Mid(Nz(Products.DatasheetPath,""""),(InStrRev(Nz(Products.DatasheetPath,""""),""\"")+1),100));"

    'Set the recordset and then loop through each product in returned recordset and copy each file at a time
    Set RS = CurrentDb.OpenRecordset(strSQL)
    Set fsObject = CreateObject("Scripting.FileSystemObject")

    With RS
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While (Not .EOF)
                Select Case RS!Discipline
                Case 1, 2, 3
                    fsObject.CopyFile RS!DatasheetPath, FoldernameDestFIRE
                Case 4, 5, 6, 7, 11, 12
                    fsObject.CopyFile RS!DatasheetPath, FoldernameDestOTHER
                Case 8
                    fsObject.CopyFile RS!DatasheetPath, FoldernameDestINTRUDER
                Case 9
                    fsObject.CopyFile RS!DatasheetPath, FoldernameDestACCESS
                Case 10
                    fsObject.CopyFile RS!DatasheetPath, FoldernameDestCCTV
                Case Else
                    MsgBox "Discipline " & RS!Discipline & " for " & RS!ProductName & " unknown"
                End Select
                
                'In the event of an error determine last file to be successfully copied
                strLastFile = RS!DatasheetPath
                strLastProductID = RS!ProductID
                strLastProductName = RS!ProductName
                'In the event of an error determine the total number of sucessful copies made
                lngCounter = lngCounter + 1
                .MoveNext
            Wend
        Else
            .Close
            MsgBox ("There are no quotes or products linked to this job")
            GoTo ExitSub
        End If
        MsgBox (lngCounter & " of " & RS.RecordCount & " Products with linked datasheets attached to this project have been successfully copied to the projects OM Manual\Datasheets folder")
        .Close

    End With
    
    Set fsObject = Nothing
    Set RS = Nothing

 

[theDBguy]

You could insert each MakeDirectory inside the appropriate Select Case. For example:

Select Case...
    If FolderExists... Then
        MakeDirectory...
    End If
    CopyFile...
...

 

[plog]

Agree with DBguy, but I think you can make your code a lot more efficient by reconfiguring your query and your code a little.

1. Make a table that maps RS!Discipline values to Destination folder names (1,2,3->FoldernameDestFIRE, 4,5,6,7,11,12->FoldernameDestOTHER, etc.)

2. Incorporate the above table into your query so that the query now contains the destination folder name and thus your recordset will.

3. Now you no longer need any Select cases in your code you simply move through your recordset, check to see if destination directory exists and makes it if not and moves rest of recordset data to it. This will kill at least 20 lines of code and makes it easier to read.

Also, the SQL of your query is a bit off. You have explicitly used a LEFT JOIN but your HAVING criteria effectively undoes the LEFT JOIN and makes it an INNER JOIN. A LEFT JOIN is meant to overlook NULL values in the LEFT JOINed table and let them into the results anyway. But when you apply criteria you undo that because a NULL value can never meet your criteria. You may still be getting the results you want, but you have used the wrong JOIN if so and should investigate.

 

[Oreynolds]

Agree with DBguy, but I think you can make your code a lot more efficient by reconfiguring your query and your code a little.

1. Make a table that maps RS!Discipline values to Destination folder names (1,2,3->FoldernameDestFIRE, 4,5,6,7,11,12->FoldernameDestOTHER, etc.)

2. Incorporate the above table into your query so that the query now contains the destination folder name and thus your recordset will.

3. Now you no longer need any Select cases in your code you simply move through your recordset, check to see if destination directory exists and makes it if not and moves rest of recordset data to it. This will kill at least 20 lines of code and makes it easier to read.

Also, the SQL of your query is a bit off. You have explicitly used a LEFT JOIN but your HAVING criteria effectively undoes the LEFT JOIN and makes it an INNER JOIN. A LEFT JOIN is meant to overlook NULL values in the LEFT JOINed table and let them into the results anyway. But when you apply criteria you undo that because a NULL value can never meet your criteria. You may still be getting the results you want, but you have used the wrong JOIN if so and should investigate.

Thanks for this, interesting approach. So, just thinking it through would I;

1) Create a temp table and copy across al the data sheet file information
2) Using select case update that table with the destination folder name info
3) Query that temp table and perform the file copying
4) Erase the temp table data

?

 

[plog]

It depends on how often you are going to run this code. If this is a one time deal, just make your code work as it is, don't put too much effort into making it pretty or efficient. If it is more than once that you will use this then you wouldn't use a temp table but a permanent table.

 

[Oreynolds]

It depends on how often you are going to run this code. If this is a one time deal, just make your code work as it is, don't put too much effort into making it pretty or efficient. If it is more than once that you will use this then you wouldn't use a temp table but a permanent table.

Ok thanks, yes this is something that is used potentially daily by the users