Form with fields that autopopulate based on data entered in corresponding fields

[Braesel]

Hello everyone,

Project Background:

I think I have intermediate experience with Access with a reasonable understanding of proper relational database design, the principles of normalization, and the creation of queries via Access or SQL coding and simple forms and reports. However I am a novice at VBA and event driven actions. As a result, I’ve reached a point where I can create a proper relational database but I am unable to utilize it to its full potential and it is time I take that step to the next level so I am seeking some help with that.

I work for a military history museum in Indiana and we have databases storing veteran information that is viewable in touchscreen kiosks. Our current setup is a bit of a mess. We have individual databases for each kiosk (WWI, WWII, Korean War, Vietnam War), as opposed to a single master database that simply has a field for the war or time period of service. Additionally, among the databases the fields are not standardized, but the programs for the kiosks are designed to work for each individual database. The current databases are not normalized and there is a great deal of data cleanup to be performed so as a long term goal I would like to create a master database for all of them, but at this point it is what it is.

In the short term, I would like to create a user friendly master data entry form in a separate, normalized database so that I can start a volunteer program to enter the records. The form would standardize the data collection process and then via queries I will be able to format the data to cater for each individual database and paste or import new records into our existing databases.

My immediate question:

I am trying to create a data entry form that auto-populates or updates fields based on information provided in other fields. I have read many posts on several forums addressing this, and I expect it involves VBA coding and event handling in the form but I have not quite figured out how to achieve it. Here is an example, I have a Veteran table storing information for each veteran. This is the main table used to populate the records in our databases. It stores text fields for information like name, service number, etc., and foreign keys to other tables such as hometown and county, war, campaigns etc. A simplified version of my veteran and hometown and county tables and their relationships is as follows.

Veteran Table	City Table	County Table	City/County Junction Table
Vet_tbl.pk	city_tbl.pk	county_tbl.pk	city_county_tbl.pk
Vet_tbl.name(txt)	city_tbl.city (txt)	county_tbl.county (txt)	city_county_tbl.city = city_tbl fk
Vet_tbl.city = city_tbl fk			city_county_tbl.county = county_tbl fk
Vet_tbl.county = county_tbl fk

I have the city and county tables linked with the junction table such that I am able to query a specific county and output only cities in that county and vice-verse. I want to create a data entry form that collects information to populate the Veteran Table…so vet name, hometown and county of residence. For user friendly data entry purposes, I want the city and county fields on the form to update one another according to what is input to either one. So, should we receive a record for John Doe, Indianapolis…the user would type “John Doe” in the vet name field, “Indianapolis” in the city field, and the county field would automatically fill to “Marion”. Likewise, for John Doe, (city blank), Marion County, the user would type “John Doe” for vet name, and “Marion” in the county field, and the city field would update such that a dropdown list including “only” the cities in Marion county would be displayed. As it is when I create a form, I can type Marion for county, but the city field dropdown list still includes every city in the State…and likewise, type Indianapolis and the County dropdown list still includes all 92 Indiana Counties. Obviously this is not ideal and could lead to data integrity issues.



Any help with this concept would be greatly appreciated because I will be able to use it in several parts of this project…for example, War-specific Campaigns - It should not be possible for a WWI veteran record to select the “Southern Philippines”campaign or a WWII veteran to select the “Meuse-Argonne” campaign. And once again, I am a VBA novice so I apologize in advance if I ask a ton of questions along the way.

On that note, I don’t mind teaching these things to myself (I have self taught myself everything I know so far about Access and proper database design and normalization), I’m just not sure where to start with VBA. So are there any good beginner VBA resources either printed or online that you would suggest?

Thank you
Chase

[June7]

Here is one example of dependent (cascading) comboboxes. http://datapigtechnologies.com/flash...combobox2.html

Here is an example of one method to filter a data set http://datapigtechnologies.com/flash...earchform.html

I think you might find the second technique useful for locating city/county record.