Solved

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

Posted on 2013-05-12
9
769 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 77

Expert Comment

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

instead of  
Me.txtNewZip
0
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

831 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