Combo box filter

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

Hi,

I have a table that has two fields: a number field and a
text field. I also have a simple for that has a text box
where I enter a number and a combo box to display the
table records. Based on the number entered I want the
combo box to display the related records based on the
number field in the table. I can't get it to work. If I
change the number in the text box, click on the combo box
the correct records don't always show.

However, if I enter a number in the text box, go
to "design" view and return and then click on the combo
box, the combo box works every time. I can change the
text box again, go to design view, return and it works.
What am I missing?

Thanks,
Phil
 
Since you have this partially working, you may need to post details of the
code you are using.

Assuming that both the text box and the combo are unbound (nothing in their
Control Source) so there is no issue of having to save the current record
before the filter is applied, I assume you are using the AfterUpdate of the
text box to set the RowSource of the combo, and the AfterUpdate of the combo
to set the Filter of the form.

Which is the Bound Column of the combo?
Look at its RowSource, and open that table in design view.
What is the Data Type of the field that is the Bound Column?
If it is a Text type field (not a Number type field), add extra quotes to
the line in the exmaple below, e.g.:
strSQL = strSQL & "([MyField2Match] = """ & Me.txtNum & """;"


Private Sub txtNum_AfterUpdate
Dim strSql As String

strSQL = "SELECT MyId, MyText FROM MyComboTable WHERE "

If IsNull(Me.txtNum) Then
strSQL = strSQL & "(False);"
Else
strSQL = strSQL & "([MyField2Match] = " & Me.txtNum & ";"
End If

Me.cbo.RowSource = strSQL
End Sub


The same principle applies to the AfterUpdate of the combo: set the Filter
of the form, including the quote mark delimiters if the filtered field is a
Text field, but not if it is a Number field.
 
Thanks Allen,

That worked great.
Next question: Is it possible to have more than one line
in a combo box? In other words, I would like each
selections in the combo box to be two lines instead of
just one. I need to display more data than one line can
show.

Thanks,
Phil
-----Original Message-----
Since you have this partially working, you may need to post details of the
code you are using.

Assuming that both the text box and the combo are unbound (nothing in their
Control Source) so there is no issue of having to save the current record
before the filter is applied, I assume you are using the AfterUpdate of the
text box to set the RowSource of the combo, and the AfterUpdate of the combo
to set the Filter of the form.

Which is the Bound Column of the combo?
Look at its RowSource, and open that table in design view.
What is the Data Type of the field that is the Bound Column?
If it is a Text type field (not a Number type field), add extra quotes to
the line in the exmaple below, e.g.:
strSQL = strSQL & "([MyField2Match] = """ & Me.txtNum & """;"


Private Sub txtNum_AfterUpdate
Dim strSql As String

strSQL = "SELECT MyId, MyText FROM MyComboTable WHERE "

If IsNull(Me.txtNum) Then
strSQL = strSQL & "(False);"
Else
strSQL = strSQL & "([MyField2Match] = " & Me.txtNum & ";"
End If

Me.cbo.RowSource = strSQL
End Sub


The same principle applies to the AfterUpdate of the combo: set the Filter
of the form, including the quote mark delimiters if the filtered field is a
Text field, but not if it is a Number field.

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

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

Hi,

I have a table that has two fields: a number field and a
text field. I also have a simple for that has a text box
where I enter a number and a combo box to display the
table records. Based on the number entered I want the
combo box to display the related records based on the
number field in the table. I can't get it to work. If I
change the number in the text box, click on the combo box
the correct records don't always show.

However, if I enter a number in the text box, go
to "design" view and return and then click on the combo
box, the combo box works every time. I can change the
text box again, go to design view, return and it works.
What am I missing?

Thanks,
Phil
Click to expand...


.
Click to expand...
 
No.

But you can use a text box to show more information. For example, if the
combo is named "cbo1", and the text you want to see is in the 2nd column,
the ControlSource of your text box would be:
=[cbo1].Column(1)

(The Column property is zero based, i.e. the first column is zero).
 
Back
Top