Solved

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

Posted on 2013-05-12
9
739 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

9 Experts available now in Live!

Get 1:1 Help Now