{"id":487,"date":"2019-02-24T14:49:03","date_gmt":"2019-02-24T22:49:03","guid":{"rendered":"https:\/\/doubleecpu.com\/?page_id=487"},"modified":"2019-06-26T00:39:34","modified_gmt":"2019-06-26T07:39:34","slug":"vba-access-combo-box-updating","status":"publish","type":"page","link":"https:\/\/doubleecpu.com\/index.php\/microsoft\/visual-basic-for-applications\/vba-access-combo-box-updating\/","title":{"rendered":"VBA Access Combo Box Updating"},"content":{"rendered":"\n
Option Compare Database
 Option Explicit
 ''''
 ' Name:
 ' Date: Created Date
 ' Author: Gilbert Medel
 ' Current Version: 1.0
 '   Called by:
 '
 ''<\/em>''
 ' 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
''<\/em>''
' 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
''<\/em>''
' Public Functions that return values
''''
' Public Sub Routines
''<\/em>''
' Private Functions
''*<\/em><\/strong>''
' Private Subs
Private Sub Form_Open(Cancel As Integer)
     'Clears data until user selects combo box values
     Clear_Data_Report
 End Sub<\/pre>\n\n\n\n
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
<\/pre>\n\n\n\n
  Private Sub Combo_Box1_AfterUpdate()
     Clear_Data_Report
     Combo_Box2 = Null
     Store_Form_Values
     Generate_SQL
     Generate_Report
 End Sub
<\/pre>\n\n\n\n
 
 Private Sub Combo_Box2_AfterUpdate()
     Clear_Data_Report
     Store_Form_Values
     Generate_SQL
     Generate_Report
 End Sub
<\/pre>\n\n\n\n
 Private Sub Clear_Data_Report()
     SRpt_Data.SourceObject = \"\"
 End Sub  <\/pre>\n\n\n\n
Private Sub Store_Form_Values()
\n    If IsNull(Combo_Query_Select) Then
\n        Filter_Query_Select = \" ((\" & Query_Name & \".From_Query_Name) IS NOT NULL) \"
\n    Else
\n        Filter_Query_Select = \"((\" & Query_Name & \".From_Query_Name)= \" & Chr(39) & Combo_Query_Select & Chr(39) & \") \"
\n        Generate_SQL_For_Combo_Box1
\n    End If
\n    If IsNull(Combo_SWUPGRADE) Then
\n        Filter_Combo_Box1 = \" ((\" & Query_Name & \".[Combo_Box1_Control_Field_Name]) IS NOT NULL) \"
\n        Generate_SQL_For_Combo_Box2
\n    Else
\n        Filter_Combo_Box1 = \" ((\" & Query_Name & \".[Combo_Box1_Control_Field_Name])= \" & Chr(39) & Combo_Box1 & Chr(39) & \") \"
\n        Generate_SQL_For_Combo_Box2
\n    End If
\n    If IsNull(Combo_Box2) Then
\n        Filter_Combo_Box2 = \" ((\" & Query_Name & \".[Combo_Box2_Control_Field_Name]) IS NOT NULL) \"
\n    Else
\n        Filter_Combo_Box2 = \" ((\" & Query_Name & \".[Combo_Box2_Control_Field_Name])= \" & Chr(39) & Combo_Box2 & Chr(39) & \") \"
\n    End If
\nEnd Sub<\/pre>\n\n\n\n
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 <\/pre>\n\n\n\n
 Private Sub Generate_Report()
     SRpt_Data.SourceObject = Sub_Report_Name
 End Sub <\/pre>\n\n\n\n
 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
 ''*<\/em><\/strong>''
 'End Code<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 ‘ … <\/p>\n
Read more “VBA Access Combo Box Updating”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":591,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-487","page","type-page","status-publish","hentry"],"featured_media_urls":[],"_links":{"self":[{"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/pages\/487","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/comments?post=487"}],"version-history":[{"count":0,"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/pages\/487\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/pages\/591"}],"wp:attachment":[{"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/media?parent=487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}