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
|
|||
|
|||
requery as each character entered
I'm using a text box (LN) in the main form (Frm_allclients) to filter a
subform (frm_allclientssub). The filter is applied by using the text from the textbox LN in the query with wildcard characters (last name LIKE forms!frm_allclients!LN&"*"). Can someone tell me the code for getting this to requery as each character is added. In the LN textbox I've used the events properties (I thought KeyDown would be my best bet) and tried this (doesn't work). Private Sub LN_KeyDown(KeyCode As Integer, Shift As Integer) DoCmd.Requery "Frm_AllClientsSub" DoCmd.RepaintObject acForm, "Frm_AllClients" Forms!Frm_AllClients!LN.SetFocus End Sub |
#2
|
|||
|
|||
requery as each character entered
While you're typing in the textbox, it doesn't "have" an updated Value yet
that the subform or its recordsource can see, so requerying the subform will not yield a "filter as you go" result. While you're typing in the textbox, the property that is being changed is the Text property, which is not read directly by the subform or its recordsource. Therefore, you'll have to program the entire process for doing this and won't be able to use Requery by itself. One would use the Change event of the textbox to run the appropriate programming. Likely, it'll be necessary for you to read the Text value, generate an SQL statement that uses the Text string to filter the query, and then assign that SQL statement to the subform's RecordSource property (which will automatically "requery" the subform). However, Allen Browne (ACCESS MVP) reports that many of the subform control's other properties (such as LinkChildFields and LinkMasterFields) are set to "empty" when one does such things - but that this does not always occur as a problem. Therefore, it also may be necessary to reset the appropriate subform's properties to your desired values. While not the same setup as what you seek to do here, I have a sample database that uses the change event of a textbox to set up a combo box that contains filtered results based on what is typed in the textbox -- the sample shows how to pair a textbox and a combo box to allow one to use a combo box when its RowSource might otherwise return more than 65,536 records (the maximum for a combo box). The programming that I use in that sample demonstrates the use of the change event and the text property (with a Class Module), so it may be helpful (caution: the programming is a bit complicated!). See http://www.cadellsoftware.org/Sample...TextComboBoxes for the sample database. -- Ken Snell MS ACCESS MVP "Ian" wrote in message ... I'm using a text box (LN) in the main form (Frm_allclients) to filter a subform (frm_allclientssub). The filter is applied by using the text from the textbox LN in the query with wildcard characters (last name LIKE forms!frm_allclients!LN&"*"). Can someone tell me the code for getting this to requery as each character is added. In the LN textbox I've used the events properties (I thought KeyDown would be my best bet) and tried this (doesn't work). Private Sub LN_KeyDown(KeyCode As Integer, Shift As Integer) DoCmd.Requery "Frm_AllClientsSub" DoCmd.RepaintObject acForm, "Frm_AllClients" Forms!Frm_AllClients!LN.SetFocus End Sub |
#3
|
|||
|
|||
requery as each character entered
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 In the form load event of the frm_allclientssub form: Private Sub Form_Load() Me.FilterOn = True End Sub The other option would be to not play around with the filter and filteron property at all but change the subforms recordsource property in the LN_Change event. "Ian" wrote in message ... I'm using a text box (LN) in the main form (Frm_allclients) to filter a subform (frm_allclientssub). The filter is applied by using the text from the textbox LN in the query with wildcard characters (last name LIKE forms!frm_allclients!LN&"*"). Can someone tell me the code for getting this to requery as each character is added. In the LN textbox I've used the events properties (I thought KeyDown would be my best bet) and tried this (doesn't work). Private Sub LN_KeyDown(KeyCode As Integer, Shift As Integer) DoCmd.Requery "Frm_AllClientsSub" DoCmd.RepaintObject acForm, "Frm_AllClients" Forms!Frm_AllClients!LN.SetFocus End Sub |
#4
|
|||
|
|||
requery as each character entered
Thanks so much Bill -- you're a genius. Solved the problem in 2 lines of
code! Turned on the filter with onopen of the subform then the one line of code in the main form. I've been playing around with this for hours. The only addition was I had to put LastName is square brackets because when I creted the union query I was dumb enough to put a space in it. Thanks again. "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 In the form load event of the frm_allclientssub form: Private Sub Form_Load() Me.FilterOn = True End Sub The other option would be to not play around with the filter and filteron property at all but change the subforms recordsource property in the LN_Change event. "Ian" wrote in message ... I'm using a text box (LN) in the main form (Frm_allclients) to filter a subform (frm_allclientssub). The filter is applied by using the text from the textbox LN in the query with wildcard characters (last name LIKE forms!frm_allclients!LN&"*"). Can someone tell me the code for getting this to requery as each character is added. In the LN textbox I've used the events properties (I thought KeyDown would be my best bet) and tried this (doesn't work). Private Sub LN_KeyDown(KeyCode As Integer, Shift As Integer) DoCmd.Requery "Frm_AllClientsSub" DoCmd.RepaintObject acForm, "Frm_AllClients" Forms!Frm_AllClients!LN.SetFocus End Sub |
#5
|
|||
|
|||
requery as each character entered
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 In the form load event of the frm_allclientssub form: Private Sub Form_Load() Me.FilterOn = True End Sub The other option would be to not play around with the filter and filteron property at all but change the subforms recordsource property in the LN_Change event. "Ian" wrote in message ... I'm using a text box (LN) in the main form (Frm_allclients) to filter a subform (frm_allclientssub). The filter is applied by using the text from the textbox LN in the query with wildcard characters (last name LIKE forms!frm_allclients!LN&"*"). Can someone tell me the code for getting this to requery as each character is added. In the LN textbox I've used the events properties (I thought KeyDown would be my best bet) and tried this (doesn't work). Private Sub LN_KeyDown(KeyCode As Integer, Shift As Integer) DoCmd.Requery "Frm_AllClientsSub" DoCmd.RepaintObject acForm, "Frm_AllClients" Forms!Frm_AllClients!LN.SetFocus End Sub |
#6
|
|||
|
|||
requery as each character entered
Create a function that returns the filter string and call that function from
each of the Change events.Something like the following (totally untested and not proofed, but should give you the idea): Private Function FilterCondition as string FilterCondition = "" IF len(me.clientid.text & "") 0 then FilterCOndition = FIlterCondition & " AND [ClientID] LIKE '" & Me.CID.Text & "*'" END IF IF len(me.FN.Text & "") 0 then FilterCondition = FilterCondition & " AND [First Name] LIKE '" & Me.FN.Text & "*'" END IF IF len(Me.LN.Text & "") 0 then FilterCondition = FilterCondition & " AND [Last Name] LIKE '" & Me.LN.Text & "*'" END IF IF Len(Me.HPhone.Text & "") 0 then FilterCondition = FilterCondition & " AND [Home Phone] LIKE '" & Me.HPhone.Text & "*'" endif ' Trim off the leading " AND " IF len(FIlterCondition & "") 0 then FilterCondition = mid$(FilterCondition,5, len(FIlterCondition)) END IF End Function Private SUB CID_Change() ME.frm_AllClientsSub.Form.FIlter = FilterCondition() END SUB PRIVATE SUB FN_Change() ME.frm_AllClientsSub.Form.FIlter = FilterCondition() END SUB "Ian" wrote in message ... 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 In the form load event of the frm_allclientssub form: Private Sub Form_Load() Me.FilterOn = True End Sub The other option would be to not play around with the filter and filteron property at all but change the subforms recordsource property in the LN_Change event. "Ian" wrote in message ... I'm using a text box (LN) in the main form (Frm_allclients) to filter a subform (frm_allclientssub). The filter is applied by using the text from the textbox LN in the query with wildcard characters (last name LIKE forms!frm_allclients!LN&"*"). Can someone tell me the code for getting this to requery as each character is added. In the LN textbox I've used the events properties (I thought KeyDown would be my best bet) and tried this (doesn't work). Private Sub LN_KeyDown(KeyCode As Integer, Shift As Integer) DoCmd.Requery "Frm_AllClientsSub" DoCmd.RepaintObject acForm, "Frm_AllClients" Forms!Frm_AllClients!LN.SetFocus End Sub |
Thread Tools | |
Display Modes | |
|
|