Thinking outside the "combo" box.

[jzacharias]

The address are only part of the database. What I am creating is a database to track cases for our investigations division. As the user is entering offender information (if not already in the database) he/she could select an address rather than type it in. I was trying to utilize as many drop down selections as possible to limit the bad and redundant data.



The example below worked except it is terribly slow and I can't seem to fix the gap in the bottom text box.

[orange]

Please post the code you used to create and populate the form.
Getting back to how you intend to use your address tables. I'm going to propose a scenario since you haven't been able to provide details. Review the scenario and adjust as necessary to describe your intended approach.

A user has info about an offender -name, address, phone number.
You need to find out, if
a) the offender is already in your database,
b) is the address provided in your database
c) is the phone number in the database.

If it is a known offender, do the values match the values for that offender in the database.

You can check the OffenderName and OffenderID and see the current value of offenderAddress.
If the offenderAddresses match OK. If not then you must verify and update your offenderAddress in you database.

You can see if the address provided by the offender is already in the database. Similar to the process I demoed. (Again format is critical to doing a match).

If this is a new offender you have to add the info to your database table(s).

If you have a need to see who occupies a specific address (noise complaint, 9-1-1, etc), you can enter the address as per the demo, and then see if there is an offender(s) (of any offense/complaint) at that address.

Similarly you could do the same set up with phone numbers. But, I would think a call to the phone company may be the quickest and most accurate/up to date.

A reference data base - primarily read only and designed and indexed for fast retrieval is one thing.
An operational database with data entry, update, statistics and reporting may be another.
Some of the design and options depends on what users will do with your database. How is it intended to support the investigators?
Will you mostly query the database for info you already have?
Will others be updating the database with offenders, addresses,offenses, witnesses.....?

I've been in situations where we had a corporate, integrated database with Finance and Assistance Programs (grants, loans, contributions etc) where info was changing by the minute. Some users/analysts needed to have a database to do statistics, reporting and querying. The online opertional system data was constantly changing, so reporting would be erratic. What was set up was a series of databases optimized for query and reporting with data at the month end, quarter end, fiscal year and calendar year end. This gave sufficiently timely and consistent data for those purposes.

Do you have a list of scenarios for which your database, especially the addresses, will be used?
If you have scenarios identifying the info/conditions users will seek from the database, then you have
a good handle on your design requirements. And you can test and modify the database/tables/queries etc to optimize the part you consider top priority.

Has the person, who assigned this project to you, identified specifically the types of queries; the conditions; and "sample information for each? These are things you need answers for to help with design. I don't know if you have created some samples or prototypes to show others for comments and critique, but that is an approach that will help you with design and priorities and will improve communications between you and potential users.

Good luck.

NOTE: If you want to send some of your data, then please zip it.

[keviny04]

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.

jzacharias, I looked at your relationship diagram and have to ask you: why are there city, state, and zip data all over the place? In a sound relational database, there should be no duplication of data; every table should be normalized and contain data that only it has and no other tables do. It seems your original question is about populating data from one table to another. In normalized tables, NO SUCH populating is necessary. If you want data from another table, just query the other table and ACCESS, not copy, data from it.

To me, you have more of an issue with table design rather than form design. You need to keep data unique (i.e. non-repeating) both within a table and among the tables. Right now, the only tables you have set up properly are the tblState and tblCity tables. All other tables need to be re-designed in ways that make sense in a sound relational database.

tblState stores a list of state names, each identified with a StateID. The tblCity table stores a list of city names and the associated states (StateID). Thus, both tables are soundly set up.

A zip code identifies a postal area in a particular city and state. So you need to put city and state info in the tblZip table. But since CityID already contains state info (as set up in tblCity), you only need CityID here. Therefore, tblZip should have fields: ZipID, Zip, CityID.

In tblAddress, you don't need StateID nor CityID, because both items are already represented by ZipID (as set up in tblZip). So tblAddress only needs the fields AddressID, StreetAddress, and ZipID.

The offenders table doesn't need CityID, StateID, nor ZipID, because AddressID already represents those items (as set up in tblAddress). Therefore, the jtblOffenderAddress table should only have these fields: OffenderAddressID, OffenderID, AddressID.

[jzacharias]

Thank you for noticing that. I'm still trying to grasp this. I saw an interesting post and saw the table layout for this particular cascading combo box discussion. Not sure it would work in my case because the max limit of the combo box
http://www.utteraccess.com/wiki/inde...ng_Combo_Boxes

[keviny04]

Thank you for noticing that. I'm still trying to grasp this. I saw an interesting post and saw the table layout for this particular cascading combo box discussion. Not sure it would work in my case because the max limit of the combo box
http://www.utteraccess.com/wiki/inde...ng_Combo_Boxes

Consider creating a "search as you type" kind of combobox. When empty, the combobox shows nothing. When the user starts typing in it, with each letter typed, the combobox keeps narrowing down the choices. That way, you are unlikely to be hindered by the 65k limit. Here is my test database with a "search as you type" combobox: SearchAsUTypeTest.zip.

Note that even though the combobox shows an address, its control source is actually AddressID. In other words, the user types in an address; but it is the AddressID value that gets stored in the table. This is what I meant by keeping only unique, non-repeating data in every table. The address data are already in the address table, so you don't store them again in the Offenders table. You store the AddressID instead.

The code is in the combobox's OnChange event. Note that this is fairly primitive code as far as "search as you type" is concerned; it is just to give you an idea. This page has a sample database with much more developed code for the purpose.

[orange]

kevin,

Did you see the links in post 6? The OP has been advised of cascading combos, some related tutorials, the Allen Browne material etc.
I've tried asking what exactly he is trying to do, and have not had a response with requirements or detailed description regarding addresses. I have suggested that if you start with Zip-State-City, you can get State and City directly from the Zipcode. But it seems (guessing on my part) he wants to find by means of Address.
My demo showed using the existing records, sort the complete addresses by street info and you will have street,city, state and zip. But until the OP can describe/identify the requirement, we can only guess.

jzacharias, you have been given advice based on what we understand of your posts. Kevin is sending you to same/similar info, so if we aren't connecting with you, it's time for you to describe to us WHAT exactly you're trying to do, and WHAT exactly is the issue at the moment. We're trying to help/advise, but we need to communicate.
Good luck with your project.

[jzacharias]

I apologize I haven't responded yet. I am looking at the suggestions you've provided and trying to fit time working on this between work and kids. My plan was for the user to select by street address so I'm hoping one of the above examples will do what I want. I will first fix the tables as suggested then get back to you. I apologize I'm not explaining this correctly.

[orange]

jzacharias,

You do not have to apologize. From experience I can assure you that many posters on on various forums and in the same position as you. Many want a database to do a, b and c, but do not have experience or knowledge currently to make it happen.
There are concepts to learn and understand. And it is important to know what you want/need, and communicate same to those trying to help. We don't all learn the same way - some want to read, some want to watch tutorials, some do trial and error...
I suggest you work through the tutorial and watch some videos, then give us more info on what you're wanting to accomplish in plain English. Adapting code you find on the internet/forums is not always in your best interest.
Good luck.