Solved

Access Form - Listbox of Zip Codes filter as Unbound Textbox is filled in

Posted on 2013-05-12
9
755 Views
Last Modified: 2013-05-13
Hello All,
It has been a little while.  Hope everyone doing well.

I have a Form that has 1 unbound Textbox where the user inputs a Zip Code (max 5 digit only).  That form also has a listbox that displays all Zip Codes currently in a table of Zip Codes.  

The Zip Code table has a Primary key field ZipID and the ZipCodeNbr field is Long, Format 00000.

Now then as a user inputs numbers into the unbound textbox I want my listbox to automatically filter the list to all zip codes beginning with how ever many digits the user has input.  IE, if they have entered the first digit of 2, then only Zips beginning with 2 should appear.  As they enter the second digit, say 5 then filter only Zips beginning with 25....

Here is the code I attempted but once I input a number in my unbound textbox my listbox goes empty.......

Private Sub Form_Load()
Dim strSQL As String
    
    strSQL = "SELECT ZipID, ZipCodeNbr FROM sysdta_ZipCodes;"
    Me.listZip.RowSource = strSQL

End Sub

Private Sub txtNewZip_Change()
Dim lngZipLength As Long, lngNewZip As Long
Dim strSQL As String

'lngNewZip = Me.txtNewZip
lngZipLength = Len(Format(Me.txtNewZip, ""))

    
    If lngZipLength > 0 Then
        strSQL = "SELECT ZipID, ZipCodeNbr" & _
        " FROM [sysdta_ZipCodes]" & _
        " WHERE " & Me.txtNewZip & " LIKE " & ZipCodeNbr & "*"
        
        Else
            strSQL = "SELECT ZipID, ZipCodeNbr FROM sysdta_ZipCodes;"
    End If
    
    Me.listZip.RowSource = strSQL

End Sub

Open in new window


Any help?
0
Comment
Question by:wlwebb
  • 5
  • 4
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39159665
This is wrong...

  " WHERE " & Me.txtNewZip & " LIKE " & ZipCodeNbr & "*"

It is the wrong way round.
You would be testing ...
if 1 Like 12345*
whereas I think you mean..
where 1* like 12345

Change the test to..
  " WHERE ZipCodeNbr & ' ' " like Me.txtNewZip &  "*"

This should force ZipCodeNbr to be treated as a string, which is what you need for a LIKE.
0
 

Author Comment

by:wlwebb
ID: 39159727
Thanks. I'll give it a shot when I get back
0
 

Author Comment

by:wlwebb
ID: 39161739
Okay, I changed the code to this........  still doesn't work....
When it hits the lngZipLength even though I've keyed a number in the field the length is saying "Null" when I step through.  Thus it skips over the code to filter.

Private Sub txtNewZip_Change()
Dim lngZipLength As Long
Dim strSQL As String

lngZipLength = Len(Me.txtNewZip)
If lngZipLength > 0 Then
        strSQL = "SELECT ZipID, ZipCodeNbr" & _
        " FROM [sysdta_ZipCodes]" & _
        " WHERE ZipCodeNbr & ''" Like Me.txtNewZip & "*"
        
        Else
            strSQL = "SELECT ZipID, ZipCodeNbr FROM sysdta_ZipCodes;"
    End If
    
    Me.listZip.RowSource = strSQL

End Sub

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 39161811
Try using
 Me.txtNewZip .Text

instead of  
Me.txtNewZip
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: 39161876
That got it to recognize that it's not null..........  But it still gives me an empty list.  

(Just a reminder, the table of ZipCodeNbr is formatted Long Integer ; Format = 00000)
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 39161988
Sorry I didn't look through the code to the end; I stopped after the first problem.

This is what you need..

Private Sub txtNewZip_Change()
Dim lngZipLength As Long
Dim strSQL As String

lngZipLength = Len(Me.txtNewZip.Text)
If lngZipLength > 0 Then
        strSQL = "SELECT ZipID, ZipCodeNbr" & _
        " FROM [sysdta_ZipCodes]" & _
        " WHERE ZipCodeNbr & '' Like '" & Me.txtNewZip.Text & "*'"
       
        Else
            strSQL = "SELECT ZipID, ZipCodeNbr FROM sysdta_ZipCodes;"
    End If
   
    Me.Listzip.RowSource = strSQL

End Sub
0
 

Author Comment

by:wlwebb
ID: 39162042
VERY CLOSE.......

When I type 2 it not only lists zip codes that begin with a 2 but also ones that begin 02...  I presume that is because even though I have it formatted 00000 that it still see say zip code 02341 is sees it as 2341......

Any way to fix?  or do I just have to live with it?
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 39162281
Setting a format in the format property does not affect the value that is stored , only how the value is displayed.
(And you cannot store a number with leading zeros anyway, only a text field can hold leading zeros).

You can change the test to...

      " WHERE format(ZipCodeNbr, "00000")  Like '" & Me.txtNewZip.Text & "*'"
0
 

Author Closing Comment

by:wlwebb
ID: 39162461
PERFECT... Thank you!!!!  Wish I could award more points.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

864 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