Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-05
22
Medium Priority
?
427 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 500 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

926 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