Filter records based on user input

[Scott]

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Klatuu]

What you are describing is normal behavior for a Combo Box.

 

[Dale Fye]

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

 

[Klatuu]

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Dale Fye]

Dave,

Think you left out the .text on your FindFirst operation.

I don't think I've ever actually done a FindFirst operation with a wildcard.
Do you still use the "=" or should it be:

..FindFirst "[Phone] like """ & me.txtFilter.Text & "*"""

----
Dale

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Klatuu]

You are correct about the =. That line should be:

.FindFirst "[Phone] Like """ & Me.txtFilter & "*"""

As to the Text property, it is not necessary. Neither is the Value
property, it is the default property returned if no other property is
specified. The Text property is really useless in VBA. It is a hold over
from VB. In VBA, it can only be used if the control has the focus.

I never use Text, and only use Value when I am working in a With/End With to
identify the value. A very simple example would be:

With Me.txtFooBar
If .Value = "blah" Then
.ForeColor = vbGreen
End If
End With
--
Dave Hargis, Microsoft Access MVP

Dave,

Think you left out the .text on your FindFirst operation.

I don't think I've ever actually done a FindFirst operation with a wildcard.
Do you still use the "=" or should it be:

.FindFirst "[Phone] like """ & me.txtFilter.Text & "*"""

----
Dale

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale



:

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Klatuu]

And as to WildCards, the FindFirst can use them.
--
Dave Hargis, Microsoft Access MVP

Dave,

Think you left out the .text on your FindFirst operation.

I don't think I've ever actually done a FindFirst operation with a wildcard.
Do you still use the "=" or should it be:

.FindFirst "[Phone] like """ & me.txtFilter.Text & "*"""

----
Dale

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale



:

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Dale Fye]

Dave,

I'll will have to disagree about your statement regarding .text and .value.

Value is the default, so I almost never use it. But in the Change event, if
you refer to the value of the control, it contains the original value (the
value from the most recent afterUpdate), so you have to use the .text
property to get what is currently in the textbox.

Dale

You are correct about the =. That line should be:

.FindFirst "[Phone] Like """ & Me.txtFilter & "*"""

As to the Text property, it is not necessary. Neither is the Value
property, it is the default property returned if no other property is
specified. The Text property is really useless in VBA. It is a hold over
from VB. In VBA, it can only be used if the control has the focus.

I never use Text, and only use Value when I am working in a With/End With to
identify the value. A very simple example would be:

With Me.txtFooBar
If .Value = "blah" Then
.ForeColor = vbGreen
End If
End With
--
Dave Hargis, Microsoft Access MVP

Dave,

Think you left out the .text on your FindFirst operation.

I don't think I've ever actually done a FindFirst operation with a wildcard.
Do you still use the "=" or should it be:

.FindFirst "[Phone] like """ & me.txtFilter.Text & "*"""

----
Dale

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP


:

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale



:

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Klatuu]

Apologies, Dale. You are correct. Since I have never done this, I made an
erroneous assumption. But, I did find something else interesting. That is
that if you are using a continuous form, every time you go to the next match,
the cursor is at the beginning of the current instance of the control on the
row and either the user has to move to the end of the text or it will start
over. The solution is to position the cursor at the end of the text.

Also, as a cosmetic effect, I added a line to the Got Focus next control in
the tab order to null the search box.

Private Sub txtClientPhone_Change()
If Len(Me.txtClientPhone.Text) > 0 Then
With Me.RecordsetClone
.FindFirst "[CLPHONEFU] Like """ & Me.txtClientPhone.Text & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.txtClientPhone.SelStart = Len(Me.txtClientPhone.Text) + 1
End If
End With
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Dave,

I'll will have to disagree about your statement regarding .text and .value.

Value is the default, so I almost never use it. But in the Change event, if
you refer to the value of the control, it contains the original value (the
value from the most recent afterUpdate), so you have to use the .text
property to get what is currently in the textbox.

Dale

You are correct about the =. That line should be:

.FindFirst "[Phone] Like """ & Me.txtFilter & "*"""

As to the Text property, it is not necessary. Neither is the Value
property, it is the default property returned if no other property is
specified. The Text property is really useless in VBA. It is a hold over
from VB. In VBA, it can only be used if the control has the focus.

I never use Text, and only use Value when I am working in a With/End With to
identify the value. A very simple example would be:

With Me.txtFooBar
If .Value = "blah" Then
.ForeColor = vbGreen
End If
End With
--
Dave Hargis, Microsoft Access MVP

Dave,

Think you left out the .text on your FindFirst operation.

I don't think I've ever actually done a FindFirst operation with a wildcard.
Do you still use the "=" or should it be:

.FindFirst "[Phone] like """ & me.txtFilter.Text & "*"""

----
Dale



:

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP


:

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale



:

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Dale Fye]

Dave,

I assume by the level of coding that you have done, that you have now tested
this with a form and data. Is your txtClientPhone textbox in the forms
header/footer?

I guess I'm surprised that the me.txtClientPhone.SelStart line works at all.
I thought that the bookmark line would set the focus to the control in the
details section with the lowest tab stop, thereby move the focus out of
txtClientPhone.

I think we have hijacked Scott's question. ;-)

----
Dale

Apologies, Dale. You are correct. Since I have never done this, I made an
erroneous assumption. But, I did find something else interesting. That is
that if you are using a continuous form, every time you go to the next match,
the cursor is at the beginning of the current instance of the control on the
row and either the user has to move to the end of the text or it will start
over. The solution is to position the cursor at the end of the text.

Also, as a cosmetic effect, I added a line to the Got Focus next control in
the tab order to null the search box.

Private Sub txtClientPhone_Change()
If Len(Me.txtClientPhone.Text) > 0 Then
With Me.RecordsetClone
.FindFirst "[CLPHONEFU] Like """ & Me.txtClientPhone.Text & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.txtClientPhone.SelStart = Len(Me.txtClientPhone.Text) + 1
End If
End With
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Dave,

I'll will have to disagree about your statement regarding .text and .value.

Value is the default, so I almost never use it. But in the Change event, if
you refer to the value of the control, it contains the original value (the
value from the most recent afterUpdate), so you have to use the .text
property to get what is currently in the textbox.

Dale

You are correct about the =. That line should be:

.FindFirst "[Phone] Like """ & Me.txtFilter & "*"""

As to the Text property, it is not necessary. Neither is the Value
property, it is the default property returned if no other property is
specified. The Text property is really useless in VBA. It is a hold over
from VB. In VBA, it can only be used if the control has the focus.

I never use Text, and only use Value when I am working in a With/End With to
identify the value. A very simple example would be:

With Me.txtFooBar
If .Value = "blah" Then
.ForeColor = vbGreen
End If
End With
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Think you left out the .text on your FindFirst operation.

I don't think I've ever actually done a FindFirst operation with a wildcard.
Do you still use the "=" or should it be:

.FindFirst "[Phone] like """ & me.txtFilter.Text & "*"""

----
Dale



:

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP


:

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale



:

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Klatuu]

No, I did not put it in the header, I just uses an unbound text box in the
detail. Not pretty, putting it in the header would proabably be a better
choice.
--
Dave Hargis, Microsoft Access MVP

Dave,

I assume by the level of coding that you have done, that you have now tested
this with a form and data. Is your txtClientPhone textbox in the forms
header/footer?

I guess I'm surprised that the me.txtClientPhone.SelStart line works at all.
I thought that the bookmark line would set the focus to the control in the
details section with the lowest tab stop, thereby move the focus out of
txtClientPhone.

I think we have hijacked Scott's question. ;-)

----
Dale

Apologies, Dale. You are correct. Since I have never done this, I made an
erroneous assumption. But, I did find something else interesting. That is
that if you are using a continuous form, every time you go to the next match,
the cursor is at the beginning of the current instance of the control on the
row and either the user has to move to the end of the text or it will start
over. The solution is to position the cursor at the end of the text.

Also, as a cosmetic effect, I added a line to the Got Focus next control in
the tab order to null the search box.

Private Sub txtClientPhone_Change()
If Len(Me.txtClientPhone.Text) > 0 Then
With Me.RecordsetClone
.FindFirst "[CLPHONEFU] Like """ & Me.txtClientPhone.Text & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.txtClientPhone.SelStart = Len(Me.txtClientPhone.Text) + 1
End If
End With
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Dave,

I'll will have to disagree about your statement regarding .text and .value.

Value is the default, so I almost never use it. But in the Change event, if
you refer to the value of the control, it contains the original value (the
value from the most recent afterUpdate), so you have to use the .text
property to get what is currently in the textbox.

Dale

:

You are correct about the =. That line should be:

.FindFirst "[Phone] Like """ & Me.txtFilter & "*"""

As to the Text property, it is not necessary. Neither is the Value
property, it is the default property returned if no other property is
specified. The Text property is really useless in VBA. It is a hold over
from VB. In VBA, it can only be used if the control has the focus.

I never use Text, and only use Value when I am working in a With/End With to
identify the value. A very simple example would be:

With Me.txtFooBar
If .Value = "blah" Then
.ForeColor = vbGreen
End If
End With
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Think you left out the .text on your FindFirst operation.

I don't think I've ever actually done a FindFirst operation with a wildcard.
Do you still use the "=" or should it be:

.FindFirst "[Phone] like """ & me.txtFilter.Text & "*"""

----
Dale



:

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP


:

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale



:

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Klatuu]

Based on your last comment, I did some additional experimenting. I put the
search text box in the header. But, doing that meant finding an exact match
visually becomes a small hassle, so I added some code to jump to the first
control in the detail portion and null out the search text box:

Option Compare Database
Option Explicit

Private Sub Text0_Change()
If Len(Me.Text0.Text) > 0 Then
With Me.RecordsetClone
.FindFirst "[CLPHONEFU] LIKE '" & Me.Text0.Text & "*'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.Text0.SelStart = Len(Me.Text0.Text) + 1
End If

With Me
If .Text0.Text = .Text6 Then
.Text2.SetFocus
End If
End With
End Sub


Private Sub Text2_GotFocus()
Me.Text0 = Null
End Sub


But, in reality, I wouldn't do it this way. I would just use a combo box.
--
Dave Hargis, Microsoft Access MVP

Dave,

I assume by the level of coding that you have done, that you have now tested
this with a form and data. Is your txtClientPhone textbox in the forms
header/footer?

I guess I'm surprised that the me.txtClientPhone.SelStart line works at all.
I thought that the bookmark line would set the focus to the control in the
details section with the lowest tab stop, thereby move the focus out of
txtClientPhone.

I think we have hijacked Scott's question. ;-)

----
Dale

Apologies, Dale. You are correct. Since I have never done this, I made an
erroneous assumption. But, I did find something else interesting. That is
that if you are using a continuous form, every time you go to the next match,
the cursor is at the beginning of the current instance of the control on the
row and either the user has to move to the end of the text or it will start
over. The solution is to position the cursor at the end of the text.

Also, as a cosmetic effect, I added a line to the Got Focus next control in
the tab order to null the search box.

Private Sub txtClientPhone_Change()
If Len(Me.txtClientPhone.Text) > 0 Then
With Me.RecordsetClone
.FindFirst "[CLPHONEFU] Like """ & Me.txtClientPhone.Text & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.txtClientPhone.SelStart = Len(Me.txtClientPhone.Text) + 1
End If
End With
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Dave,

I'll will have to disagree about your statement regarding .text and .value.

Value is the default, so I almost never use it. But in the Change event, if
you refer to the value of the control, it contains the original value (the
value from the most recent afterUpdate), so you have to use the .text
property to get what is currently in the textbox.

Dale

:

You are correct about the =. That line should be:

.FindFirst "[Phone] Like """ & Me.txtFilter & "*"""

As to the Text property, it is not necessary. Neither is the Value
property, it is the default property returned if no other property is
specified. The Text property is really useless in VBA. It is a hold over
from VB. In VBA, it can only be used if the control has the focus.

I never use Text, and only use Value when I am working in a With/End With to
identify the value. A very simple example would be:

With Me.txtFooBar
If .Value = "blah" Then
.ForeColor = vbGreen
End If
End With
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Think you left out the .text on your FindFirst operation.

I don't think I've ever actually done a FindFirst operation with a wildcard.
Do you still use the "=" or should it be:

.FindFirst "[Phone] like """ & me.txtFilter.Text & "*"""

----
Dale



:

Or, if the objective is to make the matching phone number the current record:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") > 0 then
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.txtFilter & "*"""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
endif

end sub

--
Dave Hargis, Microsoft Access MVP


:

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale



:

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Scott]

You correctly guessed at what I was trying to do and this does what I need it
to do. Thank you very much. Please don't feel bad about hijacking my
question - your discussion is interesting and useful for a novice.

One last problem is that the text in the text box is selected after the
routine runs. I would prefer that the cursor go after the text in the text
box so the user can continue adding numbers to it without having to click at
the end of the text. I couldn't figure out how to do that. (Maybe you
answered the question in the ensuing discussion, but I didn't pick it up.)

For what it's worth, the number of records is not so many (yet) that it
takes a long time to filter. It is actually pretty slick.

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.

 

[Scott]

Please ignore this post. A better phrased search yielded good answers
(.selstart) that I was able to implement. Thanks for your help.

You correctly guessed at what I was trying to do and this does what I need it
to do. Thank you very much. Please don't feel bad about hijacking my
question - your discussion is interesting and useful for a novice.

One last problem is that the text in the text box is selected after the
routine runs. I would prefer that the cursor go after the text in the text
box so the user can continue adding numbers to it without having to click at
the end of the text. I couldn't figure out how to do that. (Maybe you
answered the question in the ensuing discussion, but I didn't pick it up.)

For what it's worth, the number of records is not so many (yet) that it
takes a long time to filter. It is actually pretty slick.

As Dave mentioned, a combo box will do some of what you want.

But based on your description, it sounds like you are actually displaying
the phone numbers in a continuous form, is that the case? If so, then you
probably want to use a textbox, and then add some code to the text boxes
Change event, which would filter your form based on the text in the textbox.
If you name that textbox txtFilter, and put it in the forms header, then the
code might look like:

Private Sub txtFilter_Change

if len(me.txtFilter.Text & "") = 0 then
me.Filter = ""
me.FilterOn = false
else
docmd.hourglass true
me.filter = "[Phone] Like '" & me.txtFilter.Text & "*'"
me.filterOn = true
docmd.hourglass false
endif

end sub

Depending on the number of records this constant filtering could take a
while. It might be better to use a command button next to your textbox, and
only have it update the filter when you actually click the command button.

----
HTH
Dale

I want to have a control on a form that is used to filter the records from a
table. I've seen it on forms I have used, but I don't know how to make it
happen myself. The idea is that I have a table of phone numbers. When the
form is first opened, the form shows all the phone numbers and has a control
where the user can enter numbers that will filter the records that appear.
It should filter the records as the user types the number.

Example: The table has a lot of phone numbers. As soon as the user types a
number in the control, the form will display records that have only the
numbers in the string the user types. So if the number is 112-251-5564, if
the user types a 5, the records that have a 5 in it will show, when he types
a 5 again, only the records with 55 in the phone number will appear, when he
types a 6, only the records with a 556 will appear. Eventually he will get a
list of numbers that is short enough that he can see the number he is looking
for, even if he doesn't have the entire number.

Any help is appreciated.

Note: I have a query that strips dashes and spaces from phone numbers, so
the records are actually based on the query, not the table. i will
eventually have data from multiple tables/queries that are joined, but I
assume this will not be difficult to add later. For now I just want to get
the list of phone numbers to filter as I describe.