USER DECLARED STRING SEARCH CODE ASSITANCE.

[Craftsman61]

What I have is a button click event where from a form the user inputs a string (text or integer)in a text box or select an item from a combo box, after entering or selecting data in one or several of the "boxes" then fires the code below to display the results.

My issue is that only the "box" the farthest down the list becomes the main search criteria. In other words if several text boxes and one combobox was used the "box" farthest down the code line becomes the main, and only search criteria used. What i need is for all entry boxes to used if data is entered or selected in them for the filter.

My second issue is on the clear filter code it "resets or clears" any data entered or selected in the "boxes" but does not reset what the user is seeing. What I mean by reset is that until the filter is used all entries are listed on the screen for the user to view.

Any advice or direction to go in would be appreciated as I am not very good with vb and it took me a week to comeup with what I've got so far. Thanks in advance !!!!




Public Sub cmdFIND_Click()

' this filters the DB to show user data for filtering purposes.

Dim filstr As String
filstr = ""
If Me.txtFIND_summary <> &quot;&quot; Then
filstr = &quot;SUMMARY LIKE '*&quot; & txtFIND_summary & &quot;*'&quot;
End If
If Me.txtdescfilter <> &quot;&quot; Then
If filstr <> &quot;&quot; Then filstr = filstr & &quot; or &quot;
filstr = filstr & &quot;change LIKE '*&quot; & txtdescfilter & &quot;*'&quot;
End If
If Me.txtreasonfilter <> &quot;&quot; Then
If filstr <> &quot;&quot; Then filstr = filstr & &quot; or &quot;
filstr = filstr & &quot;reason LIKE '*&quot; & txtreasonfilter & &quot;*'&quot;
End If
If Me.cboKW1_FILTER <> &quot;&quot; Then
If filstr <> &quot;&quot; Then filstr = filstr & &quot; or &quot;
filstr = filstr & &quot;KW1 LIKE '*&quot; & cboKW1_FILTER & &quot;*'&quot;
End If
If Me.cboKW2_FILTER <> &quot;&quot; Then
If filstr <> &quot;&quot; Then filstr = filstr & &quot; or &quot;
filstr = filstr & &quot;KW2 LIKE '*&quot; & cboKW2_FILTER & &quot;*'&quot;
End If
If Me.cboKW3_FILTER <> &quot;&quot; Then
If filstr <> &quot;&quot; Then filstr = filstr & &quot; or &quot;
filstr = filstr & &quot;KW3 LIKE '*&quot; & cboKW3_FILTER & &quot;*'&quot;
End If


Me.Filter = filstr

Me.FilterOn = True
End Sub

Private Sub Clear_Click()

' THIS CLEARS THE SEARCH CRITERIA FROM THE BOXES

Me.cboKW1_FILTER = &quot;&quot;
Me.cboKW2_FILTER = &quot;&quot;
Me.cboKW3_FILTER = &quot;&quot;
Me.txtdescfilter = &quot;&quot;
Me.txtFIND_summary = &quot;&quot;
Me.txtreasonfilter = &quot;&quot;
End Sub

 

[WayneRyan]

CraftsMan61,

I think that this is what you are trying to do.


'*********************************
Dim strBoxes As String ' Note n is # of controls
Dim strFilter As String
Dim intCtr As Integer

For intCtr = 1 to n
strBoxes(intCtr) = &quot;&quot;
next intCtr

strBoxes(1) = &quot;Field1 = '&quot; & txtBox1 & &quot;'&quot;
strBoxes(2) = &quot;Field2 = '&quot; & txtBox2 & &quot;'&quot;
strBoxes(3) = &quot;Field3 = '&quot; & txtBox3 & &quot;'&quot;
.
.
.
strBoxes = Fieldn = '&quot; & txtBoxn & &quot;'&quot;

For intCtr = n to 1 Step -1
If strBoxes(intCtr) <> &quot;&quot; Then
strFilter = strBoxes(intCtr)
Me.Requery ' Refresh screen
'
' Clear the boxes if needed
'
Exit Sub
End If
Next intCtr
'*********************************

Haven't tested it.
Only typed it in NotePad.

hth,
Wayne

 

[SBendBuckeye]

A couple ideas that will hopefully help make your life simpler. First, underneath the covers, Access treats a zero as false and a non-zero value as true.

So, you can test Len(string) as if it were a boolean (eg
If Len(String2Test) Then, etc. Also, vbNullString is an enumerated constant which Access supplies. It is not huge here, but using them where you can is always good practice.

With that in mind, try redoing what you have done below as follows:

'Filters the DB to show user data for filtering purposes.
Public Sub cmdFIND_Click()
Dim filstr As String
filstr = vbNullString

If Len(Me.txtFIND_summary) Then
filstr = &quot;SUMMARY LIKE '*&quot; & txtFIND_summary & &quot;*' Or &quot;
End If

If Len(Me.txtdescfilter) Then
filstr = filstr _
& &quot;change LIKE '*&quot; & txtdescfilter & &quot;*' Or &quot;
End If

If Len(Me.txtreasonfilter) Then
filstr = filstr _
& &quot;reason LIKE '*&quot; & txtreasonfilter & &quot;*' Or &quot;
End If

If Len(Me.cboKW1_FILTER) Then
filstr = filstr _
& &quot;KW1 LIKE '*&quot; & cboKW1_FILTER & &quot;*' Or &quot;
End If

If Len(Me.cboKW2_FILTER) Then
filstr = filstr _
& &quot;KW2 LIKE '*&quot; & cboKW2_FILTER & &quot;*' Or &quot;
End If

If Len(Me.cboKW3_FILTER) Then
filstr = filstr & &quot;KW3 LIKE '*&quot; & cboKW3_FILTER & &quot;*'&quot;
End If

'Now that we are at the end, we need to get rid of any
'trailing Or statements which are left over

If Right$(Trim$(filstr), 2) = &quot;Or&quot; Then
filstr = _
Left$(Trim$(filstr), Len(Trim$(filstr)) - 2)
End If

Me.Filter = filstr

Me.FilterOn = True
End Sub

Hope this helps! Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!