search from text box in split form using vba

[siamandm]

Hi All
i have a split form with a textbox called txtSearch , i want when i put 12 digits inside the text box it automatically filter the split form
how i do that using vba ?

regareds

[HansV]

On which field should the form be filtered?

[siamandm]

thanks for the quick respond
let's say we have 4 fields, First Name Last Name, Age, and student Code, and we want to filter this form based on Student Code

regards

[HansV]

Create an After Update event procedure for the text box:

Code: Select all

Private Sub txtSearch_AfterUpdate()
    If Len(Me.txtSearch) = 12 Then
        Me.Filter = "[Student Code] = '" & Me.txtSearch & "'"
        Me.FilterOn = True
    End If
End Sub

I have assumed that Student Code is a text field. The code will run when you tab or click out of the text box.

[siamandm]

thanks a lot, may i have more questions please
1- can we make it auto once 12 characters have been added to the text box ? for example in iPhone or windows 10 when you put the login pin correct you don't need to hit enter it auto detect the correct password and then open.
2- after clearing the text box partially or completely show all the data ( remove the filter ).
3- can we make the txtSearch dynamic for example we have 5 student code
123456789012
556699887744
458785213694
528754585555
123542585555

when i type 5 it auto filter and show only
556699887744
528754585555
and when i type 52 it only show
528754585555
when i deleted the 2 inside the text box
show me both
556699887744
528754585555

this will be very nice if we can do this please

Regards

[HansV]

Your requests "make it auto once 12 characters have been added" and "when i type 5 it auto filter and show only" are contradictory.
Do you want the form to be filtered as you type, or only when 12 characters have been entered?

[siamandm]

That will be greater if we can make it filter as i type,

regards

[HansV]

Like this:

Code: Select all

Private Sub txtSearch_Change()
    Me.Filter = "[Student Code] Like '" & Me.txtSearch.Text & "*'"
    Me.FilterOn = True
    Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
End Sub

[HansV]

Please note that we now use the On Change event of the text box, instead of its After Update event.

[siamandm]

thanks a lot as usual very helpful.

i have another question if you don't mind,
what does the last line is used for : Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
also there is these options as well : SelStart, SelLengh, SelText

regards

[HansV]

When you type a character in txtSearch, filtering the form causes the entire text in the text box to become selected. If you then type another character, you would overwrite the text that you already typed. The line

Me.txtSearch.SelStart = Len(Me.txtSearch.Text)

places the insertion point after the existing text, so that you can go on typing.

SelStart is the position of the start of the selection in the text box.
SelLength is the length of the selection. If this is 0, the selection is only the insertion point.
SelText is the text of the current selection within the text box.

[siamandm]

Thanks a lot , very informative

Regards

[siamandm]

i notice one thing, in the txtSearch you can not type space, for example, if i want to use the txtSearch for filtering full name i can not type full name after i type first name i can not type ( space ) ...
your help please

[HansV]

That is an unavoidable side effect of wanting to filter as you type. Access removes trailing spaces from text boxes.
You can type johndoe, for example, then insert a space between john and doe, to get john doe.

[siamandm]

Thanks for your reply.

Regards

[siamandm]

Like this:

Code: Select all

Private Sub txtSearch_Change()
    Me.Filter = "[Student Code] Like '" & Me.txtSearch.Text & "*'"
    Me.FilterOn = True
    Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
End Sub

Hello Hans,
i notice a strange thing,
this code is working fine until you close the form, but next time when you open it and try to search for something i get this issue

Capture.PNG

when i click a debug and go to the code without doing any changes and going back to the design view and run the form again it works again ... but when i close the form and re-open it i get same error msg!

any idea why is that, please?

regards

[HansV]

Which line is highlighted when you click Debug?

[siamandm]

i created another form and try the code again, its working fine, i believe the issue was caused by other codes

thanks a lot.
regards