Solo712
Registered User.
- Local time
- Today, 17:03
- Joined
- Oct 19, 2012
- Messages
- 838
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.
The sub is triggered after the user moves of the entry field:
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
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
The startup query (in the listbox'es Row Source property) looks like this.
Code:
SELECT [tblOwnerFName] & " " & [tblOwnerLname] AS Expr1, tblOwnerID FROM Owners ORDER BY Owners.tblOwnerLName;
The sub is triggered after the user moves of the entry field:
Code:
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