Solved

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

Posted on 2013-01-05
22
418 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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