combining filter fields

[Ian]

someone was nice enough to point me in the correct direction to apply filters
using text boxes. However, after finishing the string I realized the filters
were acting individually and I had no idea how to combine 3 or 4 text boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering based on
each of 4 fields (LastName, firstname, id and phone). With the query method
I could make it use a combination of the filters to filter the records. with
the VBA filter method it only uses the last updated field. I assume I need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4 Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text & "*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" & Me.FN.Text & "*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" & Me.HPhone.Text &
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text & "*'"
End Sub

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database demonstrates how
to do that efficiently. And it is constructed so that it is simple to add as
many boxes as you might need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

someone was nice enough to point me in the correct direction to apply
filters
using text boxes. However, after finishing the string I realized the
filters
were acting individually and I had no idea how to combine 3 or 4 text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering based on
each of 4 fields (LastName, firstname, id and phone). With the query
method
I could make it use a combination of the filters to filter the records.
with
the VBA filter method it only uses the last updated field. I assume I
need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text &
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" & Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" & Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text &
"*'"
End Sub

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" & Me.LN.Text & "*'"
' The filteron property could be set on here, or it could have been set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub

 

[Ian]

Thanks for the article Allen. I've almost got this thing working. I can
filter based on any individual field my only problem now is I can reference
on control when it doesn't have the focus. I assume I need to delcare the
text in another control as some sort of string (as you can tell I have no VBA
experience). Here's the code I think will work without the 2nd control
declared as a string:

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Name] LIKE '" & Me.LN.Text & "*' AND
[Address] LIKE '" & Me.address.Text & "*'"
End Sub

But how do I reference the Me.Address.Text control while in the LN field?

Thanks Ian.

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database demonstrates how
to do that efficiently. And it is constructed so that it is simple to add as
many boxes as you might need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

someone was nice enough to point me in the correct direction to apply
filters
using text boxes. However, after finishing the string I realized the
filters
were acting individually and I had no idea how to combine 3 or 4 text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering based on
each of 4 fields (LastName, firstname, id and phone). With the query
method
I could make it use a combination of the filters to filter the records.
with
the VBA filter method it only uses the last updated field. I assume I
need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text &
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" & Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" & Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text &
"*'"
End Sub

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" & Me.LN.Text & "*'"
' The filteron property could be set on here, or it could have been set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub

 

[Allen Browne]

Just use:
Me.Address
instead of:
Me.Address.Text

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the article Allen. I've almost got this thing working. I can
filter based on any individual field my only problem now is I can
reference
on control when it doesn't have the focus. I assume I need to delcare the
text in another control as some sort of string (as you can tell I have no
VBA
experience). Here's the code I think will work without the 2nd control
declared as a string:

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Name] LIKE '" & Me.LN.Text & "*' AND
[Address] LIKE '" & Me.address.Text & "*'"
End Sub

But how do I reference the Me.Address.Text control while in the LN field?

Thanks Ian.

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database demonstrates
how
to do that efficiently. And it is constructed so that it is simple to add
as
many boxes as you might need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

someone was nice enough to point me in the correct direction to apply
filters
using text boxes. However, after finishing the string I realized the
filters
were acting individually and I had no idea how to combine 3 or 4 text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering based
on
each of 4 fields (LastName, firstname, id and phone). With the query
method
I could make it use a combination of the filters to filter the records.
with
the VBA filter method it only uses the last updated field. I assume I
need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text &
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" & Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" &
Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text &
"*'"
End Sub

:

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" & Me.LN.Text &
"*'"
' The filteron property could be set on here, or it could have been
set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub

 

[Ian]

Tried it an no go -- if you take out the .text it doesn't transfer the
contents of the field to the filter. You just get left with Filter: Like
'*' instead of the Like '[field]*'

Just use:
Me.Address
instead of:
Me.Address.Text

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the article Allen. I've almost got this thing working. I can
filter based on any individual field my only problem now is I can
reference
on control when it doesn't have the focus. I assume I need to delcare the
text in another control as some sort of string (as you can tell I have no
VBA
experience). Here's the code I think will work without the 2nd control
declared as a string:

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Name] LIKE '" & Me.LN.Text & "*' AND
[Address] LIKE '" & Me.address.Text & "*'"
End Sub

But how do I reference the Me.Address.Text control while in the LN field?

Thanks Ian.

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database demonstrates
how
to do that efficiently. And it is constructed so that it is simple to add
as
many boxes as you might need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

someone was nice enough to point me in the correct direction to apply
filters
using text boxes. However, after finishing the string I realized the
filters
were acting individually and I had no idea how to combine 3 or 4 text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering based
on
each of 4 fields (LastName, firstname, id and phone). With the query
method
I could make it use a combination of the filters to filter the records.
with
the VBA filter method it only uses the last updated field. I assume I
need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text &
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" & Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" &
Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text &
"*'"
End Sub

:

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" & Me.LN.Text &
"*'"
' The filteron property could be set on here, or it could have been
set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub

 

[Allen Browne]

The default property of a control in Access is its Value. Text applies only
while the control has focus.

Once the user enters the text, before they leave the control the Text it
converted into the Value. If the Text cannot be converted into the Value
(e.g. a bad date, or alpha characters in a number field), an error occurs,
and the user cannot leave the control.

You therefore need to design your interface so that the user leaves the
control or you in some other way force its Value to be updated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Tried it an no go -- if you take out the .text it doesn't transfer the
contents of the field to the filter. You just get left with Filter: Like
'*' instead of the Like '[field]*'

Just use:
Me.Address
instead of:
Me.Address.Text

Thanks for the article Allen. I've almost got this thing working. I
can
filter based on any individual field my only problem now is I can
reference
on control when it doesn't have the focus. I assume I need to delcare
the
text in another control as some sort of string (as you can tell I have
no
VBA
experience). Here's the code I think will work without the 2nd control
declared as a string:

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Name] LIKE '" & Me.LN.Text & "*'
AND
[Address] LIKE '" & Me.address.Text & "*'"
End Sub

But how do I reference the Me.Address.Text control while in the LN
field?

Thanks Ian.



:

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database
demonstrates
how
to do that efficiently. And it is constructed so that it is simple to
add
as
many boxes as you might need.

someone was nice enough to point me in the correct direction to
apply
filters
using text boxes. However, after finishing the string I realized
the
filters
were acting individually and I had no idea how to combine 3 or 4
text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering
based
on
each of 4 fields (LastName, firstname, id and phone). With the
query
method
I could make it use a combination of the filters to filter the
records.
with
the VBA filter method it only uses the last updated field. I assume
I
need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text
&
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" &
Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" &
Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text
&
"*'"
End Sub

:

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" & Me.LN.Text &
"*'"
' The filteron property could be set on here, or it could have been
set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub

 

[Ian]

These are just unbound text boxes. Not sure how/what I should be converting
the data to.

The default property of a control in Access is its Value. Text applies only
while the control has focus.

Once the user enters the text, before they leave the control the Text it
converted into the Value. If the Text cannot be converted into the Value
(e.g. a bad date, or alpha characters in a number field), an error occurs,
and the user cannot leave the control.

You therefore need to design your interface so that the user leaves the
control or you in some other way force its Value to be updated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Tried it an no go -- if you take out the .text it doesn't transfer the
contents of the field to the filter. You just get left with Filter: Like
'*' instead of the Like '[field]*'

Just use:
Me.Address
instead of:
Me.Address.Text

Thanks for the article Allen. I've almost got this thing working. I
can
filter based on any individual field my only problem now is I can
reference
on control when it doesn't have the focus. I assume I need to delcare
the
text in another control as some sort of string (as you can tell I have
no
VBA
experience). Here's the code I think will work without the 2nd control
declared as a string:

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Name] LIKE '" & Me.LN.Text & "*'
AND
[Address] LIKE '" & Me.address.Text & "*'"
End Sub

But how do I reference the Me.Address.Text control while in the LN
field?

Thanks Ian.



:

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database
demonstrates
how
to do that efficiently. And it is constructed so that it is simple to
add
as
many boxes as you might need.

someone was nice enough to point me in the correct direction to
apply
filters
using text boxes. However, after finishing the string I realized
the
filters
were acting individually and I had no idea how to combine 3 or 4
text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering
based
on
each of 4 fields (LastName, firstname, id and phone). With the
query
method
I could make it use a combination of the filters to filter the
records.
with
the VBA filter method it only uses the last updated field. I assume
I
need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text
&
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" &
Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" &
Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text
&
"*'"
End Sub

:

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" & Me.LN.Text &
"*'"
' The filteron property could be set on here, or it could have been
set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub

 

[Ian]

Think I'm almost there Allen. I created another textbox called Text15 and as
it's control source built up the statement using the other text boxes and
characters as necessary.

Subform on load -- Me.FilterOn = true
Text15 contol source: "[Name] Like '" & [LN] & "*'" & " AND [Address] Like
'" & [Address] & "*'" & " AND [Phone] Like '" & [Phone] & "*'" & " AND [ID]
Like '" & [ClientID] & "*'"

Text 15 on GotFocus
Me.Frm_AllClientsSub.Form.Filter = Me.Text15.Text

Last problem is if I set the OnChange event of the LN text boxes to switch
back and forth to the Text15 it highlights the entire contents of the LN
textbox when it returns. Anyway to put my cursor at the end of the text when
it returns?

The default property of a control in Access is its Value. Text applies only
while the control has focus.

Once the user enters the text, before they leave the control the Text it
converted into the Value. If the Text cannot be converted into the Value
(e.g. a bad date, or alpha characters in a number field), an error occurs,
and the user cannot leave the control.

You therefore need to design your interface so that the user leaves the
control or you in some other way force its Value to be updated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Tried it an no go -- if you take out the .text it doesn't transfer the
contents of the field to the filter. You just get left with Filter: Like
'*' instead of the Like '[field]*'

Just use:
Me.Address
instead of:
Me.Address.Text

Thanks for the article Allen. I've almost got this thing working. I
can
filter based on any individual field my only problem now is I can
reference
on control when it doesn't have the focus. I assume I need to delcare
the
text in another control as some sort of string (as you can tell I have
no
VBA
experience). Here's the code I think will work without the 2nd control
declared as a string:

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Name] LIKE '" & Me.LN.Text & "*'
AND
[Address] LIKE '" & Me.address.Text & "*'"
End Sub

But how do I reference the Me.Address.Text control while in the LN
field?

Thanks Ian.



:

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database
demonstrates
how
to do that efficiently. And it is constructed so that it is simple to
add
as
many boxes as you might need.

someone was nice enough to point me in the correct direction to
apply
filters
using text boxes. However, after finishing the string I realized
the
filters
were acting individually and I had no idea how to combine 3 or 4
text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering
based
on
each of 4 fields (LastName, firstname, id and phone). With the
query
method
I could make it use a combination of the filters to filter the
records.
with
the VBA filter method it only uses the last updated field. I assume
I
need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text
&
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" &
Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" &
Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text
&
"*'"
End Sub

:

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" & Me.LN.Text &
"*'"
' The filteron property could be set on here, or it could have been
set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub

 

[Ian]

GOT IT!!!! Only 3 lines of codes per text box and the Control string.
Suform On Load: Me.FilterOn = True

Control of text15 (holds string of each text)
="[Name] Like '" & [LN] & "*'" & " AND [Address] Like '" & [Address] & "*'"
& " AND [Phone] Like '" & [Phone] & "*'" & " AND [ID] Like '" & [ClientID] &
"*'"
GotFocus Event of text15
Me.Frm_AllClientsSub.Form.Filter = Me.Text15.Text

In each text box (in this case LN, Address, CliientID and Phone) change event:
Text15.SetFocus
LN.SetFocus
Me!LN.SelStart = Me!LN.SelLength

So far working like a charm. Thanks for the help Allen. You rock.

The default property of a control in Access is its Value. Text applies only
while the control has focus.

Once the user enters the text, before they leave the control the Text it
converted into the Value. If the Text cannot be converted into the Value
(e.g. a bad date, or alpha characters in a number field), an error occurs,
and the user cannot leave the control.

You therefore need to design your interface so that the user leaves the
control or you in some other way force its Value to be updated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Tried it an no go -- if you take out the .text it doesn't transfer the
contents of the field to the filter. You just get left with Filter: Like
'*' instead of the Like '[field]*'

Just use:
Me.Address
instead of:
Me.Address.Text

Thanks for the article Allen. I've almost got this thing working. I
can
filter based on any individual field my only problem now is I can
reference
on control when it doesn't have the focus. I assume I need to delcare
the
text in another control as some sort of string (as you can tell I have
no
VBA
experience). Here's the code I think will work without the 2nd control
declared as a string:

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Name] LIKE '" & Me.LN.Text & "*'
AND
[Address] LIKE '" & Me.address.Text & "*'"
End Sub

But how do I reference the Me.Address.Text control while in the LN
field?

Thanks Ian.



:

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database
demonstrates
how
to do that efficiently. And it is constructed so that it is simple to
add
as
many boxes as you might need.

someone was nice enough to point me in the correct direction to
apply
filters
using text boxes. However, after finishing the string I realized
the
filters
were acting individually and I had no idea how to combine 3 or 4
text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering
based
on
each of 4 fields (LastName, firstname, id and phone). With the
query
method
I could make it use a combination of the filters to filter the
records.
with
the VBA filter method it only uses the last updated field. I assume
I
need
to join the filter codes together under each of the OnChange event
properties. Can you tell me how to join the fields? Here are the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" & Me.CID.Text
&
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" &
Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" &
Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" & Me.LN.Text
&
"*'"
End Sub

:

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" & Me.LN.Text &
"*'"
' The filteron property could be set on here, or it could have been
set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub

 

[Allen Browne]

You can use SelStart, SelLength, and Len() to figure out place the cursor
where you want.

Seems like a sledgehammer cracking a walnut though. The description of Text
and Value applies to unbound boxes as well as bound.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Think I'm almost there Allen. I created another textbox called Text15 and
as
it's control source built up the statement using the other text boxes and
characters as necessary.

Subform on load -- Me.FilterOn = true
Text15 contol source: "[Name] Like '" & [LN] & "*'" & " AND [Address] Like
'" & [Address] & "*'" & " AND [Phone] Like '" & [Phone] & "*'" & " AND
[ID]
Like '" & [ClientID] & "*'"

Text 15 on GotFocus
Me.Frm_AllClientsSub.Form.Filter = Me.Text15.Text

Last problem is if I set the OnChange event of the LN text boxes to switch
back and forth to the Text15 it highlights the entire contents of the LN
textbox when it returns. Anyway to put my cursor at the end of the text
when
it returns?

The default property of a control in Access is its Value. Text applies
only
while the control has focus.

Once the user enters the text, before they leave the control the Text it
converted into the Value. If the Text cannot be converted into the Value
(e.g. a bad date, or alpha characters in a number field), an error
occurs,
and the user cannot leave the control.

You therefore need to design your interface so that the user leaves the
control or you in some other way force its Value to be updated.

Tried it an no go -- if you take out the .text it doesn't transfer the
contents of the field to the filter. You just get left with Filter:
Like
'*' instead of the Like '[field]*'

:

Just use:
Me.Address
instead of:
Me.Address.Text

Thanks for the article Allen. I've almost got this thing working.
I
can
filter based on any individual field my only problem now is I can
reference
on control when it doesn't have the focus. I assume I need to
delcare
the
text in another control as some sort of string (as you can tell I
have
no
VBA
experience). Here's the code I think will work without the 2nd
control
declared as a string:

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Name] LIKE '" & Me.LN.Text &
"*'
AND
[Address] LIKE '" & Me.address.Text & "*'"
End Sub

But how do I reference the Me.Address.Text control while in the LN
field?

Thanks Ian.



:

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The answer does involve some code, but the sample database
demonstrates
how
to do that efficiently. And it is constructed so that it is simple
to
add
as
many boxes as you might need.

someone was nice enough to point me in the correct direction to
apply
filters
using text boxes. However, after finishing the string I realized
the
filters
were acting individually and I had no idea how to combine 3 or 4
text
boxes
to create one filter.

Here is the sting. Hope someone can help. Thanks Ian.

Hey Bill -- hope your still around. I've got the form filtering
based
on
each of 4 fields (LastName, firstname, id and phone). With the
query
method
I could make it use a combination of the filters to filter the
records.
with
the VBA filter method it only uses the last updated field. I
assume
I
need
to join the filter codes together under each of the OnChange
event
properties. Can you tell me how to join the fields? Here are
the 4
Change
events.

Private Sub CID_Change()
Me.Frm_AllClientsSub.Form.Filter = "[ClientID] LIKE '" &
Me.CID.Text
&
"*'"
End Sub

Private Sub FN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[First Name] LIKE '" &
Me.FN.Text &
"*'"
End Sub



Private Sub HPhone_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Home Phone] LIKE '" &
Me.HPhone.Text
&
"*'"
End Sub

Private Sub LN_Change()
Me.Frm_AllClientsSub.Form.Filter = "[Last Name] LIKE '" &
Me.LN.Text
&
"*'"
End Sub

:

In the LN text box of the main form:

Private Sub LN_Change()
Me.frm_allclientssub.Form.Filter = "LastName LIKE '" &
Me.LN.Text &
"*'"
' The filteron property could be set on here, or it could have
been
set
on
previously
' for example in the Form_Load event of the subform (see below)
' Me.frm_allclientssub.Form.FilterOn = True
End Sub