I am currently using a VBA code to split my report into pdf's and save them into folders based on specific fields in my query which works perfect.
When I create the folder I need to set the permissions to only a specific person who the PDF belongs to. I can't find the right procedure to also set the permissions to the folder. I need the folder to only have read access and I need the top level folders to have inherit permissions so they can see anything below them. For example.
Main Folder - John Smith (CEO) - has pdf in it that only he can view.
Sub Folder - Mark Jones (EVP) - has a pdf in it and John and Mark can read.
Sub Folder - Amy Clark (Reports to EVP) - has a pdf in it and Amy and John can see it and read it but John can not.
Sub Folder under Amy's Folder called Danny Grant (Reports to Amy) - John, Amy and Danny can view and read it.
This is the module I use to make sure the folder exist:
Public Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long
This is my code to split the report into a folder. This is the code that creates the Main Folder.
Private Sub Top4_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strPath As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select DISTINCT DirectReports, FolderName from [_ceodirect]", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
DoCmd.OpenReport "rpt1PAGER_Final_BonusOnly_Distribution_ceo", acViewPreview, , "[DirectReports]='" & rst!DirectReports & "'"
strPath = "M:\PDFs\1Pgrs\CEO"
Call MakeSureDirectoryPathExists(strPath)
DoCmd.OutputTo acOutputReport, "rpt1PAGER_Final_BonusOnly_Distribution_ceo", acFormatPDF, strPath & rst!DirectReports & ".pdf", False
DoCmd.Close acReport, "rpt1PAGER_Final_BonusOnly_Distribution_ceo"
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
strSQL = ""
End Sub
This code would create the first round of Sub Folders. I have 3 other sets of code that does the same thing for those who report to the EVP.
Private Sub EVP_s_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strPath As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select DISTINCT Top4_Name, EVPSVP_Name, EVPSVP_JobID, FolderName from [_evpsdirect]", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
DoCmd.OpenReport "rpt1PAGER_Final_BonusOnly_Distribution_evps", acViewPreview, , "[Top4_Name]='" & rst!Top4_Name & "'"
strPath = "M:\PDFs\1Pgrs\ceo" & rst!FolderName & ""
Call MakeSureDirectoryPathExists(strPath)
DoCmd.OutputTo acOutputReport, "rpt1PAGER_Final_BonusOnly_Distribution_evps", acFormatPDF, strPath & rst!Top4_Name & " Direct Reports.pdf", False
DoCmd.Close acReport, "rpt1PAGER_Final_BonusOnly_Distribution_evps"
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
strSQL = ""
End Sub
The folder name is the name of name of the person who needs the permissions set for on my network. I have their name and their email address which I use if I am manually setting the permissions on the folder.
I am very new to VBA so if you give suggestions can you please write them as if I know nothing about VBA please.
Any help would be greatly appreciated. I hope I have explained the need correctly.
Thanks in advance,