search from text box in split form using vba
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
search from text box in split form using vba
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
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: search from text box in split form using vba
On which field should the form be filtered?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
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
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: search from text box in split form using vba
Create an After Update event procedure for the text box:
I have assumed that Student Code is a text field. The code will run when you tab or click out of 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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
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
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: search from text box in split form using vba
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?
Do you want the form to be filtered as you type, or only when 12 characters have been entered?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
That will be greater if we can make it filter as i type,
regards
regards
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: search from text box in split form using vba
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
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: search from text box in split form using vba
Please note that we now use the On Change event of the text box, instead of its After Update event.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
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
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: search from text box in split form using vba
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
Thanks a lot , very informative
Regards
Regards
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
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
your help please
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: search from text box in split form using vba
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.
You can type johndoe, for example, then insert a space between john and doe, to get john doe.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
Thanks for your reply.
Regards
Regards
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
Hello Hans,HansV wrote: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
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 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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: search from text box in split form using vba
Which line is highlighted when you click Debug?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1259
- Joined: 01 May 2016, 09:58
Re: search from text box in split form using vba
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
thanks a lot.
regards