Re: How to further filter a row source from a query by the value of a control?

[Dirk Goldgar]

Hi,

I have a form. Let us say there are two controls on it.
One is text box. The other is a combox box. The combo box
has a table/query row source "select controlid from
query_abc".
I would like results of the query_abc to further narrow
down based on the value from the text box and whenever the
value in the text box changes, the query results will be
refreshed to reflect the new value in the text box.

I have tried some ways, but seems it is hard to get the
query results requeried to reflect a changed value in the
test box.
I am new to Access form programming, so please help me and
I need it to finish one of my project due soon.

You don't say in what way you want to filter the query results based on
the text box, but here's one general way. Assume the combo box is named
"cboControlID" and the text box is named "txtFilterValue", and that you
only want to see items where the field "ControlType" is the same as
specified in the text box. You'd put code in the AfterUpdate event of
txtFilterValue to rewrite the RowSource property of cboControlID, like
this:

'----- start of example code -----
Private Sub txtFilterValue_AfterUpdate()

Dim strRowSource As String

strRowSource = "SELECT ControlID FROM query_abc"

If IsNull(Me!txtFilterValue) Then
' leave strRowSource unfiltered.
Else
' add a WHERE clause to the rowsource SQL statement.
strRowSource = strRowSource & _
" WHERE ControlType = '" & Me!txtFilterValue & "'"
End If

Me!cboControlID.RowSource = strRowSource & ";"

End Sub

 

[Thomas]

Dirk,

Your example is exactly I want to accomplish in my code.
Thank you a great deal.
Let me try it out now! So happy to know this method!!!!

-----Original Message-----


You don't say in what way you want to filter the query results based on
the text box, but here's one general way. Assume the combo box is named
"cboControlID" and the text box is

named "txtFilterValue", and that you