Building a powerful filtering strategy...

  • Thread starter Thread starter Atlas
  • Start date Start date
A

Atlas

I'm developing a readonly continuos form with
- AC2003
- adp
- ADO
- MS SQL server 2000

The continuos form will be populated by a maximum of 2000 records.

As at runtime the context menu/filter is disabled, I thought I could set
above the columns headings some unbound textboxes where users can type some
text and let access (VB) filter data, as they type.

Could be that on keydown of each textbox some code would do the job of
filtering. Some of you would state that filtering on the server side is
better, and that is true with massive data requests; as the recordset will
hold a maximum of about 2000 records it will be probably faster to retrieve
the recorset when the form is opened and then play with filters on the form.
Requerying the server continuosly is probably slower than continuosly
applying filters on a small recordset.

Could be that on keydown of each textbox a timer is fired just to delay
filter appliance and when timer is expired, lte VB do the job.

Now I'm working this way but allready facing the first problems, just trying
to use filter property.
What I would like to achieve is to filter the recorset on a table text field
"Description" with the LIKE operator.

Heres the code:

Private Sub FilterTextBox_KeyDown(KeyCode As Integer, Shift As Integer)

Dim FilterString as String

If KeyCode = vbKeyBack Then
If Len(Me.FilterTextBox) > 0 Then
Me.FilterTextBox = Left(Me.FilterTextBox, Len(Me.FilterTextBox) -
1)
End If
Else
Me.FilterTextBox = Me.FilterTextBox & Chr(KeyCode)
End If

FilterString = "Description LIKE '" & Me.FilterTextBox & "*'"

If Len(Me.FilterTextBox) > 0 Then
Me.FilterOn = True
Me.Recordset.Filter = FilterString
Else
Me.FilterOn = False
Me.Filter = Empty
End If

End sub


When the filter above is applied, the form becomes empty like if no record
match is found.

Any hint?
 
Last notes:

mofied the code with "me.form." prefix.

Made a few tests

1) The "=" operator makes the filter work, assuming the exact content is set
2) The "=" operator fails if the "*" is append to the filter string.
3) the "LIKE" operator fails allways, even if the exact string is
given.......

Uhu????
 
Damned thing! I've got it working coding "%" instead of "*".

Who writes documentation and help at MS???
 
Back
Top