filter a split form using a textbox on header

[s_samira_21]

Hi everyone


* I m working with access 2007
I have a split form that have some fields like "Organization_ID_", "Organization_Name_" and ...
also I have a command button that can add or remove some other fields (that exist in database) to the table of the split form
and an unbound text box on the header.
something that I want is, when the user types something in the text box, access should start to filter the records of the split form, by each letter.
In addition access should search for the word in hole split form not in a specific field.
at the end i want the access focus on the first field of the first record that is shown in split form.

Anyone can help me?
I Hope I could Say What I meant, exactly.

 

[VilaRestal]

Hi again,

First get it working for a specific field (and see how well it behaves) and then think about adapting it to search all fields.

What you would do is have code in the Change event of the textbox. It would look at the Text property of the textbox and apply a filter accordingly. Let's say we call the textbox txtFind. Then the code would look something like this:

Private Sub txtFind_Change()
    Dim sFind, sField, sFilter As String
    txtFind.SetFocus
    sFind = Nz(txtFind.Text, "")
    If Len(sFind) > 0 Then
        sField = "[Field Name]" 'Replace with a real field
        sFilter = sField & " Like '*" & sFind & "*'"
        Me.Filter = sFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    txtFind.SetFocus
    txtFind.SelStart = Len(sFind) + 1
    txtFind.SelLength = 0
End Sub

There may be performance issues with that (with the whole idea). Every key press in that textbox will cause Access to apply a new filter to the records. How long that will take will depend on the number of records and the lengths of the strings in each record. You may find it's unfriendly to use if it takes more than a tenth of a second to do that. If so then you'd be better off putting a 'Find' button next to the textbox and putting the code in its Click event, which would be the usual way of doing such a thing.

To search all fields will get a lot more complex. Off the top of my head you're going to need to analyse the recordset behind the form and get the names of all the fields and then construct sFind as a sequence like the simple case but with " Or " joining each field's sField & " Like '*" & sFind & "*'".

Like I say, get the one-field example working first.

 

[VilaRestal]

For it to be searching all fields the code would be this:

Private Sub txtFind_Change()
    Dim sFind, sField, sFilter As String
    txtFind.SetFocus
    sFind = Nz(txtFind.Text, "")
    If Len(sFind) > 0 Then
        Dim rsMe As Recordset
        Set rsMe = Me.Recordset
        Dim iFields, iField, iFieldsIncluded As Integer
        iFields = rsMe.Fields.Count
        iFieldsIncluded = 0
        sFilter = ""
        For iField = 0 To iFields - 1
            sField = rsMe.Fields(iField).Name
            If rsMe.Fields(iField).Type = dbText Then
                If iFieldsIncluded > 0 Then sFilter = sFilter & " Or "
                sFilter = sFilter & "[" & sField & "] Like '*" & sFind & "*'"
                iFieldsIncluded = iFieldsIncluded + 1
            End If
        Next iField
        Set rsMe = Nothing
        Me.Filter = sFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    txtFind.SetFocus
    txtFind.SelStart = Len(sFind) + 1
    txtFind.SelLength = 0
End Sub

 

[VilaRestal]

The only problem with that code (apart from possible performance issues) is that spaces get eliminated from the textbox: Access kindly removes them

I'll see if I can work out a way to stop it.

 

[VilaRestal]

To get round that problem requires a module wide variable and analysing the KeyPress event too eek. The whole code for the form needs to be (excluding any other code for other things):

Option Compare Database
Option Explicit

Private bFindLastKeySpace As Boolean
 
Private Sub Form_Load()
    bFindLastKeySpace = False
End Sub
 
Private Sub txtFind_KeyPress(KeyAscii As Integer)
    bFindLastKeySpace = (KeyAscii = vbKeySpace)
End Sub
 
Private Sub txtFind_Change()
    Dim sFind, sField, sFilter As String
    txtFind.SetFocus
    sFind = Nz(txtFind.Text, "")
    If Len(sFind) > 0 Then
        Dim rsMe As Recordset
        Set rsMe = Me.Recordset
        Dim iFields, iField, iFieldsIncluded As Integer
        iFields = rsMe.Fields.Count
        iFieldsIncluded = 0
        sFilter = ""
        For iField = 0 To iFields - 1
            sField = rsMe.Fields(iField).Name
            If rsMe.Fields(iField).Type = dbText Then
                If iFieldsIncluded > 0 Then sFilter = sFilter & " Or "
                sFilter = sFilter & "[" & sField & "] Like '*" & sFind & "*'"
                iFieldsIncluded = iFieldsIncluded + 1
            End If
        Next iField
        Set rsMe = Nothing
        Me.Filter = sFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    txtFind.SetFocus
    If bFindLastKeySpace Then
        txtFind.Value = txtFind.Value & " "
        txtFind.SelStart = Len(sFind) + 2
    Else
        txtFind.SelStart = Len(sFind) + 1
    End If
    txtFind.SelLength = 0
    bFindLastKeySpace = False
End Sub

 

[s_samira_21]

again thank youuuuuuuuuuuuuuuuuuuu

but I have three problems
1) Space Key Doesn't work in Txtfind
2) If I want to search for an ID, It Doesn't work. because all Parameters are String
3) I want access to focus on the first field of the first record that was filtered.

sorry I don't know anything about VBA, yet. But I'm Trying

 

[s_samira_21]

thank you for solving first problem!

 

[VilaRestal]

OK,

1) space was fixed in previous post.
2) To search all fields not just text fields makes it simpler:

Option Compare Database
Option Explicit
 
Private bFindLastKeySpace As Boolean
 
Private Sub Form_Load()
    bFindLastKeySpace = False
End Sub
 
Private Sub txtFind_KeyPress(KeyAscii As Integer)
    bFindLastKeySpace = (KeyAscii = vbKeySpace)
End Sub
 
Private Sub txtFind_Change()
    Dim sFind, sField, sFilter As String
    txtFind.SetFocus
    sFind = Nz(txtFind.Text, "")
    If Len(sFind) > 0 Then
        Dim rsMe As Recordset
        Set rsMe = Me.Recordset
        Dim iFields, iField As Integer
        iFields = rsMe.Fields.Count
        sFilter = ""
        For iField = 0 To iFields - 1
            sField = rsMe.Fields(iField).Name
            If iField > 0 Then sFilter = sFilter & " Or "
            sFilter = sFilter & "[" & sField & "] Like '*" & sFind & "*'"
        Next iField
        Set rsMe = Nothing
        Me.Filter = sFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    txtFind.SetFocus
    If bFindLastKeySpace Then
        txtFind.Value = txtFind.Value & " "
        txtFind.SelStart = Len(sFind) + 2
    Else
        txtFind.SelStart = Len(sFind) + 1
    End If
    txtFind.SelLength = 0
    bFindLastKeySpace = False
End Sub

3) That would require more code before txtFind.SetFocus (finding the first field in the first record that matched the criteria and selecting it). Is it worth the effort? In fact, the txtFind.SetFocus line will kill that anyway. The line is essential - the focus mustn't shift from the textbox while user is typing in it - and to highlight a field in a record would do that. To reset the focus back on the textbox will remove the focus on the field as the records are in the same form. (If the records were in a subform it could be made to work but again, it's a very minor cosmetic improvement for a lot of effort.)

 

[VilaRestal]

Performance could be improved a bit by not having to reanalyse the recordset every key press but do all that when the form loads:

Option Compare Database
Option Explicit
 
Private bFindLastKeySpace As Boolean
Private rsMe As Recordset
Private iFields As Integer
 
Private Sub Form_Load()
    bFindLastKeySpace = False
    Set rsMe = Me.Recordset
    iFields = rsMe.Fields.Count
End Sub
 
Private Sub txtFind_KeyPress(KeyAscii As Integer)
    bFindLastKeySpace = (KeyAscii = vbKeySpace)
End Sub
 
Private Sub txtFind_Change()
    Dim sFind, sField, sFilter As String
    txtFind.SetFocus
    sFind = Nz(txtFind.Text, "")
    If Len(sFind) > 0 Then
        Dim iField As Integer
        sFilter = ""
        For iField = 0 To iFields - 1
            sField = rsMe.Fields(iField).Name
            If iField > 0 Then sFilter = sFilter & " Or "
            sFilter = sFilter & "[" & sField & "] Like '*" & sFind & "*'"
        Next iField
        Me.Filter = sFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    txtFind.SetFocus
    If bFindLastKeySpace Then
        txtFind.Value = txtFind.Value & " "
        txtFind.SelStart = Len(sFind) + 2
    Else
        txtFind.SelStart = Len(sFind) + 1
    End If
    txtFind.SelLength = 0
    bFindLastKeySpace = False
End Sub

On second thoughts, the difference is probably miniscule. Those few lines probably only take a microsecond.

 

[s_samira_21]

thank you u u u u u
it is fantastic
about 3th issue I don't like to use mouse when using a application so I like access focus on the record that is filtered.
but if is not possible don't mention it.

 

[VilaRestal]

Like I say, it would have to be a subform which would have its own control that has the focus separate to the main form's, which can only have one control with the focus and that has to be the textbox).

Another issue to bear in mind is it is searching all fields in the form's record source. There might be some fields you want to exclude: fields that aren't actually displayed for example.

To allow for that, here is the new improved version:

Option Compare Database
Option Explicit
 
Private bFindLastKeySpace As Boolean
Private rsMe As Recordset
Private iFields As Integer
 
Private Sub Form_Load()
    bFindLastKeySpace = False
    Set rsMe = Me.Recordset
    iFields = rsMe.Fields.Count
End Sub
 
Private Sub txtFind_KeyPress(KeyAscii As Integer)
    bFindLastKeySpace = (KeyAscii = vbKeySpace)
End Sub
 
Private Sub txtFind_Change()
    Dim sFind, sField, sFilter As String
    txtFind.SetFocus
    sFind = Nz(txtFind.Text, "")
    If Len(sFind) > 0 Then
        Dim iField, iFieldsIncluded As Integer
        iFieldsIncluded = 0
        sFilter = ""
        For iField = 0 To iFields - 1
            Select Case rsMe.Fields(iField).Type
                Case dbBoolean 'Field types not to search in
                    'Don't include it
                Case Else
                    sField = rsMe.Fields(iField).Name
                    Select Case sField
                        Case "DemoMemoField", "DemoDateField" 'Field names not to search in
                            'Don't include it
                        Case Else
                            If iFieldsIncluded > 0 Then sFilter = sFilter & " Or "
                            sFilter = sFilter & "[" & sField & "] Like '*" & sFind & "*'"
                            iFieldsIncluded = iFieldsIncluded + 1
                    End Select
            End Select
        Next iField
        Me.Filter = sFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    txtFind.SetFocus
    If bFindLastKeySpace Then
        txtFind.Value = txtFind.Value & " "
        txtFind.SelStart = Len(sFind) + 2
    Else
        txtFind.SelStart = Len(sFind) + 1
    End If
    txtFind.SelLength = 0
    bFindLastKeySpace = False
End Sub

You'll notice places to add field types and names you don't want included in the search.

And you're very welcome. I'll probably use that code myself from time to time too so not completely altruistic of me

Feel free to click the Thanks button by the way

 

[s_samira_21]

very nice
I have another question here:
http://www.access-programmers.co.uk/forums/showthread.php?p=1074097#post1074097