Option Compare Database
Option Explicit
''''
' Name:
' Date: Created Date
' Author: Gilbert Medel
' Current Version: 1.0
' Called by:
'
''''
' Notes:
' SRpt_Data references subreport object that must be on form
' Query_Selected_1, Query_Selected_2, Query_Default should be designed to include all data for the Reports and drop down
' Report_Based_on_Query_1, Report_Based_on_Query_2 , Report_Based_on_Query_Default should be designed to display the data with no filters
' Data filtering will be accomplished by generating the SQL after changing the combo box values.
' Combo_Box_Query_Select Will let users select the Query Data
' Combo_Box1 and Combo_Box2 will let the user select data from Query Data that is filtered after selecting other Combo_Box values
' After Combo_Box is updated the Control.RowSource Queries will be updated to filter other combo box values
' Filter_combo_Box# will hold each parameter for filtering. The operator will be AND for filtering the data
' Sub Generate_SQL will concatenate Query Name and Filter_'s To produce the SQL for the SRpt
' The SRpt_Data.SourceObject is reassigned and the Form will update.
''''
' Public Variables
''''
' Private Variables
Private SRpt_Recset As Object
Private Filter_Query_Select As Variant
Private Filter_Combo_Box1 As Variant
Private Filter_Combo_Box2 As Variant
Private Filter_ShowArchives As Variant
Private Query_Name As Variant
Private Sub_Report_Name As Variant
Private Temp_Query As Variant
''''
' Public properties
''''
' Public Functions that return values
''''
' Public Sub Routines
''''
' Private Functions
''*''
' Private Subs
Private Sub Form_Open(Cancel As Integer)
'Clears data until user selects combo box values
Clear_Data_Report
End Sub
Private Sub Combo_Box_Query_Select_AfterUpdate()
Select Case Combo_Query_Select
Case "Query 1 Name"
Query_Name = "Query_Selected_1"
Sub_Report_Name = "Report.Report_Based_on_Query_1"
Form_Title.Caption = "Report Name Query 1
Case "Query 2 Name"
Query_Name = "Query_Selected_2"
Sub_Report_Name = "Report.Report_Based_on_Query_2"
Form_Title.Caption = "Report Name Query 2
Case Else
If IsNull(Combo_Query_Select) Then
Combo_Query_Select = "Query_Default"
End If
Query_Name = "Q_Query_Default"
Sub_Report_Name = "Report.Report_Based_on_Query_Default"
Form_Title.Caption = "Report Name Default"
End Select
If Combo_Query_Select = "Archive" Then
Filter_ShowArchives = "(( " & Query_Name & ".ShowArchives)=False) "
Else
Filter_ShowArchives = "(( " & Query_Name & ".ShowArchives)=True) "
End If
Clear_Data_Report
Combo_Box1 = Null
Combo_Box2 = Null
Store_Form_Values
Generate_SQL
'Generate_Report
End Sub
Private Sub Combo_Box1_AfterUpdate()
Clear_Data_Report
Combo_Box2 = Null
Store_Form_Values
Generate_SQL
Generate_Report
End Sub
Private Sub Combo_Box2_AfterUpdate()
Clear_Data_Report
Store_Form_Values
Generate_SQL
Generate_Report
End Sub
Private Sub Clear_Data_Report()
SRpt_Data.SourceObject = ""
End Sub
Private Sub Store_Form_Values()
If IsNull(Combo_Query_Select) Then
Filter_Query_Select = " ((" & Query_Name & ".From_Query_Name) IS NOT NULL) "
Else
Filter_Query_Select = "((" & Query_Name & ".From_Query_Name)= " & Chr(39) & Combo_Query_Select & Chr(39) & ") "
Generate_SQL_For_Combo_Box1
End If
If IsNull(Combo_SWUPGRADE) Then
Filter_Combo_Box1 = " ((" & Query_Name & ".[Combo_Box1_Control_Field_Name]) IS NOT NULL) "
Generate_SQL_For_Combo_Box2
Else
Filter_Combo_Box1 = " ((" & Query_Name & ".[Combo_Box1_Control_Field_Name])= " & Chr(39) & Combo_Box1 & Chr(39) & ") "
Generate_SQL_For_Combo_Box2
End If
If IsNull(Combo_Box2) Then
Filter_Combo_Box2 = " ((" & Query_Name & ".[Combo_Box2_Control_Field_Name]) IS NOT NULL) "
Else
Filter_Combo_Box2 = " ((" & Query_Name & ".[Combo_Box2_Control_Field_Name])= " & Chr(39) & Combo_Box2 & Chr(39) & ") "
End If
End Sub
Private Sub Generate_SQL()
Set SRpt_Recset = CurrentDb.QueryDefs("Q_Data_From_Query_Name")
Temp_Query = " SELECT " & Query_Name & ".* " & _
"FROM " & Query_Name & " " & _
"WHERE (" & Filter_Query_Select & " AND " & Filter_Combo_Box1 & " AND " & Filter_Combo_Box2 & ") ORDER BY [Field_Name_For_Sorting];"
SRpt_Recset.SQL = Temp_Query
End Sub
Private Sub Generate_Report()
SRpt_Data.SourceObject = Sub_Report_Name
End Sub
Private Sub Generate_SQL_For_Combo_Box1()
Dim Only_Active As Variant
Combo_Box1.RowSource = "SELECT DISTINCT " & Query_Name & ".[Combo_Box1_Control_Field_Name], " & _
Query_Name & ".ShowArchives, " & Query_Name & ".From_Query_Name" & _
" FROM " & Query_Name & _
" WHERE (" & Filter_ShowArchives & " AND " & Filter_Query_Select & ");"
End Sub
Private Sub Generate_SQL_For_Combo_Box2()
Combo_Box2.RowSource = "SELECT DISTINCT " & Query_Name & ".[Combo_Box2_Control_Field_Name], " & _
Query_Name & ".ShowArchives, " & Query_Name & ".From_Query_Name" & _
" FROM " & Query_Name & _
" WHERE (" & Filter_ShowArchives & " AND " & Filter_Query_Select & " AND " & Filter_Combo_Box1 & ");"
End Sub
''*''
'End Code
