If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
combining filter fields
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 "Bill Edwards" wrote: 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 |
#2
|
|||
|
|||
combining filter fields
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
#3
|
|||
|
|||
combining filter fields
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. "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
#4
|
|||
|
|||
combining filter fields
Just use:
Me.Address instead of: Me.Address.Text -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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. "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
#5
|
|||
|
|||
combining filter fields
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]*' "Allen Browne" wrote: Just use: Me.Address instead of: Me.Address.Text -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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. "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
#6
|
|||
|
|||
combining filter fields
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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]*' "Allen Browne" wrote: Just use: Me.Address instead of: Me.Address.Text "Ian" wrote in message ... 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. "Allen Browne" wrote: 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. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
#7
|
|||
|
|||
combining filter fields
These are just unbound text boxes. Not sure how/what I should be converting
the data to. "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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]*' "Allen Browne" wrote: Just use: Me.Address instead of: Me.Address.Text "Ian" wrote in message ... 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. "Allen Browne" wrote: 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. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
#8
|
|||
|
|||
combining filter fields
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? "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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]*' "Allen Browne" wrote: Just use: Me.Address instead of: Me.Address.Text "Ian" wrote in message ... 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. "Allen Browne" wrote: 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. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
#9
|
|||
|
|||
combining filter fields
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. "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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]*' "Allen Browne" wrote: Just use: Me.Address instead of: Me.Address.Text "Ian" wrote in message ... 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. "Allen Browne" wrote: 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. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
#10
|
|||
|
|||
combining filter fields
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ian" wrote in message ... 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? "Allen Browne" wrote: 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. "Ian" wrote in message ... 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]*' "Allen Browne" wrote: Just use: Me.Address instead of: Me.Address.Text "Ian" wrote in message ... 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. "Allen Browne" wrote: 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. "Ian" wrote in message ... 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 "Bill Edwards" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|