Combo Box needed, entering any part of a field

  • Thread starter Thread starter JohnG via AccessMonster.com
  • Start date Start date
J

JohnG via AccessMonster.com

I have a form in which I want to be able to enter a string of any part of a
field into a combo box which will bring up all the instances in the table
with that string to select from.

Any suggestions in Access Monster Land.

Thanks
JohnG
 
I would recommend doing this with a textbox and a list control. Use the
Change event of the textbox to modify the rowsource of the listbox using the
TEXT property of the textbox. Here is some sample code - with a search on
CustName as an example:

Private Sub txtCustFilter_Change()
Dim strSQL As String
Const strSQL1 As String = "SELECT [Customer].[custid], [Customer].[CustName]
FROM [Customer]"
Const strSQL2 As String = " ORDER BY [CustName];"
Dim strWhere As String
strWhere = "where CustName like ""*" & Me.txtCustFilter.Text & "*"" "
strSQL = strSQL1 & strWhere & strSQL2
Me.lstFAQs.RowSource = strSQL
End Sub


You could use the same logic for a combo box but the application of it seems
a little odd to me.
 
Thanks for the code Sandra.

The reason for the Combo Box approach was that once a list of the "Like"
records appears in the combo box, the user will select one and the column(0)
field of this combo box record will be the key to open another form listing
all the fields in that record.

JohnG

Sandra said:
I would recommend doing this with a textbox and a list control. Use the
Change event of the textbox to modify the rowsource of the listbox using the
TEXT property of the textbox. Here is some sample code - with a search on
CustName as an example:

Private Sub txtCustFilter_Change()
Dim strSQL As String
Const strSQL1 As String = "SELECT [Customer].[custid], [Customer].[CustName]
FROM [Customer]"
Const strSQL2 As String = " ORDER BY [CustName];"
Dim strWhere As String
strWhere = "where CustName like ""*" & Me.txtCustFilter.Text & "*"" "
strSQL = strSQL1 & strWhere & strSQL2
Me.lstFAQs.RowSource = strSQL
End Sub

You could use the same logic for a combo box but the application of it seems
a little odd to me.
I have a form in which I want to be able to enter a string of any
part of a field into a combo box which will bring up all the
Click to expand...
[quoted text clipped - 4 lines]
Thanks
JohnG
Click to expand...
Click to expand...
 
Right - I should have explained myself a bit better. I meant odd in that
most users are conditioned to expect the combo to match on the first letters
typed in the combo. You were asking for the entered text to match any part
of the field (ie. I could enter "ef" and match on the "abcdefg").
Regardless, it will work - I think :-)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for the code Sandra.

The reason for the Combo Box approach was that once a list of the
"Like" records appears in the combo box, the user will select one and
the column(0) field of this combo box record will be the key to open
another form listing all the fields in that record.

JohnG

Sandra said:
I would recommend doing this with a textbox and a list control. Use
the Change event of the textbox to modify the rowsource of the
listbox using the TEXT property of the textbox. Here is some sample
code - with a search on CustName as an example:

Private Sub txtCustFilter_Change()
Dim strSQL As String
Const strSQL1 As String = "SELECT [Customer].[custid],
[Customer].[CustName] FROM [Customer]"
Const strSQL2 As String = " ORDER BY [CustName];"
Dim strWhere As String
strWhere = "where CustName like ""*" & Me.txtCustFilter.Text & "*"" "
strSQL = strSQL1 & strWhere & strSQL2
Me.lstFAQs.RowSource = strSQL
End Sub

You could use the same logic for a combo box but the application of
it seems a little odd to me.
I have a form in which I want to be able to enter a string of any
part of a field into a combo box which will bring up all the
Click to expand...
[quoted text clipped - 4 lines]
Thanks
JohnG
Click to expand...
Click to expand...
Click to expand...
 
Back
Top