Search Form with a Dynamic Listbox

[Solo712]

Hi everyone, a newbie here. I have introduced myself in another thread.

It has become clear to me over the past few weeks that boards like this one are an enormous resource. They have already saved me tons of time on little piddly problems and bigger ones as well. So I thought I'd share one of my little discoveries as way of saying "thanks and glad to have you around".

Here is a type of problem that I found tricky: create a listbox from which to pick a row in a table to use on another form. Restrict the displayed rows (that find record keys) based on input by the form user, allowing partial names (on SQL 'LIKE'). Allow reverting to the display of all rows (there will be max 2000 recs).

After some searching (on boards like this one) I finally got to the gist of the problem. The SQL string retains the filter. To keep reusing it some cutting and pasting had to be done. Essentially the SQL string has to be cut up into three segments. The first one terminates with the "FROM

" statement, the second one starts with "ORDER BY" and the third is the filtering "WHERE" section which is inserted between the two (or not, depending).

The startup query (in the listbox'es Row Source property) looks like this.

SELECT [tblOwnerFName] & " " & [tblOwnerLname] AS Expr1, tblOwnerID FROM Owners ORDER BY Owners.tblOwnerLName;

The sub is triggered after the user moves of the entry field:

Private Sub Text2_AfterUpdate()
   Dim SQLStr, SQLStr1, SQLStr2 As String
   Dim ordrstr, wherestr As Integer
 
   ' Requery the listbox through filter by
   ' user input in Text2
 
    SQLStr = Me!OwnerList.RowSource
    ordrstr = InStr(SQLStr, " ORDER")
    wherestr = InStr(SQLStr, " WHERE")
    SQLStr2 = Mid(SQLStr, ordrstr)
 
    ' this removes result of previous filter
    ' from the new SQL string
    If wherestr <> 0 Then ordrstr = wherestr
 
    ' expunges the previous WHERE clause if any
    SQLStr = Mid(SQLStr, 1#, ordrstr - 1)
 
    If Me!Text2 = "*" Then    'an asterisk in the field removes the filter
       SQLStr1 = ""
    Else
       SQLStr1 = " WHERE tblOwnerLname LIKE '" & Me!Text2 & "*'"
    End If
 
    SQLStr = SQLStr + SQLStr1 + SQLStr2
 
    Me!OwnerList.RowSource = SQLStr
    Me!OwnerList.Requery
End Sub

Works like a charm for me. I do hope you will find this technique useful otherwise my thank-you note is f***ed.

Best,
Jiri