Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31

    Thinking outside the "combo" box.

    I am stumped. I have a reference table with 217,000 street address. My goal was to use a combo box to list the addresses so the user could begin typing the address and it would show up. Unfortunately every thing I have read is the maximum is 65,000 records. I attempted to use a VBA fix for the row source which worked, but because the row source is left blank, the entry isn't saved. At least that's why I think it's not working. I thought of cascading the combo boxes but it would be backwards if I start with State ==> City ==> Street Address. What alternatives are there to make the data entry flow in a natural way?

    The code I used:

    Private Sub cmbMyCombo2_Change()
    Dim strRowSource As String


    strRowSource = strRowSource & "SELECT AddressId, StreetAddress, CityID, City, StateID, State, ZipID, Zip FROM Query2 "
    If Len(Me!cmbMyCombo2.Text) = 1 Then
    strRowSource = strRowSource & " WHERE StreetAddress Like '"
    strRowSource = strRowSource & Me!cmbMyCombo2.Text
    strRowSource = strRowSource & "*'Order By StreetAddress"
    Me!cmbMyCombo2.RowSource = strRowSource
    Me!cmbMyCombo2.Dropdown
    End If
    End Sub

    This method let me begin typing the address and when I selected the address I want, it would automatically populate the City, State and Zip.

    I hope I explained this in a manner someone can understand. I am a novice at this.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Do your 217000 addresses have zips?
    You could consider cascading combos. See these 2 free videos for ideas.

    http://www.datapigtechnologies.com/f...combobox1.html
    http://www.datapigtechnologies.com/flashfiles/combobox2.html


    Such an approach would get you zip, state and city.

    If you start with street address -there are many formats and some may not match your data.

    I think the 65000 records applies to Excel, not Access.
    Good luck

  3. #3
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    I separated the addresses into the following; tblstreetaddress, tblcity, tblstreetaddress and tblcity.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Can you post a few records from each of these tables?

    Can you also show your relationships window as a jpg?

  5. #5
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    Click image for larger version. 

Name:	Zip.jpg 
Views:	31 
Size:	263.0 KB 
ID:	20514Click image for larger version. 

Name:	Address Relationship.jpg 
Views:	31 
Size:	268.4 KB 
ID:	20515Click image for larger version. 

Name:	City.jpg 
Views:	31 
Size:	70.8 KB 
ID:	20516Click image for larger version. 

Name:	State.jpg 
Views:	31 
Size:	296.4 KB 
ID:	20517Click image for larger version. 

Name:	StreetAddress.jpg 
Views:	31 
Size:	212.0 KB 
ID:	20518

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Have you seen these materials from Allen Browne?
    http://allenbrowne.com/AppFindAsUType.html
    http://allenbrowne.com/AppFindAsUType2.html

    For your application, do you have to enter new addresses to your table, or is it all retrieval from your 217000 records?

    In simple terms and point form, what is the process that you want to "create/improve"?

  7. #7
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    I obtained all the address for the county. I felt that since I had all the data it would be more efficient to use a drop down than to type in the address and eliminate duplicates. I imagine as houses are built I could add to the tables.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Yes, it's a reasonable strategy. I'm in Canada and have used Postal Codes to help with record searching etc.
    There are new postal codes added regularly, as well as some edits/adjustments, and deletions. A static table without some linkage to our Postal Service would have been out of date quickly in our application.

    What exactly are you trying to do with the tables and data you have?
    What steps do you see - in plain English?

  9. #9
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    The goal is to track all the cases in out investigations division. We would like to track all offenders, charges, locations where arrest took place, offenders info along with other misc. data. Knowing the users, I want to keep as much to drop down selections as possible.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    So how do you want to enter and find addresses or whatever it is you are doing with these tables and data?

  11. #11
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    I like how I had it working with the above VBA. I would begin typing an address and once selected I had three text boxes that auto populated because I had their control source set to the combo box with different columns. Unfortunately I couldn't find a way to store the values. The other option would be a normal cascading flow of address ==> City ==> State ==> Zip. I just felt that since all the table are related the first option made sense since the user didn't have to know the city or zip code.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Here are some screen shots in my effort to mimic finding an address from a number of addresses.
    I have a sample of 440 addresses with City, Province and PostalCode.
    The addresses are sorted based on the address line in alpha sort sequence.

    Sample records........................................... .......addressrecords.jpg
    Entering first character.......................................en tering_1...
    Entering third character......................................ent ering_122...
    Identifying a record from the list..........................identifying
    Double click on the selected record......................doubleclick....

    This involves a form, 2 textboxes and a listbox.

    This is the OnChange event code for the top textbox text0
    Code:
    Private Sub Text0_Change()
        Me.List2.Requery
        Me.Refresh
        Me.Text0.SelStart = Me.Text0.SelLength  'not sure this is needed, but I had it available
    End Sub
    This is the code for the dblClick on selected record
    Code:
    Private Sub List2_DblClick(Cancel As Integer)
    Me.SelectedAddress =  List2.Column(0) & ", " & List2.Column(1) & ", " &  List2.Column(2) & ", " & List2.Column(3)
    End Sub
    This is the rowsource for the listbox list2
    Code:
    SELECT tbl_Spl_LCL_Std_EstAddressMailing.MailingAddr
    , tbl_Spl_LCL_Std_EstAddressMailing.MailingCity
    , tbl_Spl_LCL_Std_EstAddressMailing.MailingProv
    , tbl_Spl_LCL_Std_EstAddressMailing.MailingPostalZip
    FROM tbl_Spl_LCL_Std_EstAddressMailing
    WHERE (((tbl_Spl_LCL_Std_EstAddressMailing.MailingAddr) Like [forms]![frmAddressLookup].[text0] & "*"))
    ORDER BY tbl_Spl_LCL_Std_EstAddressMailing.MailingAddr;
    Attached Thumbnails Attached Thumbnails AddressRecords.jpg   entering_1_asstartofAddress.jpg   Entering_122_AsStartOfAddress.jpg   IdentifyingTheRecordIWant.jpg   DoubleClickSelectedAndAddToLowerTextBoxWithFormat.jpg  


  13. #13
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    So you are entering the address in the text box then it narrows the results in the list box then populates the full address in the bottom text box?

  14. #14
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    It works, however, It is very slow and there is a large gap between the city and state. I imagine there is no way to speed it up due to the amount of records.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Again, I 'm asking --what exactly do you want? What is the requirement regarding addresses?
    In the example I showed, I took all existing addresses I had for a number of organizations. I set these up as complete addresses -to avoid lookups/queries to resolve city, then state, then zip. These addresses had all fields in records in that table. I did not have to look into additional tables to resolve city or state or zip.
    The approach was that I had a number of known addresses. These included street number, street name,city, state and zip (in your terms).

    If you have a reference table that relates zip, state and city, then by entering a zip you can get a list of states and cities for that zip. Note: Zips/postal codes are not 1 to 1 with states/provinces necessarily. So starting with zip may not be helpful to you. If you have 217000 existing addresses and you want to find 1 and the city, state and zip involved,then organizing your addresses in sequence and using the OnChange
    event of your input field/control, you can reduce the list of "probable addresses" considerably with each input character you enter. At some point you/the operator will identify the address sought and could dblClick it and send the value(address,city,state and zip) to a location(s) of your choice.

    Three issues jump out.
    1-Since your table of addresses is from your data, there is a possibility that the address sought is NOT in your table.
    2-A process(es) to maintain addresses current is needed. Two issues here:
    a)As I mentioned separately, zips change, new ones added, some deleted or merged or separated as time moves on. This could involve info from the US Postal Service or other body that maintains a master zip code file.
    b) In your monitoring process, if you find a new address for an offender, you need a process to update that address in your table(s).

    3-If the addresses in your table have any extra spaces or inconsistent formatting -apt no, unit etc - then those records could be missed in the OnChange and operator viewing process.

    Part of my question is really analysis - what is the requirement? What do you have currently? What is causing issues (speed, delay, inconsistency, extra input or effort....)?

    I realize you are monitoring/tracking offenders, but that is a piece of the application. Offenders can move/change addresses. Offenders could have multiple addresses (last known, previous, previous..) The Zip codes can change also.
    In one online system, we had 60000+ companies offering goods and services in "an online read-only catalog".
    Companies updated their own records and the info was kept as current as practical (6-10 months).
    There were processes to vet/review company supplied info (address, city, prov and postal code) against an up to date Postal Code, Province, City, address file ( a for cost contracted service with Canada Post with quarterly updates to the Postal Code file). When reviewing a company, we would use a weighted combination of Operating Name, Legal Name, registered aliases, Postal Code and phone number to identify potential duplicates.

    Hope this is helpful to you.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  2. Replies: 1
    Last Post: 09-03-2014, 03:27 AM
  3. Replies: 8
    Last Post: 07-15-2014, 05:56 PM
  4. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  5. Replies: 16
    Last Post: 07-22-2011, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums