E
Elvis72
This is a follow up from a post on 4.14.
OK, I have the original code working now EXCEPT I cannot select more than
one criteria AND after doing one search and I hit clear the results come up
blank.
Here is the Search Button:
Private Sub Command212_Click()
Dim strWhere As String
Dim lngLen As Long
If Me.cboFilterCommissioning = -1 Then
strWhere = strWhere & "([Commissioning] = True) AND "
ElseIf Me.cboFilterCommissioning = 0 Then
strWhere = strWhere & "([Commissioning] = False) AND "
End If
If Me.cboFilterSteamLineBlowing = -1 Then
strWhere = strWhere & "([Steam Line Blowing] = True) AND "
ElseIf Me.cboFilterSteamLineBlowing = 0 Then
strWhere = strWhere & "([Steam Line Blowing] = False) AND "
End If
If Me.cboFilterChemicalCleaning = -1 Then
strWhere = strWhere & "([Chemical Cleaning] = True) AND "
ElseIf Me.cboFilterChemicalCleaning = 0 Then
strWhere = strWhere & "([Chemical Cleaning] = False) AND "
End If
If Me.cboFilterCommissioningIandE = -1 Then
strWhere = strWhere & "([Commissioning I and E] = True) AND "
ElseIf Me.cboFilterCommissioningIandE = 0 Then
strWhere = strWhere & "([Commissioning I and E] = False) AND "
End If
If Me.cboFilterTurnoverCoordination = -1 Then
strWhere = strWhere & "([Turnover Coordination] = True) AND "
ElseIf Me.cboFilterTurnoverCoordination = 0 Then
strWhere = strWhere & "([Turnover Coordination] = False) AND "
End If
If Me.cboFilterCommissioningPlanning = -1 Then
strWhere = strWhere & "([Commissioning Planning] = True) AND "
ElseIf Me.cboFilterCommissioningPlanning = 0 Then
strWhere = strWhere & "([Commissioning Planning] = False) AND "
End If
If Me.cboFilterFacilityEvaluation = -1 Then
strWhere = strWhere & "([Facility Evaluation] = True) AND "
ElseIf Me.cboFilterFacilityEvaluation = 0 Then
strWhere = strWhere & "([Facility Evaluation] = False) AND "
End If
If Me.cboFilterOperationPreparedness = -1 Then
strWhere = strWhere & "([Operation Preparedness] = True) AND "
ElseIf Me.cboFilterOperationPreparedness = 0 Then
strWhere = strWhere & "([Operation Preparedness] = False) AND "
End If
If Me.cboFilterCorrosionProgram = -1 Then
strWhere = strWhere & "([Corrosion Program] = True) AND "
ElseIf Me.cboFilterCorrosionProgram = 0 Then
strWhere = strWhere & "([Corrosion Program] = False) AND "
End If
If Me.cboFilterFugitiveEmissionTesting = -1 Then
strWhere = strWhere & "([Fugitive Emission Testing] = True) AND "
ElseIf Me.cboFilterFugitiveEmissionTesting = 0 Then
strWhere = strWhere & "([Fugitive Emission Testing] = False) AND "
End If
If Me.cboFilterNDTCertification = -1 Then
strWhere = strWhere & "([NDT Certification] = True) AND "
ElseIf Me.cboFilterNDTCertification = 0 Then
strWhere = strWhere & "([NDT Certification] = False) AND "
End If
If Me.cboFilterMaintenance = -1 Then
strWhere = strWhere & "([Maintenance] = True) AND "
ElseIf Me.cboFilterMaintenance = 0 Then
strWhere = strWhere & "([Maintenance] = False) AND "
End If
If Me.cboFilterMaintenancePlanning = -1 Then
strWhere = strWhere & "([Maintenance Planning] = True) AND "
ElseIf Me.cboFilterMaintenancePlanning = 0 Then
strWhere = strWhere & "([Maintenance Planning] = False) AND "
End If
If Me.cboFilterPiping = -1 Then
strWhere = strWhere & "([Piping] = True) AND "
ElseIf Me.cboFilterPiping = 0 Then
strWhere = strWhere & "([Piping] = False) AND "
End If
If Me.cboFilterRotating = -1 Then
strWhere = strWhere & "([Rotating] = True) AND "
ElseIf Me.cboFilterRotating = 0 Then
strWhere = strWhere & "([Rotating] = False) AND "
End If
If Me.cboFilterMaintenanceIandE = -1 Then
strWhere = strWhere & "([Maintenance I and E] = True) AND "
ElseIf Me.cboFilterMaintenanceIandE = 0 Then
strWhere = strWhere & "([Maintenance I and E] = False) AND "
End If
If Me.cboFilterOperations = -1 Then
strWhere = strWhere & "([Operations] = True) AND "
ElseIf Me.cboFilterOperations = 0 Then
strWhere = strWhere & "([Operations] = False) AND "
End If
If Me.cboFilterOperationsPlanning = -1 Then
strWhere = strWhere & "([Operations Planning] = True) AND "
ElseIf Me.cboFilterOperationsPlanning = 0 Then
strWhere = strWhere & "([Operations Planning] = False) AND "
End If
If Me.cboFilterScheduling = -1 Then
strWhere = strWhere & "([Scheduling] = True) AND "
ElseIf Me.cboFilterScheduling = 0 Then
strWhere = strWhere & "([Scheduling] = False) AND "
End If
If Me.cboFilterInventoryofSupply = -1 Then
strWhere = strWhere & "([Inventory of Supply] = True) AND "
ElseIf Me.cboFilterInventoryofSupply = 0 Then
strWhere = strWhere & "([Inventory of Supply] = False) AND "
End If
If Me.cboFilterProductMovement = -1 Then
strWhere = strWhere & "([Product Movement] = True) AND "
ElseIf Me.cboFilterProductMovement = 0 Then
strWhere = strWhere & "([Product Movement] = False) AND "
End If
If Me.cboFilterStartUp = -1 Then
strWhere = strWhere & "([StartUp] = True) AND "
ElseIf Me.cboFilterStartUp = 0 Then
strWhere = strWhere & "([StartUp] = False) AND "
End If
If Me.cboFilterTemporary = -1 Then
strWhere = strWhere & "([Temporary] = True) AND "
ElseIf Me.cboFilterTemporary = 0 Then
strWhere = strWhere & "([Temporary] = False) AND "
End If
If Me.cboFilterLongTerm = -1 Then
strWhere = strWhere & "([LongTerm] = True) AND "
ElseIf Me.cboFilterLongTerm = 0 Then
strWhere = strWhere & "([LongTerm] = False) AND "
End If
If Me.cboFilterPandIDReviews = -1 Then
strWhere = strWhere & "([PandID Reviews] = True) AND "
ElseIf Me.cboFilterPandIDReviews = 0 Then
strWhere = strWhere & "([PandID Reviews] = False) AND "
End If
If Me.cboFilterPreliminary = -1 Then
strWhere = strWhere & "([Preliminary] = True) AND "
ElseIf Me.cboFilterPreliminary = 0 Then
strWhere = strWhere & "([Preliminary] = False) AND "
End If
If Me.cboFilterAsBuilt = -1 Then
strWhere = strWhere & "([As Built] = True) AND "
ElseIf Me.cboFilterAsBuilt = 0 Then
strWhere = strWhere & "([As Built] = False) AND "
End If
If Me.cboFilterHAZOPParticipation = -1 Then
strWhere = strWhere & "([HAZOP Participation] = True) AND "
ElseIf Me.cboFilterHAZOPParticipation = 0 Then
strWhere = strWhere & "([HAZOP Participation] = False) AND "
End If
If Me.cboFilterTraining = -1 Then
strWhere = strWhere & "([Training] = True) AND "
ElseIf Me.cboFilterTraining = 0 Then
strWhere = strWhere & "([Training] = False) AND "
End If
If Me.cboFilterCreateTrainingMaterial = -1 Then
strWhere = strWhere & "([Create Training Material] = True) AND "
ElseIf Me.cboFilterCreateTrainingMaterial = 0 Then
strWhere = strWhere & "([Create Training Material] = False) AND "
End If
If Me.cboFilterPresenter = -1 Then
strWhere = strWhere & "([Presenter] = True) AND "
ElseIf Me.cboFilterPresenter = 0 Then
strWhere = strWhere & "([Presenter] = False) AND "
End If
If Me.cboFilterTechnicalWriting = -1 Then
strWhere = strWhere & "([Technical Writing] = True) AND "
ElseIf Me.cboFilterTechnicalWriting = 0 Then
strWhere = strWhere & "([Technical Writing] = False) AND "
End If
If Me.cboFilterUnderstandingofMaterial = -1 Then
strWhere = strWhere & "([Understanding of Material] = True) AND "
ElseIf Me.cboFilterUnderstandingofMaterial = 0 Then
strWhere = strWhere & "([Understanding of Material] = False) ANDR "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Here is the Clear Button:
Private Sub Command437_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control
'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acCheckBox
ctl.Value = False
End Select
Next
'Remove the form's .
Me.FilterOn = False
End Sub
I think its getting lost somewhere?
BUT there are NO errors coming up?
And I can't select 2 criteria?
OK, I have the original code working now EXCEPT I cannot select more than
one criteria AND after doing one search and I hit clear the results come up
blank.
Here is the Search Button:
Private Sub Command212_Click()
Dim strWhere As String
Dim lngLen As Long
If Me.cboFilterCommissioning = -1 Then
strWhere = strWhere & "([Commissioning] = True) AND "
ElseIf Me.cboFilterCommissioning = 0 Then
strWhere = strWhere & "([Commissioning] = False) AND "
End If
If Me.cboFilterSteamLineBlowing = -1 Then
strWhere = strWhere & "([Steam Line Blowing] = True) AND "
ElseIf Me.cboFilterSteamLineBlowing = 0 Then
strWhere = strWhere & "([Steam Line Blowing] = False) AND "
End If
If Me.cboFilterChemicalCleaning = -1 Then
strWhere = strWhere & "([Chemical Cleaning] = True) AND "
ElseIf Me.cboFilterChemicalCleaning = 0 Then
strWhere = strWhere & "([Chemical Cleaning] = False) AND "
End If
If Me.cboFilterCommissioningIandE = -1 Then
strWhere = strWhere & "([Commissioning I and E] = True) AND "
ElseIf Me.cboFilterCommissioningIandE = 0 Then
strWhere = strWhere & "([Commissioning I and E] = False) AND "
End If
If Me.cboFilterTurnoverCoordination = -1 Then
strWhere = strWhere & "([Turnover Coordination] = True) AND "
ElseIf Me.cboFilterTurnoverCoordination = 0 Then
strWhere = strWhere & "([Turnover Coordination] = False) AND "
End If
If Me.cboFilterCommissioningPlanning = -1 Then
strWhere = strWhere & "([Commissioning Planning] = True) AND "
ElseIf Me.cboFilterCommissioningPlanning = 0 Then
strWhere = strWhere & "([Commissioning Planning] = False) AND "
End If
If Me.cboFilterFacilityEvaluation = -1 Then
strWhere = strWhere & "([Facility Evaluation] = True) AND "
ElseIf Me.cboFilterFacilityEvaluation = 0 Then
strWhere = strWhere & "([Facility Evaluation] = False) AND "
End If
If Me.cboFilterOperationPreparedness = -1 Then
strWhere = strWhere & "([Operation Preparedness] = True) AND "
ElseIf Me.cboFilterOperationPreparedness = 0 Then
strWhere = strWhere & "([Operation Preparedness] = False) AND "
End If
If Me.cboFilterCorrosionProgram = -1 Then
strWhere = strWhere & "([Corrosion Program] = True) AND "
ElseIf Me.cboFilterCorrosionProgram = 0 Then
strWhere = strWhere & "([Corrosion Program] = False) AND "
End If
If Me.cboFilterFugitiveEmissionTesting = -1 Then
strWhere = strWhere & "([Fugitive Emission Testing] = True) AND "
ElseIf Me.cboFilterFugitiveEmissionTesting = 0 Then
strWhere = strWhere & "([Fugitive Emission Testing] = False) AND "
End If
If Me.cboFilterNDTCertification = -1 Then
strWhere = strWhere & "([NDT Certification] = True) AND "
ElseIf Me.cboFilterNDTCertification = 0 Then
strWhere = strWhere & "([NDT Certification] = False) AND "
End If
If Me.cboFilterMaintenance = -1 Then
strWhere = strWhere & "([Maintenance] = True) AND "
ElseIf Me.cboFilterMaintenance = 0 Then
strWhere = strWhere & "([Maintenance] = False) AND "
End If
If Me.cboFilterMaintenancePlanning = -1 Then
strWhere = strWhere & "([Maintenance Planning] = True) AND "
ElseIf Me.cboFilterMaintenancePlanning = 0 Then
strWhere = strWhere & "([Maintenance Planning] = False) AND "
End If
If Me.cboFilterPiping = -1 Then
strWhere = strWhere & "([Piping] = True) AND "
ElseIf Me.cboFilterPiping = 0 Then
strWhere = strWhere & "([Piping] = False) AND "
End If
If Me.cboFilterRotating = -1 Then
strWhere = strWhere & "([Rotating] = True) AND "
ElseIf Me.cboFilterRotating = 0 Then
strWhere = strWhere & "([Rotating] = False) AND "
End If
If Me.cboFilterMaintenanceIandE = -1 Then
strWhere = strWhere & "([Maintenance I and E] = True) AND "
ElseIf Me.cboFilterMaintenanceIandE = 0 Then
strWhere = strWhere & "([Maintenance I and E] = False) AND "
End If
If Me.cboFilterOperations = -1 Then
strWhere = strWhere & "([Operations] = True) AND "
ElseIf Me.cboFilterOperations = 0 Then
strWhere = strWhere & "([Operations] = False) AND "
End If
If Me.cboFilterOperationsPlanning = -1 Then
strWhere = strWhere & "([Operations Planning] = True) AND "
ElseIf Me.cboFilterOperationsPlanning = 0 Then
strWhere = strWhere & "([Operations Planning] = False) AND "
End If
If Me.cboFilterScheduling = -1 Then
strWhere = strWhere & "([Scheduling] = True) AND "
ElseIf Me.cboFilterScheduling = 0 Then
strWhere = strWhere & "([Scheduling] = False) AND "
End If
If Me.cboFilterInventoryofSupply = -1 Then
strWhere = strWhere & "([Inventory of Supply] = True) AND "
ElseIf Me.cboFilterInventoryofSupply = 0 Then
strWhere = strWhere & "([Inventory of Supply] = False) AND "
End If
If Me.cboFilterProductMovement = -1 Then
strWhere = strWhere & "([Product Movement] = True) AND "
ElseIf Me.cboFilterProductMovement = 0 Then
strWhere = strWhere & "([Product Movement] = False) AND "
End If
If Me.cboFilterStartUp = -1 Then
strWhere = strWhere & "([StartUp] = True) AND "
ElseIf Me.cboFilterStartUp = 0 Then
strWhere = strWhere & "([StartUp] = False) AND "
End If
If Me.cboFilterTemporary = -1 Then
strWhere = strWhere & "([Temporary] = True) AND "
ElseIf Me.cboFilterTemporary = 0 Then
strWhere = strWhere & "([Temporary] = False) AND "
End If
If Me.cboFilterLongTerm = -1 Then
strWhere = strWhere & "([LongTerm] = True) AND "
ElseIf Me.cboFilterLongTerm = 0 Then
strWhere = strWhere & "([LongTerm] = False) AND "
End If
If Me.cboFilterPandIDReviews = -1 Then
strWhere = strWhere & "([PandID Reviews] = True) AND "
ElseIf Me.cboFilterPandIDReviews = 0 Then
strWhere = strWhere & "([PandID Reviews] = False) AND "
End If
If Me.cboFilterPreliminary = -1 Then
strWhere = strWhere & "([Preliminary] = True) AND "
ElseIf Me.cboFilterPreliminary = 0 Then
strWhere = strWhere & "([Preliminary] = False) AND "
End If
If Me.cboFilterAsBuilt = -1 Then
strWhere = strWhere & "([As Built] = True) AND "
ElseIf Me.cboFilterAsBuilt = 0 Then
strWhere = strWhere & "([As Built] = False) AND "
End If
If Me.cboFilterHAZOPParticipation = -1 Then
strWhere = strWhere & "([HAZOP Participation] = True) AND "
ElseIf Me.cboFilterHAZOPParticipation = 0 Then
strWhere = strWhere & "([HAZOP Participation] = False) AND "
End If
If Me.cboFilterTraining = -1 Then
strWhere = strWhere & "([Training] = True) AND "
ElseIf Me.cboFilterTraining = 0 Then
strWhere = strWhere & "([Training] = False) AND "
End If
If Me.cboFilterCreateTrainingMaterial = -1 Then
strWhere = strWhere & "([Create Training Material] = True) AND "
ElseIf Me.cboFilterCreateTrainingMaterial = 0 Then
strWhere = strWhere & "([Create Training Material] = False) AND "
End If
If Me.cboFilterPresenter = -1 Then
strWhere = strWhere & "([Presenter] = True) AND "
ElseIf Me.cboFilterPresenter = 0 Then
strWhere = strWhere & "([Presenter] = False) AND "
End If
If Me.cboFilterTechnicalWriting = -1 Then
strWhere = strWhere & "([Technical Writing] = True) AND "
ElseIf Me.cboFilterTechnicalWriting = 0 Then
strWhere = strWhere & "([Technical Writing] = False) AND "
End If
If Me.cboFilterUnderstandingofMaterial = -1 Then
strWhere = strWhere & "([Understanding of Material] = True) AND "
ElseIf Me.cboFilterUnderstandingofMaterial = 0 Then
strWhere = strWhere & "([Understanding of Material] = False) ANDR "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Here is the Clear Button:
Private Sub Command437_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control
'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acCheckBox
ctl.Value = False
End Select
Next
'Remove the form's .
Me.FilterOn = False
End Sub
I think its getting lost somewhere?
BUT there are NO errors coming up?
And I can't select 2 criteria?