Search through the same recordset several times

[MrStar]

I have a form with a subform.
The main form include only one input field, and the subform all the datafields.
The data in the subform is based on a query with a 'like' statement. The content of the 'like' is the word entered on the main form.

My problem is that I want to make another 'like' search on the result from the previous 'like'.
I shall be able to do the 'like' again and again until the result is 'no more records'

I guess it is something with recordsetClone, but I need some good input on this problem.

 

[jksmi]

hi, if you're setting the filter property of the form you could try try something like...
if len(me.Filter)>0 then
me.filter = me. filter & " AND " strFilter
else
me.filter = strFilter
end if
me.filteron = true


HTH, Jamie
FAQ219-2884

 

[MrStar]

Thanks for your answer jksmi, but I'm not sure this is what I'm lkooking for.

When I type something in the field on my mainform, it fills the subform with data based on a query that uses "databasefield like [Forms]![MainForm]!InputField.
This gives med mayby 100 records.

Then I want to type in something new in my mainform, but then the query shall run only on the 100 previous found records.

 

[jksmi]

Hi,

if you use the form filter property then you won't need to include the like statement in your underlying query. Remove the like condition from your query and use code along the lines of...

Private Sub cmdFilter_Click()
Dim strFilter As String

strFilter = "[YOUR_FIELD] like '*" & txtFilter & "*'"

If Len(Me.Filter) > 0 Then
    Me.Filter = Me.Filter & " AND " & strFilter
Else
    Me.Filter = strFilter
End If

Me.FilterOn = True

End Sub

You also need a button to clear the filter (you'd need that anyway}for that just add me.Filter = "" to a new button.

When you open the form there will be no filter so the 'if' exists so that there is no AND included at the beginning of the filter condition (otherwise the filter would be 0 AND [YOUR_FIELD] like '*abc*'). This will filter your query from, say, 1,000 rows to 100. From now on, changing, or adding to, the text box and clicking the button will add a new like condition in your where clause - because we have used AND it will effectively apply the second [nth] filter to the 100 rows returned and return, say 50 of the 100. Using recordsetclone you would be doing exactly the same thing only more complicated to code.

i.e. assuming were filtering on a description field - if we first say return records with "T-shirt" it will return all records with "T-Shirt" in the description. If we then change that to "green" it will return all records with "T-shirt" and "green" in the description. Adding a further qualification of "large" will return all records with "T-shirt" and "green" and "large", and so on. In this example each qualification is added seperately and it doesnt matter in what order they appear in the description, if the text box is not cleared, i.e. "T-shirt green" the exact phrase "T-shirt green" would have to appear in the description. (if you hadn't guessed I'm wearing a green T-shirt)

HTH, Jamie
FAQ219-2884