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
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
Code:
'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