I have no idea if this is possible, but I think i can do a workaround if it's not.
Bear with me as i'm going to try to explain this as best as i can.
My program is growing and, instead of creating a bunch of buttons for each report, I decided to create a table containing the report name (what the user sees), category, access report name (what the actual name in access is), description, and filter criteria (monthly filter, yearly filter, range filter, specific filter).
The user selects a category from a dropdown. After the category is selected, a listbox populates with all the reports under that category. The user then selects the report, types in the filter values, and hits generate. I got most of the reports working except for ones i need to use a dlookup function to find an ID.
Here is the code for the Generate button:
So for one of the reports that uses an specific ID (like getting an airpack inspection history report), the user enters the airpack number (E3-3) in the txtID field on the form. When they click generate it has to look up the ID from the airpack table using E3-3 as the where condition. The expression I have is the table's specific filter is
So basically the sub gets the above "command line" from the table and uses that as actual vba code.... can this even work?
Here is a picture of my table with the "command line" to give you a better idea of what I mean.
Bear with me as i'm going to try to explain this as best as i can.
My program is growing and, instead of creating a bunch of buttons for each report, I decided to create a table containing the report name (what the user sees), category, access report name (what the actual name in access is), description, and filter criteria (monthly filter, yearly filter, range filter, specific filter).
The user selects a category from a dropdown. After the category is selected, a listbox populates with all the reports under that category. The user then selects the report, types in the filter values, and hits generate. I got most of the reports working except for ones i need to use a dlookup function to find an ID.
Here is the code for the Generate button:
Code:
Private Sub CmdGenerateReport_Click()
On Error GoTo errhandler
Dim strReportName As String 'AccessReportName
Dim strPerm As String 'Permissions
Dim strPreFilter As String 'MonthlyFilter, YearlyFilter, RangeFilter, SpecificIdFilter
Dim strOpenArgs As String 'Options to bypass filters (montly, yearly, etc...)
Dim strMonthFilter As String 'String that holds the where conditon
Dim strYearFilter As String 'String that holds the where conditon
Dim strFilter As String 'String that holds the where conditon
'Fill string variables
'If nothing is in the "accessReportName" then that report isn't created yet
If IsNull(DLookup("[AccessReportName]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then
MsgBox "This report doesn't exist yet.", vbInformation
Exit Sub
Else
strReportName = DLookup("[AccessReportName]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
End If
strPerm = DLookup("[Permissions]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
'The following if statement bypasses the filter step for general reports like inventory reports where there is not filter needed
If IsNull(DLookup("[OpenArg]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo FilterStep
strOpenArgs = DLookup("[OpenArg]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
FilterStep:
MsgBox "Step 1 complete" 'Put in for testing purposes... take out when done
'If it's a general report, bypass filter section and just generate the report
Select Case strOpenArgs
Case "G"
GoTo OpenReport
End Select
'Create filter string
Select Case Frame8.Value
Case 1 'monthly
If IsNull(DLookup("[MonthlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo NextStep
strMonthFilter = DLookup("[MonthlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
strYearFilter = DLookup("[yearlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
strFilter = strMonthFilter & txtmonth.Value & " AND " & strYearFilter & txtyear.Value
Case 2 'yearly
If IsNull(DLookup("[YearlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo NextStep
strMonthFilter = DLookup("[YearlyFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
strFilter = strPreFilter & txtyear.Value
Case 3 'range 'I havn't tested this part yet, but i'm sure i'll have to have 2 string variables and combine them
If IsNull(DLookup("[RangeFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo NextStep
strPreFilter = DLookup("[RangeFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
Case 4 'Specific ID
If IsNull(DLookup("[SpecificIdFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)) Then GoTo NextStep
strPreFilter = DLookup("[SpecificIdFilter]", "[ReportName]", "[ID] = " & ListAvailableReports.Value)
strFilter = strPreFilter & TxtID.Value & "'"")"
Case Else
MsgBox "Please select report filter", vbExclamation, "Report"
Exit Sub
End Select
NextStep:
MsgBox strFilter
If strFilter = "" Then
MsgBox "This report isn't available with the filter you selected.", vbInformation
Exit Sub
End If
OpenReport:
'Open Report
DoCmd.OpenReport strReportName, acViewPreview, , strFilter, acWindowNormal, strOpenArgs
ExitProc:
Exit Sub
errhandler:
lngErr = Err.Number
strDesc = Err.Description
Call LogError(lngErr, strDesc, "Admin - CmdGenerateReport()")
Resume ExitProc
End Sub
So for one of the reports that uses an specific ID (like getting an airpack inspection history report), the user enters the airpack number (E3-3) in the txtID field on the form. When they click generate it has to look up the ID from the airpack table using E3-3 as the where condition. The expression I have is the table's specific filter is
Code:
"Airpack = " & DLookup("[ID]", "[Packs]", "[Dept ID] = "'
So basically the sub gets the above "command line" from the table and uses that as actual vba code.... can this even work?
Here is a picture of my table with the "command line" to give you a better idea of what I mean.