1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
|
'Update the filter of the component list
Public Sub FilterByComponent()
Dim ComponentlList As String
Dim ComponentlListPlatform As String
Dim ComponentlListProject As String
Dim ComponentlListPlatformProject As String
Dim ComponentListWithoutModule As String
Dim filter As String
'create the sql for the normal list of all the components with thier sub category
ComponentlList = "SELECT T_Components.*, T_ComponentsCategories.CSubCategory, T_ComponentsCategories.CCategory " & _
" FROM T_ComponentsCategories INNER JOIN T_Components ON T_ComponentsCategories.CCategoryReference = T_Components.CCategoryReference;"
'create the sql for the list of all the components with thier sub category with thier platform associated
ComponentlListPlatform = "SELECT DISTINCT T_Components.*, T_ComponentsCategories.*, T_Modules.Platform " & _
" FROM T_Modules INNER JOIN ((T_ComponentsCategories INNER JOIN T_Components ON T_ComponentsCategories.CCategoryReference = T_Components.CCategoryReference) INNER JOIN T_ModuleConstruction ON T_Components.ManufacturerReference = T_ModuleConstruction.ManufacturerReference) ON T_Modules.OrderNumber = T_ModuleConstruction.OrderNumber;"
'create the sql for the list of all the components with thier sub category with thier project associated
ComponentlListProject = "SELECT DISTINCT T_Components.*, T_ComponentsCategories.CSubCategory, T_Projects.ProjectName, T_ComponentsCategories.CCategory " & _
" FROM T_Projects INNER JOIN ((T_Modules INNER JOIN ((T_ComponentsCategories INNER JOIN T_Components ON T_ComponentsCategories.CCategoryReference = T_Components.CCategoryReference) INNER JOIN T_ModuleConstruction ON T_Components.ManufacturerReference = T_ModuleConstruction.ManufacturerReference) ON T_Modules.OrderNumber = T_ModuleConstruction.OrderNumber) INNER JOIN T_ProjectConstruction ON T_Modules.OrderNumber = T_ProjectConstruction.OrderNumber) ON T_Projects.ProjectReference = T_ProjectConstruction.ProjectReference;"
'create the sql for the list of all the components with thier sub category with thier platform and project associated
ComponentlListPlatformProject = "SELECT DISTINCT T_Components.*, T_Modules.Platform, T_Projects.ProjectName, T_ComponentsCategories.CSubCategory, T_ComponentsCategories.CCategory " & _
" FROM T_Projects INNER JOIN ((T_Modules INNER JOIN ((T_ComponentsCategories INNER JOIN T_Components ON T_ComponentsCategories.CCategoryReference = T_Components.CCategoryReference) INNER JOIN T_ModuleConstruction ON T_Components.ManufacturerReference = T_ModuleConstruction.ManufacturerReference) ON T_Modules.OrderNumber = T_ModuleConstruction.OrderNumber) INNER JOIN T_ProjectConstruction ON T_Modules.OrderNumber = T_ProjectConstruction.OrderNumber) ON T_Projects.ProjectReference = T_ProjectConstruction.ProjectReference; "
'create the sql for the list of the components without modules associated
ComponentListWithoutModule = "SELECT T_Components.ManufacturerReference, T_Components.Manufacturer, T_Components.CCategoryReference, T_ComponentsCategories.CSubCategory, T_ComponentsCategories.CCategory " & _
" FROM T_ComponentsCategories INNER JOIN (T_Components LEFT JOIN T_ModuleConstruction ON T_Components.[ManufacturerReference] = T_ModuleConstruction.[ManufacturerReference]) ON T_ComponentsCategories.CCategoryReference = T_Components.CCategoryReference " & _
" WHERE (((T_ModuleConstruction.ManufacturerReference) Is Null)); "
filter = "true"
If Not IsNull(Me.txtComponentSearch) And Me.txtComponentSearch <> "" Then
filter = filter + " AND [ManufacturerReference] like ""*" + Me.txtComponentSearch + "*"""
End If
If Me![cbxFilterByComponent].Value Then
If Not IsNull(Me.cboComponentsCategories) And Me.cboComponentsCategories <> "-- All --" Then
filter = filter + " AND [CCategory] = '" + Me.cboComponentsCategories + " ' "
End If
If Not IsNull(Me.cboComponentsSubCategories) And Me.cboComponentsSubCategories <> "-- All --" Then
filter = filter + " AND [CSubCategory] = '" + Me.cboComponentsSubCategories + " ' "
End If
If Me.txtManufacturerNameComponent <> "" Then
filter = filter + " AND [Manufacturer] like ""*" + Me.txtManufacturerNameComponent + "*"""
End If
If Me.cbxWithoutModule Then
Forms![frmMain]![sfmComponentsList].Form.RecordSource = ComponentListWithoutModule
Else
If Not IsNull(Me.cboProjectComponent) And Me.cboProjectComponent <> "-- All --" Then
If Not IsNull(Me.cboPlatformComponent) And Me.cboPlatformComponent <> "-- All --" Then
Forms![frmMain]![sfmComponentsList].Form.RecordSource = ComponentlListPlatformProject
filter = filter + " AND [Platform] = '" + Me.cboPlatformComponent + " ' "
Else
Forms![frmMain]![sfmComponentsList].Form.RecordSource = ComponentlListProject
End If
filter = filter + " AND [ProjectName] = '" + Me.cboProjectComponent + " ' "
Else
If Not IsNull(Me.cboPlatformComponent) And Me.cboPlatformComponent <> "-- All --" Then
Forms![frmMain]![sfmComponentsList].Form.RecordSource = ComponentlListPlatform
filter = filter + " AND [Platform] = '" + Me.cboPlatformComponent + " ' "
Else
Forms![frmMain]![sfmComponentsList].Form.RecordSource = ComponentlList
End If
End If
End If
Else
Forms![frmMain]![sfmComponentsList].Form.RecordSource = ComponentlList
End If
With Forms!frmMain.sfmComponentsList.Form
.filter = filter
.FilterOn = True
End With
Me.txtNumbComponents.Requery
End Sub |
Partager