requery as each character entered

[Guest]

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

 

[Ken Snell \(MVP\)]

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/SampleDBs.htm#CombinedTextComboBoxes for the
sample database.

 

[Bill Edwards]

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.

 

[Guest]

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.

 

[Guest]

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]

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

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

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.