Solved

Access Vb - Filtering a List Control as Data is input in a Control Textbox - Grouping/Ordering List

Posted on 2013-01-06
3
301 Views
Last Modified: 2013-01-06
Hello All

This is a followup question to Original Question on same topic

Irog assisted in fixing the problem after Mbiz had pointed me down the correct path but I was missing a piece still.

The filtering of the list as the user inputs info into a bound textbox control works but the list is sorting based on the ID number.  I need to modify the code to keep the list filtered by the StreetName (text).....

The original code that filters (unsorted) is.....

    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 dta_Streets"
    End If

    Me.listExistingStreets.RowSource = strSQL

Open in new window


Now then I attempted to modify the strSQL to Group and Order the list but it makes the filtering of the list unresponsive....

The code I tried changing it to is:
    If Len(StreetName.Text) > 0 Then
        strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets" & _
        " WHERE AddressStreetID > 2 AND StreetName LIKE " & Me.StreetName & "*'" & _
        " GROUP BY AddressStreetID, StreetName" & _
        " ORDER BY StreetName"
    Else
        strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets" & _
        " GROUP BY AddressStreetID, StreetName" & _
        " ORDER BY StreetName"

    End If
    
    Me.listExistingStreets.RowSource = strSQL

Open in new window


Is my syntax wrong on adding the Group By and Order By ????
0
Comment
Question by:wlwebb
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 38748180
" I need to modify the code to keep the list filtered by the StreetName (text)...."

Do you mean Sorted by streetname.  If so remove both Group By clauses (lines 4 and 8)
0
 

Author Comment

by:wlwebb
ID: 38748184
When I do that the filtering of the list doesn't work......  oops I did mean sorted by StreetName, you are correct.

PS Yes, StreetName is Text
0
 

Author Closing Comment

by:wlwebb
ID: 38748186
Never mind... missed an ' in my code........  uggghhh
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

895 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

14 Experts available now in Live!

Get 1:1 Help Now