Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-05-12
9
Medium Priority
?
794 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

927 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