Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-12
9
Medium Priority
?
792 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
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…
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: …

705 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