Solved

Access Vb - Filtering a List Control as a Data input in a Control Textbox

Posted on 2013-01-05
22
411 Views
Last Modified: 2013-01-06
Hello all and Happy New Year!

Can you filter a List as you input new data into a Control Textbox????

Have a Form.  On that form I have a Listbox to display all of the "Existing" Streets in the Db.   The form is bound to dta_Streets and The user inputs the new Street name into StreetName.

As the user inputs a new Street Name in the Form's Control for StreetName I would like for that list box to be filtered as the characters are input so that if they input the letter M then all streets with M show then as they enter the 2nd character, let's say it's "A" then the list filters to Streets beginning with "MA" then as the enter the 3rd Character, let's say "I" then it filters the list to "MAI" etc........

This is the code I "Attempted" that with but.............. well you know......

Private Sub StreetName_Change()
Dim strStreet As String, lngStreetLength As Long

strStreet = Nz(Me.StreetName, "")
lngStreetLength = Nz(Len(Me.StreetName), 0)

    Me.listExistingStreets.RowSource = "SELECT AddressStreetID, StreetName" & _
    " FROM dta_Streets" & _
    " WHERE (((dta_Streets.AddressStreetID)>2)) AND Left([StreetName]," & lngStreetLength & ")=" & strStreet & _
    " GROUP BY dta_Streets.AddressStreetID, dta_Streets.StreetName ORDER BY dta_Streets.StreetName; "
    Me.listExistingStreets.Requery
End Sub

Open in new window

0
Comment
Question by:wlwebb
  • 9
  • 8
  • 5
22 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 125 total points
ID: 38747993
You can use LIKE for this type of comparison.  This is the basic logic for your Change Event Code:


strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets WHERE StreetName LIKE " 
strSearch =  chr(34)  &  Me.StreetName & "*"  & chr(34) 
Me.listExistingStreets.RowSource = strSQL & strSearch 

Open in new window



Try out the sample database in my article here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_10162-What-is-in-a-field-Value-vs-Text-and-the-differences-between-form-data-and-table-data.html


Edit:

Calling it a night here - so I personally won't be able to follow up until tomorrow.
0
 

Author Comment

by:wlwebb
ID: 38748032
Thanks Mbiz......

Tried these two different approaches but neither did the trick so I must be missing something.....

Dim strSQL As String,  strSearch As String

strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets WHERE AddressStreetID > 2 AND StreetName LIKE "
strSearch = Chr(34) & Me.StreetName & "*" & Chr(34)
Me.listExistingStreets.RowSource = strSQL & 2 & strSQL2 & strSearch

Open in new window


Dim strSQL As String, strSQL2 As String, strSearch As String

strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets WHERE AddressStreetID >"
strSQL2 = " AND StreetName LIKE "
strSearch = Chr(34) & Me.StreetName & "*" & Chr(34)
Me.listExistingStreets.RowSource = strSQL & 2 & strSQL2 & strSearch

Open in new window


Note,  In my list I have to exclude AddressStreetID's 1 & 2
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38748100
i'm not sure what criteria you're looking for for AddressStreetID but this is how you could do it for StreetName:

Dim strSQL As String

strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets " &  _
    "WHERE StreetName LIKE '*" & Me.StreetName & "*'"
Me.listExistingStreets.RowSource = strSQL

Open in new window

0
 

Author Comment

by:wlwebb
ID: 38748102
For AddressStreetID it is an Autonumber field.  I use the ID # 1 & #2 for predefined StreetName.  AddressStreetID 1's StreetName is {Select Street} and AddressStreetID 2's StreetName  is {Add New Street}.  When they select #2 I have it opening this Form to add a new street name.


Therefore, I want that list box to SELECT all addressStreetID's except ID # 1 & 2
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38748111
strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets " &  _
    "WHERE AddressStreetID > 2 AND StreetName LIKE '*" & Me.StreetName & "*'"

Open in new window

0
 

Author Comment

by:wlwebb
ID: 38748112
Irog....

Copied and pasted yours...... I have 4 Street Names that begin with an "M"  as soon as I try to add another street beginning with an "M" when I type the M my list goes blank........
****EDITED--- FORGET THE GOING BLANK... MY FAT FINGERS HIT A "b" in my code*******

I remarked everything else out of my code in the StreetName_Change() sub........ so the only code in that sub is this .......

What I am expecting (if possible) is as they type each character from M to A to I to N that the list would"
after the 1st character is pressed (the "M") the list would show only StreetNames beginning with "M" then

after the 2nd character is pressed (the "A") the list would show only StreetNames beginning with "MA" then

after the 3rd character is pressed (the "I") the list would show only StreetNames beginning with "MAI" then

after the 4th character is pressed (the "N") the list would show only StreetNames beginning with "MAIN" then
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38748117
You didn't remove this line, right?
Me.listExistingStreets.RowSource = strSQL
0
 

Author Comment

by:wlwebb
ID: 38748121
correct........

Here is a copy paste of my current code......

Private Sub StreetName_Change()
Dim strSQL As String, strSQL2 As String, strSearch As String

'Dim strStreet As String, lngStreetLength As Long
'strStreet = Nz(Me.StreetName, "")
'lngStreetLength = Nz(Len(Me.StreetName), 0)


'strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets" & _
'" WHERE (((AddressStreetID)>2)) AND StreetName LIKE '*" & Me.StreetName & "*'" & _
'" GROUP BY AddressStreetID, StreetName" & _
'" ORDER BY StreetName"

strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets " & _
    "WHERE AddressStreetID > 2 AND StreetName LIKE '*" & Me.StreetName & "*'"
Me.listExistingStreets.RowSource = strSQL


'Me.StreetName = fStripNameIllegal(Me.StreetName)
'Me.StreetName = TestProperCase(Me.StreetName)


End Sub

Open in new window

0
 

Author Comment

by:wlwebb
ID: 38748125
Irog...
PS
The only thing in the table for dta_Streets is
AddressStreetID - PK Autonumber
StreetNames - Text - Required Yes; Allow Zero Length No; Indexed No


For the Control listExistingStreets
Control Source is blank
Row source is blank
Bound Column is 1
Column Count is 2
Column Widths is 0";1.5625"
Column Head is No
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 375 total points
ID: 38748137
Here you go:
Private Sub StreetName_Change()
    Dim strSQL As String
    
    If Len(StreetName.Text) > 0 Then
        strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets " & _
        "WHERE AddressStreetID > 2 AND StreetName LIKE '*" & Me.StreetName.Text & "*'"
    Else
        strSQL = "SELECT AddressStreetID, StreetName FROM NameOfTable"
    End If
    Me.listExistingStreets.RowSource = strSQL
    
End Sub

Open in new window

I needed to use the Text property of StreetName,; otherwise it would look for the previous value.
0
 

Author Comment

by:wlwebb
ID: 38748142
Yep....That did the trick........

Just curious.... when using the "Like" it looks at the whole word.... SO for example if I have a Street named "Wall St" and I want to put a new streetname in called Lexington when I type the first character "L" it will still list Wall St since it has an "L" somewhere in its name..... is there a different "Like" expression that would look left to right based on the number of characters entered.?????????
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 375 total points
ID: 38748144
Remove the first asterisk so that there is no wildcard before the search string.
0
 

Author Comment

by:wlwebb
ID: 38748154
That Got it.......  I'm going to post another followup question for the GroupBy and OrderBy portion... when I add those it doesn't filter right.
0
 

Author Closing Comment

by:wlwebb
ID: 38748157
Thank you for chiming in Mbiz and Irog!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38748377
Glad to help out!

>>> I needed to use the Text property of StreetName,

Lol!  I must have been really tired last night when I posted.  The distinction between Text and Value and when to use each was actually the topic of my article.  It's done right in the sample, but I goofed it up here.  :-)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38748574
@mbizup,
That's a very good article you wrote, Miriam. It cleared up a couple of things for me.  Strange though,  I wanted to answer the question on whether the article was helpful, but I couldn't find where to click.

Ron
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38748755
Hi Ron,

Do you see yes/no buttons?

I think they are down at the bottom of the articles.   If they are not there I think we've got a bug.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38748769
Nada on buttons, at least in Chrome browser (mobile and desktop). Definitely a bug.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38749509
Ron,

Do me a favor, and take another look - just to confirm.

This is what it looks like for me in Chrome:

Voting buttons
If you don't see them, it is probably an issue with the way the permissions are set.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38749770
Miriam, I tried it on both Chrome and IE.  Here's what it looks like for me:
screenshot
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38749776
Okay....  that means you've already voted 'yes' on it :-).
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38749816
Hmmm, I don't remember but maybe I did.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now