Full Version: Listbox Search
UtterAccess Forums > Microsoft® Access > Access Forms
petite39
On my form is a listbox that has a very large amount of data.
want a way to type into a search box to easily get to the right data starting point. It's in company name order and if the company name starts with a "P", I'd like to jump to that point in the listbox.
Oplaced a text box on the form called: txtsearch
The on change event looks like:
Private Sub txtsearch_Change()
Dim strLike As String
' variable to hold the search text
Dim strSQL As String
' variable to hold listbox Rowsource
' Grab what user has typed
strLike = Me.txtsearch.Text
' Build SQL for listbox RowSource
strSQL = "SELECT tblshipto.SCoName, tblshipto.SCustID, tblshipto.SAddress1, tblshipto.SCity, tblMainCoverPage.Date, tblMainCoverPage.DocID, tblInspectForms.InspectForm, tblFormsUsed.NoOfUnits, tblFormsUsed.FormID " & _
"FROM (tblshipto INNER JOIN tblMainCoverPage ON tblshipto.SCustID = tblMainCoverPage.SCustID) INNER JOIN (tblInspectForms INNER JOIN tblFormsUsed ON tblInspectForms.FormID = tblFormsUsed.FormID) ON tblMainCoverPage.MainID = tblFormsUsed.MainID " & _
"ORDER BY tblshipto.SCoName, tblMainCoverPage.Date DESC;"
Me.SCoName.RowSource = strSQL
End Sub
I can't get it to jump to where I want. Any ideas what I'm doing wrong?
theDBguy
Hi,
don't see anywhere in the SQL where you used the strLike variable...
Check out Allen Browne's website for some ideas:
Combos with Tens of Thousands of Records
Hope that helps...
Jeff B.
I didn't spot your "WHERE" clause ... if you type in the letter "P", wouldn't you want to limit the source on the listbox to those records that start with a "P"?
Jeff B.
By the way, a listbox takes up a lot of screen real-estate. Have you considered using a combobox instead?
And Allen Browne's website has a routine for limiting the rows in a combobox by first typing in "n" characters...
datAdrenaline
Unrelated at this point ... but with the following:
trLike = Me.txtsearch.Text
It is important to note that the .Text property is only a valid call IF the control has the focus. In this case you are ok since this is in the AfterUpdate of the control being used. However, I would encourage you to use .Value (which is the default property, so you don't even need to specify it).
strLike = Me.txtsearch
petite39
Thank you for all the info. I'm still lost. I'm not proficient in this at all and I got the code online somewhere a long time ago and put it to use with another listbox and got it to work....but that was so long ago. I'll keep plugging away I suppose.
petite39
I did consider a combox box but I need to choose with the ctrl key a number of lines sometimes, not just one line.
petite39
Yeah I guess that's my problem but I don't know how to put the where clause in that would accept only the fist few characters typed in txtsearch box.
theDBguy
Hi,
Try the following:
Private Sub txtsearch_Change()
Dim strLike As String
' variable to hold the search text
Dim strSQL As String
' variable to hold listbox Rowsource
' Grab what user has typed
strLike = Me.txtsearch.Text
' Build SQL for listbox RowSource
strSQL = "SELECT tblshipto.SCoName, tblshipto.SCustID, tblshipto.SAddress1, tblshipto.SCity, tblMainCoverPage.Date, tblMainCoverPage.DocID, tblInspectForms.InspectForm, tblFormsUsed.NoOfUnits, tblFormsUsed.FormID " & _
"FROM (tblshipto INNER JOIN tblMainCoverPage ON tblshipto.SCustID = tblMainCoverPage.SCustID) INNER JOIN (tblInspectForms INNER JOIN tblFormsUsed ON tblInspectForms.FormID = tblFormsUsed.FormID) ON tblMainCoverPage.MainID = tblFormsUsed.MainID " & _
"WHERE tblshipto.SCoName Like '" & strLike & "*'" & _
"ORDER BY tblshipto.SCoName, tblMainCoverPage.Date DESC;"
Me.SCoName.RowSource = strSQL
End Sub
(just a guess)
Hope that helps...
petite39
Yes that's it! Thanks!
theDBguy
Hi,
Jeff, Brent, and I are happy to help. Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.