Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search Form - RunTime Error 2176

Status
Not open for further replies.
Elvis72

Elvis72

Technical User
Dec 6, 2007
211
0
0
US
I am getting this error:

The setting for this property is too long.

If I select the first criteria of Commissioning it works great...but if I select something else I get that error:

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 & "([Start-Up] = True) AND "
ElseIf Me.cboFilterStartUp = 0 Then
strWhere = strWhere & "([Start-Up] = 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 & "([Long-Term] = True) AND "
ElseIf Me.cboFilterLongTerm = 0 Then
strWhere = strWhere & "([Long-Term] = 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

If Me.cboFilterAutoCAD = -1 Then
strWhere = strWhere & "([AutoCAD] = True) AND "
ElseIf Me.cboFilterAutoCAD = 0 Then
strWhere = strWhere & "([AutoCAD] = False) AND "
End If

If Me.cboFilterPrimavera = -1 Then
strWhere = strWhere & "([Primavera] = True) AND "
ElseIf Me.cboFilterPrimavera = 0 Then
strWhere = strWhere & "([Primavera] = False) AND "
End If

If Me.cboFilterMicroStation = -1 Then
strWhere = strWhere & "([MicroStation] = True) AND "
ElseIf Me.cboFilterMicroStation = 0 Then
strWhere = strWhere & "([MicroStation] = False) AND "
End If

If Me.cboFilterAromatics = -1 Then
strWhere = strWhere & "([Aromatics] = True) AND "
ElseIf Me.cboFilterAromatics = 0 Then
strWhere = strWhere & "([Aromatics] = False) AND "
End If

If Me.cboFilterBiodiesel = -1 Then
strWhere = strWhere & "([Biodiesel] = True) AND "
ElseIf Me.cboFilterBiodiesel = 0 Then
strWhere = strWhere & "([Biodiesel] = False) AND "
End If

If Me.cboFilterBioethanol = -1 Then
strWhere = strWhere & "([Bioethanol] = True) AND "
ElseIf Me.cboFilterBioethanol = 0 Then
strWhere = strWhere & "([Bioethanol] = False) AND "
End If

If Me.cboFilterBPA = -1 Then
strWhere = strWhere & "([BPA] = True) AND "
ElseIf Me.cboFilterBPA = 0 Then
strWhere = strWhere & "([BPA] = False) AND "
End If

If Me.cboFilterButene = -1 Then
strWhere = strWhere & "([Butene] = True) AND "
ElseIf Me.cboFilterButene = 0 Then
strWhere = strWhere & "([Butene] = False) AND "
End If

If Me.cboFilterCatalyst = -1 Then
strWhere = strWhere & "([Catalyst] = True) AND "
ElseIf Me.cboFilterCatalyst = 0 Then
strWhere = strWhere & "([Catalyst] = False) AND "
End If

If Me.cboFilterChlorAlkalai = -1 Then
strWhere = strWhere & "([Chlor-Alkalai] = True) AND "
ElseIf Me.cboFilterChlorAlkalai = 0 Then
strWhere = strWhere & "([Chlor-Alkalai] = False) AND "
End If

If Me.cboFilterCoal = -1 Then
strWhere = strWhere & "([Coal] = True) AND "
ElseIf Me.cboFilterCoal = 0 Then
strWhere = strWhere & "([Coal] = False) AND "
End If

If Me.cboFilterCumene = -1 Then
strWhere = strWhere & "([Cumene] = True) AND "
ElseIf Me.cboFilterCumene = 0 Then
strWhere = strWhere & "([Cumene] = False) AND "
End If

If Me.cboFilterEBSM = -1 Then
strWhere = strWhere & "([EB/SM] = True) AND "
ElseIf Me.cboFilterEBSM = 0 Then
strWhere = strWhere & "([EB/SM] = False) AND "
End If

If Me.cboFilterEthanol = -1 Then
strWhere = strWhere & "([Ethanol] = True) AND "
ElseIf Me.cboFilterEthanol = 0 Then
strWhere = strWhere & "([Ethanol] = False) AND "
End If

If Me.cboFilterEthylene = -1 Then
strWhere = strWhere & "([Ethylene] = True) AND "
ElseIf Me.cboFilterEthylene = 0 Then
strWhere = strWhere & "([Ethylene] = False) AND "
End If

If Me.cboFilterFCCDCC = -1 Then
strWhere = strWhere & "([FCC/DCC] = True) AND "
ElseIf Me.cboFilterFCCDCC = 0 Then
strWhere = strWhere & "([FCC/DCC] = False) AND "
End If

If Me.cboFilterFGD = -1 Then
strWhere = strWhere & "([FGD] = True) AND "
ElseIf Me.cboFilterFGD = 0 Then
strWhere = strWhere & "([FGD] = False) AND "
End If

If Me.cboFilterFossilPlant = -1 Then
strWhere = strWhere & "([Fossil Plant] = True) AND "
ElseIf Me.cboFilterFossilPlant = 0 Then
strWhere = strWhere & "([Fossil Plant] = False) AND "
End If

If Me.cboFilterFuelsDepots = -1 Then
strWhere = strWhere & "([Fuels Depots] = True) AND "
ElseIf Me.cboFilterFuelsDepots = 0 Then
strWhere = strWhere & "([Fuels Depots] = False) AND "
End If

If Me.cboFilterFurnaces = -1 Then
strWhere = strWhere & "([Furnaces] = True) AND "
ElseIf Me.cboFilterFurnaces = 0 Then
strWhere = strWhere & "([Furnaces] = False) AND "
End If

If Me.cboFilterGas = -1 Then
strWhere = strWhere & "([Gas] = True) AND "
ElseIf Me.cboFilterGas = 0 Then
strWhere = strWhere & "([Gas] = False) AND "
End If

If Me.cboFilterGasTreatment = -1 Then
strWhere = strWhere & "([Gas Treatment] = True) AND "
ElseIf Me.cboFilterGasTreatment = 0 Then
strWhere = strWhere & "([Gas Treatment] = False) AND "
End If

If Me.cboFilterGeothermal = -1 Then
strWhere = strWhere & "([Geothermal] = True) AND "
ElseIf Me.cboFilterGeothermal = 0 Then
strWhere = strWhere & "([Geothermal] = False) AND "
End If

If Me.cboFilterGrassroots = -1 Then
strWhere = strWhere & "([Grassroots] = True) AND "
ElseIf Me.cboFilterGrassroots = 0 Then
strWhere = strWhere & "([Grassroots] = False) AND "
End If

If Me.cboFilterHydroElectric = -1 Then
strWhere = strWhere & "([Hydro Electric] = True) AND "
ElseIf Me.cboFilterHydroElectric = 0 Then
strWhere = strWhere & "([Hydro Electric] = False) AND "
End If

If Me.cboFilterHydrocracker = -1 Then
strWhere = strWhere & "([Hydrocracker] = True) AND "
ElseIf Me.cboFilterHydrocracker = 0 Then
strWhere = strWhere & "([Hydrocracker] = False) AND "
End If

If Me.cboFilterHydrotreater = -1 Then
strWhere = strWhere & "([Hydrotreater] = True) AND "
ElseIf Me.cboFilterHydrotreater = 0 Then
strWhere = strWhere & "([Hydrotreater] = False) AND "
End If

If Me.cboFilterLNG = -1 Then
strWhere = strWhere & "([LNG] = True) AND "
ElseIf Me.cboFilterLNG = 0 Then
strWhere = strWhere & "([LNG] = False) AND "
End If

If Me.cboFilterLNGImportTerm = -1 Then
strWhere = strWhere & "([LNG Import Term] = True) AND "
ElseIf Me.cboFilterLNGImportTerm = 0 Then
strWhere = strWhere & "([LNG Import Term] = False) AND "
End If

If Me.cboFilterNapthaCracker = -1 Then
strWhere = strWhere & "([Naptha Cracker] = True) AND "
ElseIf Me.cboFilterNapthaCracker = 0 Then
strWhere = strWhere & "([Naptha Cracker] = False) AND "
End If

If Me.cboFilterOilandGas = -1 Then
strWhere = strWhere & "([Oil and Gas] = True) AND "
ElseIf Me.cboFilterOilandGas = 0 Then
strWhere = strWhere & "([Oil and Gas] = False) AND "
End If

If Me.cboFilterOlefins = -1 Then
strWhere = strWhere & "([Olefins] = True) AND "
ElseIf Me.cboFilterOlefins = 0 Then
strWhere = strWhere & "([Olefins] = False) AND "
End If

If Me.cboFilterPhenolPlant = -1 Then
strWhere = strWhere & "([Phenol Plant] = True) AND "
ElseIf Me.cboFilterPhenolPlant = 0 Then
strWhere = strWhere & "([Phenol Plant] = False) AND "
End If

If Me.cboFilterPolymers = -1 Then
strWhere = strWhere & "([Polymers] = True) AND "
ElseIf Me.cboFilterPolymers = 0 Then
strWhere = strWhere & "([Polymers] = False) AND "
End If

If Me.cboFilterPolypropylene = -1 Then
strWhere = strWhere & "([Polypropylene] = True) AND "
ElseIf Me.cboFilterPolypropylene = 0 Then
strWhere = strWhere & "([Polypropylene] = False) AND "
End If

If Me.cboFilterPolysilicon = -1 Then
strWhere = strWhere & "([Polysilicon] = True) AND "
ElseIf Me.cboFilterPolysilicon = 0 Then
strWhere = strWhere & "([Polysilicon] = False) AND "
End If

If Me.cboFilterPolystyrene = -1 Then
strWhere = strWhere & "([Polystyrene] = True) AND "
ElseIf Me.cboFilterPolystyrene = 0 Then
strWhere = strWhere & "([Polystyrene] = False) AND "
End If

If Me.cboFilterProcessPlant = -1 Then
strWhere = strWhere & "([Process Plant] = True) AND "
ElseIf Me.cboFilterProcessPlant = 0 Then
strWhere = strWhere & "([Process Plant] = False) AND "
End If

If Me.cboFilterRefinery = -1 Then
strWhere = strWhere & "([Refinery] = True) AND "
ElseIf Me.cboFilterRefinery = 0 Then
strWhere = strWhere & "([Refinery] = False) AND "
End If

If Me.cboFilterRegassification = -1 Then
strWhere = strWhere & "([Regassification] = True) AND "
ElseIf Me.cboFilterRegassification = 0 Then
strWhere = strWhere & "([Regassification] = False) AND "
End If

If Me.cboFilterRevamp = -1 Then
strWhere = strWhere & "([Revamp] = True) AND "
ElseIf Me.cboFilterRevamp = 0 Then
strWhere = strWhere & "([Revamp] = False) AND "
End If

If Me.cboFilterRFCC = -1 Then
strWhere = strWhere & "([RFCC] = True) AND "
ElseIf Me.cboFilterRFCC = 0 Then
strWhere = strWhere & "([RFCC] = False) AND "
End If

If Me.cboFilterRippleTrays = -1 Then
strWhere = strWhere & "([Ripple Trays] = True) AND "
ElseIf Me.cboFilterRippleTrays = 0 Then
strWhere = strWhere & "([Ripple Trays] = False) AND "
End If

If Me.cboFilterServices = -1 Then
strWhere = strWhere & "([Services] = True) AND "
ElseIf Me.cboFilterServices = 0 Then
strWhere = strWhere & "([Services] = False) AND "
End If

If Me.cboFilterVCMUnit = -1 Then
strWhere = strWhere & "([VCM Unit] = True) AND "
ElseIf Me.cboFilterVCMUnit = 0 Then
strWhere = strWhere & "([VCM Unit] = False) AND "
End If

If Me.cboFilterWasteWater = -1 Then
strWhere = strWhere & "([Waste Water] = True) AND "
ElseIf Me.cboFilterWasteWater = 0 Then
strWhere = strWhere & "([Waste Water] = False) AND "
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

It doesn't seem to like the lngLen which makes it not like the Me.Filter either...

I have no idea how to fix it?
 
Sort by date Sort by votes
In order to shorten the Filter property, you may try to replace this:
If Me.cboFilterCommissioning = -1 Then
strWhere = strWhere & "([Commissioning] = True) AND "
ElseIf Me.cboFilterCommissioning = 0 Then
strWhere = strWhere & "([Commissioning] = False) AND "
End If

with this:
strWhere = strWhere & "([Commissioning] = " & Me!cboFilterCommissioning & ") AND "

for all your True/False criterias.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I think the error is being caused by exceeding the allowed string length for the Form's filter property.

You could try verifying the integrity of strWhere by outputting it to a MsgBox before setting the filter property and if this seems OK then try creating the Filter as a string to mock up a value for strWhere ie:

Code: 
testStr = "([Commissioning] = True) AND ([Steam Line Blowing] = True) AND ([Chemical Cleaning] = True) AND ...etc
Me.Filter = testStr

My guess is you will get the same error....if so let me know and I will give it some more thought.
 
Upvote 0 Downvote
PHV -

I updated the string per your suggestion and I am still getting the too long error and it is still highlighting:

Me.Filter = strWhere
Me.FilterOn = True

MrMerlotti -

I tried yours and cannot fit everything into one string so I shortened it just for the sake of trying it and I get a Run Time Error 2448, you can't assign value to this object and it highlights:

Me.Filter = testStr



 
Upvote 0 Downvote
Here is the code, I have split the search criteria because it was just too long and I have no idea how to work around that:


Private Sub Command212_Click()
Dim strWhere As String
Dim lngLen As Long

strWhere = strWhere & "([Commissioning] = " & Me!cboFilterCommissioning & ") AND "
strWhere = strWhere & "([ Steam Line Blowing] = " & Me!cboFilterSteamLineBlowing & ") AND "
strWhere = strWhere & "([Chemical Cleaning] = " & Me!cboFilterChemicalCleaning & ") AND "
strWhere = strWhere & "([Commissioning I and E] = " & Me!cboFilterCommissioningIandE & ") AND "
strWhere = strWhere & "([Turnover Coordination] = " & Me!cboFilterTurnoverCoordination & ") AND "
strWhere = strWhere & "([Commissioning Planning] = " & Me!cboFilterCommissioningPlanning & ") AND "
strWhere = strWhere & "([Facility Evaluation] = " & Me!cboFilterFacilityEvaluation & ") AND "
strWhere = strWhere & "([Operation Preparedness] = " & Me!cboFilterOperationPreparedness & ") AND "
strWhere = strWhere & "([Corrosion Program] = " & Me!cboFilterCorrosionProgram & ") AND "
strWhere = strWhere & "([Fugitive Emission Testing] = " & Me!cboFilterFugitiveEmissionTesting & ") AND "
strWhere = strWhere & "([NDT Certification] = " & Me!cboFilterNDTCertification & ") AND "
strWhere = strWhere & "([Maintenance] = " & Me!cboFilterMaintenance & ") AND "
strWhere = strWhere & "([Maintenance Planning] = " & Me!cboFilterMaintenancePlanning & ") AND "
strWhere = strWhere & "([Piping] = " & Me!cboFilterPiping & ") AND "
strWhere = strWhere & "([Rotating] = " & Me!cboFilterRotating & ") AND "
strWhere = strWhere & "([Maintenance I and E] = " & Me!cboFilterMaintenanceIandE & ") AND "
strWhere = strWhere & "([Operations] = " & Me!cboFilterOperations & ") AND "
strWhere = strWhere & "([Operations Planning] = " & Me!cboFilterOperationsPlanning & ") AND "
strWhere = strWhere & "([Scheduling] = " & Me!cboFilterScheduling & ") AND "
strWhere = strWhere & "([Inventory of Supply] = " & Me!cboFilterInventoryofSupply & ") AND "
strWhere = strWhere & "([Product Movement] = " & Me!cboFilterProductMovement & ") AND "
strWhere = strWhere & "([Start-Up] = " & Me!cboFilterStartUp & ") AND "
strWhere = strWhere & "([Temporary] = " & Me!cboFilterTemporary & ") AND "
strWhere = strWhere & "([Long-Term] = " & Me!cboFilterLongTerm & ") AND "
strWhere = strWhere & "([PandID Reviews] = " & Me!cboFilterPandIDReviews & ") AND "
strWhere = strWhere & "([Preliminary] = " & Me!cboFilterPreliminary & ") AND "
strWhere = strWhere & "([As Built] = " & Me!cboFilterAsBuilt & ") AND "
strWhere = strWhere & "([HAZOP Participation] = " & Me!cboFilterHAZOPParticipation & ") AND "
strWhere = strWhere & "([Training] = " & Me!cboFilterTraining & ") AND "
strWhere = strWhere & "([Create Training Material] = " & Me!cboFilterCreateTrainingMaterial & ") AND "
strWhere = strWhere & "([Presenter] = " & Me!cboFilterPresenter & ") AND "
strWhere = strWhere & "([Technical Writing] = " & Me!cboFilterTechnicalWriting & ") AND "
strWhere = strWhere & "([Understanding of Material] = " & Me!cboFilterUnderstandingofMaterial & ") AND "

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

But it is still coming up with errors on the Me.Filters.
 
Upvote 0 Downvote
You could try removing your filter criteria to a seperate form and generating a SELECT statement based on your filter requirements as a string. You then open the results form with the Record Source set to the SELECT statement.

I'm not sure what your underlying table looks like but for a simple example take a table structure, for an example table tblExample (I've included underscores in the field names to make it easier to create the select statement):

RecID (Autonumber)
Desc (Text)
Facility_Evaluation (Yes/No)
Operation_Prepardness (Yes/No)

You could build your select statement as follows:

Code: 
'Command button on search form
 
dim strSelect as String, strWhere as String
strSelect = "SELECT * from tblExample WHERE RecID <> 0 "			' will select all records
strWhere = ""							'empty string

If Me.cboFilterFacilityEvaluation = -1 Then
        strWhere = strWhere  & " AND Facility_Evaluation = True"
    ElseIf Me.cboFilterFacilityEvaluation = 0 Then
        strWhere = strWhere & " AND Facility_Evaluation = False"
    End If
    
    If Me.cboFilterOperationPreparedness = -1 Then
        strWhere = strWhere & " AND Operation Preparedness = True"
    ElseIf Me.cboFilterOperationPreparedness = 0 Then
        strWhere = strWhere & " AND Operation_Preparedness = False"
    End If

strSELECT = strSELECT & strWHERE

DoCmd.OpenForm "frmResults", , , , , , [strSELECT]			'open the Results form
Forms![frmFindResults].Form.RecordSource = strSELECT			'set RecordSource

The form frmResults is built in the normal way - the RecordSource is then deleted in the form properties.

Let me know if you think this approach would work as I've not really had time to test the code or if anybody else has a more elegant solution.
 
Upvote 0 Downvote
Why not just build a query and place the control name in the field's criteria?
 
Upvote 0 Downvote
Ok, I'm only 5'2"...and feeling a little buried at the moment...grin

MrMerlotti - your response is greatly appreciated but I believe way over my head. My experience with recordsource is extremely non-existent.

MajP - I'm not exactly following you on placing the control name in the fields criteria in the query, can you please elaborate.

 
Upvote 0 Downvote
You can do this in the query builder, but here is demo the SQL

Code: 
SELECT tblTest.Commisioning, tblTest.[Steam Line Blowing], tblTest.[Chemical Cleaning], tblTest.ID
FROM tblTest
WHERE (((tblTest.Commisioning)=[forms]![frmSearch]![ctrlCommisioning]) AND ((tblTest.[Steam Line Blowing])=[forms]![frmSearch]![ctrlSteamLineBlowing]) AND ((tblTest.[Chemical Cleaning])=[forms]![frmSearch]![ctrlChemicalCleaning]))
;

Be sure the controls do not pass null values back but trues and falses.
 
Upvote 0 Downvote
cANT HELP YOU THERE, but what I am trying to do in the following is prompt a user to enter an invoice number. If the invoice number is in the table, then bring up the Invoice Report. If the invoice number is not in the table, then display a messagebox stating so, and when the user clicks ok to that message box, to take them back to the main form. When I enter an invoice number that is not in the database , the messagebox comes up, I click ok, but the blank invoice remains on the screen, which is not what I want it to do.. Attached is my code:
'------------------------------------------------------------
' CMDpreviewInvoiceReport_Click
'
'------------------------------------------------------------
Private Sub CMDpreviewInvoiceReport_Click()
On Error GoTo CMDpreviewInvoiceReport_Click_Err

'note, below GRPIO is the name of the Form containing the option buttons.

If (GRPIO = 1) Then
' Select by Invoice #
DoCmd.OpenReport "INVOICE REPORT", acViewPreview, "", "[INVOICE QUERY].[Invoice Number]=[What Invoice Number?]", acNormal
On Error GoTo CMDpreviewInvoiceReport_Numb_Err
End If
DoCmd.Maximize
If (GRPIO = 2) Then
' Select by Company
DoCmd.OpenReport "INVOICE REPORT", acViewPreview, "", "[INVOICE QUERY].[Company]=[What Company?]", acNormal
End If
DoCmd.Maximize
If (GRPIO = 3) Then
' Select ALL Invoices
DoCmd.OpenReport "INVOICE REPORT", acViewPreview, "", "", acNormal
End If
DoCmd.Maximize

CMDpreviewInvoiceReport_Numb_Err:
MsgBox "That Invoice number does not exist"
Resume CMDpreviewInvoiceReport_Click_Exit



CMDpreviewInvoiceReport_Click_Exit:
Exit Sub

CMDpreviewInvoiceReport_Click_Err:
MsgBox Error$
Resume CMDpreviewInvoiceReport_Click_Exit

End Sub
 
Upvote 0 Downvote
Elvis,
I think I miss interpreted. The user can select true, false, or not select anything for a field control (a null control value). In that case you want to return selected true values and selected false values and all values for the controls not selected. That is something like this:

SELECT tblTest.Commisioning
FROM tblTest
WHERE (((tblTest.Commisioning)=[forms]![frmSearch]![ctrlCommisioning])) OR ((([forms]![frmSearch]![ctrlCommisioning]) Is Null))
;

see


Doing this in the query builder for 5 or 10 controls would be the easiest solution. Technically it is easy but really tedious, you have a whole lot of controls to write and ensure all names are correct. I would not want to try to do this except using code to loop and build the sql string. Let me think of a loop and get back.
 
Upvote 0 Downvote
This is how I would build the where statement.
1)In each control on the search form in the tag property put the name of the Field
example:cboFilterPresenter
Tag: Presenter
2)Ensure no other fields not used in the search have any Tag property values
3)Use this code to build your string where

Code: 
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
    If Not (ctrl.Tag = "") Then
        If Not (Trim(ctrl.Value & " ") = "") Then
         getWhere = getWhere & " [" & ctrl.Tag & "] = " & ctrl.Value & " AND"
      End If
    End If
  Next ctrl
  getWhere = Left(getWhere, Len(getWhere) - 4)
End Function

This is the easiest way to manage building a where string of such length. When I call this function in my little test example and have three controls selected I get
Code: 
[Commisioning] = -1 AND [Steam Line Blowing] = 0 AND [Chemical Cleaning] = -1

now you have a function that returns the correct where statement. You can use this in lots of places as long as you do not bump up against the string limits.

1) Docmd.openform with a criteria
2) Add to the rest of the sql string and use Docmd.opensql
3) Create a qrydef and add to the query defs
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
213
Andrzejek
Andrzejek
JimStrand
  • Locked
  • Question
Trying to Use Form Control as Variable in Report Control Source 2
Replies
5
Views
135
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Stop user changing size of Access Application (not form)
Replies
1
Views
153
Andrzejek
Andrzejek
JimStrand
  • Locked
  • Question
Using Report Header (Form control) as Variable in Report Expression 1
Replies
11
Views
270
dhookom
dhookom
Moss100
  • Locked
  • Question
Find error in currency feild 2
Replies
8
Views
197
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top