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

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

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

Select allOpen in new window

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

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

Select allOpen in new window

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

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

Select allOpen in new window

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

You didn't remove this line, right?
Me.listExistingStreets.RowSource = strSQL

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

Select allOpen in new window

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

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

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.

Thank you for chiming in Mbiz and Irog!

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.  :-)

@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

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.

Nada on buttons, at least in Chrome browser (mobile and desktop). Definitely a bug.

Ron,

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

This is what it looks like for me in Chrome:


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

Miriam, I tried it on both Chrome and IE.  Here's what it looks like for me:

Okay....  that means you've already voted 'yes' on it :-).

Hmmm, I don't remember but maybe I did.