Thinking outside the "combo" box.

[jzacharias]

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.

[orange]

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

[jzacharias]

I separated the addresses into the following; tblstreetaddress, tblcity, tblstreetaddress and tblcity.

[orange]

Can you post a few records from each of these tables?

Can you also show your relationships window as a jpg?

[jzacharias]

[orange]

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"?

[jzacharias]

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.

[orange]

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?

[jzacharias]

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.

[orange]

So how do you want to enter and find addresses or whatever it is you are doing with these tables and data?

[jzacharias]

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.

[orange]

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

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

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

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;

[jzacharias]

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?

[jzacharias]

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.

[orange]

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.