Combo/List Box Coding

  • Thread starter Thread starter mbox204
  • Start date Start date
M

mbox204

Hello,

I am comfortable with use and incorporation of combo and list boxes in forms
however, I would like to know more about the actual code that makes these
objects work the way they do. The purpose, I am attempting to use the same
coding logic in a text box that will drill down a list in a listbox. For
example, I would begin to type a few letters in a text box then the
corresponding listbox below would begin to drill down corresponding to the
matches of the typed letters in the corresponding text box.

I have made an attempt at solving this problem with use of a query specified
as the row source in the listbox. The query has a reference to the text box
and an "on change" event for that text box. The "on change" makes reference
to:

Forms!frmardb_lookup!List45.RowSource = "qryphones_search_lookup_lname"
Forms!frmardb_lookup.List45.Requery

In my query, I can get results for which the row source query references,
bringing forth all records in the table. My theory is that I need to using
something like the following as part of the query condition: WHERE
<statement> LIKE <build a string from textbox plus the access wildcard *> I
have attempted to build the string using something like this LIKE
Left(Forms!frmARDB_lookup!text136, <some string length variable>) & '*'

I am having no luck, though I think I may be close to the solution, at least
perhaps on the right track.

I could use some guidance as I keep researching.

I know I could just use the combo box and be done with it, I like challenge.

Thank you in advance for your assistance.
 
You are on the right track - I generally do this using inline SQL to build
the Rowsource query - and I do this using the Change event of the textbox.
Note that the following code uses the Text property of the text box - this
property contains whatever has been typed into the control, before it it is
saved.

Private Sub Text6_Change()
Const strBaseSQL As String = "Select Custid, Custname from Customer "
Me.txtSQL = strBaseSQL & "Where Custname like """ _
& Me.Text6.Text & "*"" Order by Custname;"
Me.List2.RowSource = Me.txtSQL
End Sub
 
Back
Top