cssamerican
03-31-2006, 12:23 PM
I have a recently been redoing one of my spreadsheets. It originally was basically a database program than ran using loops and arrays. Needless to say, it began to bog down and was a real pain to keep updated. My new version retrieves data from worksheets with a SQL statement using DAO. The report feature currently has two reports a detailed one that shows individual user activity within a budget code, and a summary report that shows a summary of departmental activity for all budget codes.
The goal is to have a department head enter a password when they open up the workbook. Then that department head can only pull data that corresponds to budget codes that they are responsible for. When they choose to do a detail report I want for them to only be able to see/select budget codes from a combo box that they are in charge of. Then when they choose the summary report I would like it to display a summary of all departmental activity for all the budget codes that they are responsible for. Please note that each department head could be responsible for multiple budget codes. This doesn?t have to be really secure, I am not dealing with hackers or anything like that.
I had all this working when I was retrieving my data the hard way, but for some reason I am having a hard time figuring out to control access to the summary report when using this better method of data aquisition. The way I am controlling the access to the detailed reports is with the following code:
Private Sub UserForm_Initialize()
Dim NBC As Integer
Dim CounterOne As Integer
NBC = 151
If Worksheets(2).Cells(1, 5) = "Master" Then
For CounterOne = 1 To NBC
cbBudget_Code.AddItem Worksheets(2).Cells(CounterOne, 2).Value
Next
Else
For CounterOne = 1 To NBC
If Worksheets(2).Cells(CounterOne, 3).Value = Worksheets(2).Cells(1, 5) Then
cbBudget_Code.AddItem Worksheets(2).Cells(CounterOne, 2).Value
End If
Next
End If
End Sub
I know it is a pretty pathetic excuse for password protection but the idea behind it works well enough for what I am using it for. My one complaint is I would prefer to not have to manually keep up with the number of budget codes if possible. But as of now I can live with it if I can get some type of system to control the summary reports.
I am not sure if this is necessary, but this is my new code that is retrieving the data and putting it on the page:
Sub DAO_Report_Engine()
Const stExtens As String = "Excel 8.0;HDR=Yes;IMEX=1"
'Variables for DAO.
Dim DAO_ws As DAO.Workspace
Dim DAO_db As DAO.Database
Dim DAO_rs As DAO.Recordset
Dim strDb As String
Dim strSQLSummary As String
Dim strSQLDetail As String
strSQLSummary = "SELECT [Sheet2$].Department, " & vbNewLine & _
"[Sheet2$].Department_Head, " & vbNewLine & _
"[Sheet2$].Department_Code, " & vbNewLine & _
"SUM([Sheet1$].Centralized), " & vbNewLine & _
"SUM([Sheet1$].Paper), " & vbNewLine & _
"SUM([Sheet1$].IDCard), " & vbNewLine & _
"SUM([Sheet1$].Print_Management), " & vbNewLine & _
"SUM([Sheet1$].Local+[Sheet1$].Network) AS 'NCPrints', " & vbNewLine & _
"SUM([Sheet1$].Color) AS 'CPrints', " & vbNewLine & _
"SUM(SNXXXXX01+SNXXXXX02+SNXXXXX03+SNXXXXX04+SNXXXXX05+" _
& "SNXXXXX06+SNXXXXX07+SNXXXXX08+SNXXXXX09+SNXXXXX10+SNXXXXX11+"_
& "SNXXXXX12+SNXXXXX13+SNXXXXX14+SNXXXXX15+SNXXXXX16) " _
& "AS 'CCopies' " & vbNewLine & _
"FROM [Sheet2$] " & vbNewLine & _
"INNER JOIN [Sheet1$]" & vbNewLine & _
"ON [Sheet2$].Department_Code=[Sheet1$].Budget_Code " & vbNewLine & _
"GROUP BY Department, Department_Head, Department_Code " & vbNewLine & _
"ORDER BY Department_Code"
strSQLDetail = "SELECT Last_Name, " & vbNewLine & _
"First_Name, " & vbNewLine & _
"Budget_Code, " & vbNewLine & _
"SUM(Centralized), " & vbNewLine & _
"SUM(Paper), " & vbNewLine & _
"SUM(IDCard), " & vbNewLine & _
"SUM(Print_Management), " & vbNewLine & _
"SUM(Local+Network) AS 'NCPrints', " & vbNewLine & _
"SUM(Color) AS 'CPrints', " & vbNewLine & _
"SUM(SNXXXXX01+SNXXXXX02+SNXXXXX03+SNXXXXX04+SNXXXXX05+" _
& "SNXXXXX06+SNXXXXX07+SNXXXXX08+SNXXXXX09+SNXXXXX10+SNXXXXX11+"_
& "SNXXXXX12+SNXXXXX13+SNXXXXX14+SNXXXXX15+SNXXXXX16) " _
& "AS 'CCopies' " & vbNewLine & _
"FROM [Sheet1$] " & vbNewLine & _
"WHERE Budget_Code = '" & UserForm1.cbBudget_Code.Value & "' " & vbNewLine & _
"GROUP BY Last_Name, First_Name, Budget_Code"
'Variables for Excel.
Dim wbBook As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rnTarget As Range
Dim rsCounter As Integer
Set wbBook = ActiveWorkbook
Set wsTarget = wbBook.Worksheets("Reports")
With wsTarget
.Cells.ClearContents
.Range("D6").Value = "Centralized"
.Range("D7").Value = "Production"
.Range("E7").Value = "Paper"
.Range("F7").Value = "ID Card"
.Range("G6").Value = "Print"
.Range("G7").Value = "Management"
.Range("H6").Value = "Non-CPrints"
.Range("H7").Value = "Rate is at .0056?"
.Range("I6").Value = "CPrints"
.Range("I7").Value = "Rate is at 8?"
.Range("J6").Value = "CCopies"
.Range("J7").Value = "Rate is at 8?"
.Range("K6").Value = "Cost of Impressions"
.Range("K7").Value = "At Current Rates"
Set rnTarget = .Range("A8")
End With
strDb = wbBook.FullName
'Instantiate the DAO objects.
Set DAO_ws = DBEngine.Workspaces(0)
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
If UserForm1.strReport = "Detail" Then
Set DAO_rs = DAO_db.OpenRecordset(strSQLDetail, dbOpenForwardOnly)
With wsTarget
.Range("A7").Value = "Last Name"
.Range("B7").Value = "First Name"
.Range("C7").Value = "Budget Code"
End With
ElseIf UserForm1.strReport = "Summary" Then
Set DAO_rs = DAO_db.OpenRecordset(strSQLSummary, dbOpenForwardOnly)
With wsTarget
.Range("A7").Value = "Department"
.Range("B7").Value = "Department Head"
.Range("C7").Value = "Budget Code"
End With
Else
MsgBox "No reports selected.", vbCritical
Exit Sub
End If
'Write the Recordset to the target range.
rnTarget.CopyFromRecordset DAO_rs
'Close the instances.
DAO_rs.Close
DAO_db.Close
DAO_ws.Close
'Release objects from memory.
Set DAO_rs = Nothing
Set DAO_db = Nothing
Set DAO_ws = Nothing
End Sub
Any advice would be appreciated. If you need me to post upload either one of my files that is fine, but I will need some time to dummy up the data.
The goal is to have a department head enter a password when they open up the workbook. Then that department head can only pull data that corresponds to budget codes that they are responsible for. When they choose to do a detail report I want for them to only be able to see/select budget codes from a combo box that they are in charge of. Then when they choose the summary report I would like it to display a summary of all departmental activity for all the budget codes that they are responsible for. Please note that each department head could be responsible for multiple budget codes. This doesn?t have to be really secure, I am not dealing with hackers or anything like that.
I had all this working when I was retrieving my data the hard way, but for some reason I am having a hard time figuring out to control access to the summary report when using this better method of data aquisition. The way I am controlling the access to the detailed reports is with the following code:
Private Sub UserForm_Initialize()
Dim NBC As Integer
Dim CounterOne As Integer
NBC = 151
If Worksheets(2).Cells(1, 5) = "Master" Then
For CounterOne = 1 To NBC
cbBudget_Code.AddItem Worksheets(2).Cells(CounterOne, 2).Value
Next
Else
For CounterOne = 1 To NBC
If Worksheets(2).Cells(CounterOne, 3).Value = Worksheets(2).Cells(1, 5) Then
cbBudget_Code.AddItem Worksheets(2).Cells(CounterOne, 2).Value
End If
Next
End If
End Sub
I know it is a pretty pathetic excuse for password protection but the idea behind it works well enough for what I am using it for. My one complaint is I would prefer to not have to manually keep up with the number of budget codes if possible. But as of now I can live with it if I can get some type of system to control the summary reports.
I am not sure if this is necessary, but this is my new code that is retrieving the data and putting it on the page:
Sub DAO_Report_Engine()
Const stExtens As String = "Excel 8.0;HDR=Yes;IMEX=1"
'Variables for DAO.
Dim DAO_ws As DAO.Workspace
Dim DAO_db As DAO.Database
Dim DAO_rs As DAO.Recordset
Dim strDb As String
Dim strSQLSummary As String
Dim strSQLDetail As String
strSQLSummary = "SELECT [Sheet2$].Department, " & vbNewLine & _
"[Sheet2$].Department_Head, " & vbNewLine & _
"[Sheet2$].Department_Code, " & vbNewLine & _
"SUM([Sheet1$].Centralized), " & vbNewLine & _
"SUM([Sheet1$].Paper), " & vbNewLine & _
"SUM([Sheet1$].IDCard), " & vbNewLine & _
"SUM([Sheet1$].Print_Management), " & vbNewLine & _
"SUM([Sheet1$].Local+[Sheet1$].Network) AS 'NCPrints', " & vbNewLine & _
"SUM([Sheet1$].Color) AS 'CPrints', " & vbNewLine & _
"SUM(SNXXXXX01+SNXXXXX02+SNXXXXX03+SNXXXXX04+SNXXXXX05+" _
& "SNXXXXX06+SNXXXXX07+SNXXXXX08+SNXXXXX09+SNXXXXX10+SNXXXXX11+"_
& "SNXXXXX12+SNXXXXX13+SNXXXXX14+SNXXXXX15+SNXXXXX16) " _
& "AS 'CCopies' " & vbNewLine & _
"FROM [Sheet2$] " & vbNewLine & _
"INNER JOIN [Sheet1$]" & vbNewLine & _
"ON [Sheet2$].Department_Code=[Sheet1$].Budget_Code " & vbNewLine & _
"GROUP BY Department, Department_Head, Department_Code " & vbNewLine & _
"ORDER BY Department_Code"
strSQLDetail = "SELECT Last_Name, " & vbNewLine & _
"First_Name, " & vbNewLine & _
"Budget_Code, " & vbNewLine & _
"SUM(Centralized), " & vbNewLine & _
"SUM(Paper), " & vbNewLine & _
"SUM(IDCard), " & vbNewLine & _
"SUM(Print_Management), " & vbNewLine & _
"SUM(Local+Network) AS 'NCPrints', " & vbNewLine & _
"SUM(Color) AS 'CPrints', " & vbNewLine & _
"SUM(SNXXXXX01+SNXXXXX02+SNXXXXX03+SNXXXXX04+SNXXXXX05+" _
& "SNXXXXX06+SNXXXXX07+SNXXXXX08+SNXXXXX09+SNXXXXX10+SNXXXXX11+"_
& "SNXXXXX12+SNXXXXX13+SNXXXXX14+SNXXXXX15+SNXXXXX16) " _
& "AS 'CCopies' " & vbNewLine & _
"FROM [Sheet1$] " & vbNewLine & _
"WHERE Budget_Code = '" & UserForm1.cbBudget_Code.Value & "' " & vbNewLine & _
"GROUP BY Last_Name, First_Name, Budget_Code"
'Variables for Excel.
Dim wbBook As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rnTarget As Range
Dim rsCounter As Integer
Set wbBook = ActiveWorkbook
Set wsTarget = wbBook.Worksheets("Reports")
With wsTarget
.Cells.ClearContents
.Range("D6").Value = "Centralized"
.Range("D7").Value = "Production"
.Range("E7").Value = "Paper"
.Range("F7").Value = "ID Card"
.Range("G6").Value = "Print"
.Range("G7").Value = "Management"
.Range("H6").Value = "Non-CPrints"
.Range("H7").Value = "Rate is at .0056?"
.Range("I6").Value = "CPrints"
.Range("I7").Value = "Rate is at 8?"
.Range("J6").Value = "CCopies"
.Range("J7").Value = "Rate is at 8?"
.Range("K6").Value = "Cost of Impressions"
.Range("K7").Value = "At Current Rates"
Set rnTarget = .Range("A8")
End With
strDb = wbBook.FullName
'Instantiate the DAO objects.
Set DAO_ws = DBEngine.Workspaces(0)
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
If UserForm1.strReport = "Detail" Then
Set DAO_rs = DAO_db.OpenRecordset(strSQLDetail, dbOpenForwardOnly)
With wsTarget
.Range("A7").Value = "Last Name"
.Range("B7").Value = "First Name"
.Range("C7").Value = "Budget Code"
End With
ElseIf UserForm1.strReport = "Summary" Then
Set DAO_rs = DAO_db.OpenRecordset(strSQLSummary, dbOpenForwardOnly)
With wsTarget
.Range("A7").Value = "Department"
.Range("B7").Value = "Department Head"
.Range("C7").Value = "Budget Code"
End With
Else
MsgBox "No reports selected.", vbCritical
Exit Sub
End If
'Write the Recordset to the target range.
rnTarget.CopyFromRecordset DAO_rs
'Close the instances.
DAO_rs.Close
DAO_db.Close
DAO_ws.Close
'Release objects from memory.
Set DAO_rs = Nothing
Set DAO_db = Nothing
Set DAO_ws = Nothing
End Sub
Any advice would be appreciated. If you need me to post upload either one of my files that is fine, but I will need some time to dummy up the data.