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 gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Command Buttons Search Form

Status
Not open for further replies.
Elvis72

Elvis72

Technical User
Dec 6, 2007
211
US
I have the two below buttons...

The first one will search and bring up the results.

But when I use the clear button it clears the y/n fields and brings back all the records, which is what it is supposed to do. But when I select another y/n field and hit search all the results go 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?
 
Sort by date Sort by votes
Did you correct the mistake in the line...
strWhere = strWhere & "([Understanding of Material] = False) ANDR "

But when I select another y/n field and hit search all the results go blank.
Click to expand...
Have you checked what strwhere contains when this happens? set a break point, use the immediate window.

Did you clear the strwhere when you cleared the y/n fields?

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!
 
Upvote 0 Downvote
OK, when you start talking about break points I am totally lost....

When I hit the clear button all the records return and the y/n fields are not checked.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

PrimoSteve
  • Locked
  • Question
Cannot add Image to Command Button?
Replies
9
Views
678
PrimoSteve
PrimoSteve
Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
659
Kennelbloke
Kennelbloke
Xavierparkash
  • Locked
  • Question
MS Access Slow Search in Linked Database
Replies
0
Views
261
Xavierparkash
Xavierparkash
caykamanj1966
  • Locked
  • Question
Help With Search Form Results 2
Replies
15
Views
399
caykamanj1966
caykamanj1966
TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
531
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top