Autofill data (1 Viewer)

[sdollen]

I know this question has been asked and answered a million times over. Someone should write a "how to" book! Call it, Access for Idiots (like me!).

Seriously... I've searched and read on this topic and tried many things but, so green at using Access that many of the answers are over my head. I created two tables and two forms which are very simple and hope that someone will show me how to make it do an autofill data on the 2nd table.

tblMember - Member name "Member", District "District
tblContact - Member name "Member" (using a combo box which is easy enough to understand how to do), "District" , "Visit Date", ect, ect....

Would anyone mind showing me on the attachment how to create it so that when I select the Member name in the tblContact, the corresponding District number autofills in the "District" field in tblContact?

I would be SUPER Grateful.

I'll be doing many queries and reports such as creating a table for conferences that members attend and entering their info in that, and then doing queries to generate reports on who we saw at which conferences. I have a decent handle on that...and will do those later. But, the autofill is STUMPING me.

Thanks again!

 

[bradcccs]

You do not need to capture the district details in the contact table. As you are storing the Member City ID, you can always lookup or refer to the district associated with the member city in the TblMember Cities in a query for reporting etc.

ie: Do not store the same data twice.
If a contact occurs in Stillwater, then this is district 2. (looked up from the TblMember Cities).

I am guessing that your extract is only for demo purposes, but you are also storing the city name as a pk instead of MemberID.

If you need further clarification, please repost.

Brad.

 

[sdollen]

Brad - thanks for the quick response!

Yes, the file was only for demo purposes. I'm not sure what "pk" means exactly.

The "MemberID" field was created by Access. Not sure that I really need that field.

I'm trying to learn Access... so, let me write what I probably should do with the tables and tell me if I'm out in left field.

For my purposes, the city or "Member" name is the unique field.

So, should I be building one table "TblMember Cities" with just the city/Member names in it. Then, build a table "TblContacts" with all the info EXCEPT the city/Member name? Then, build a form where I can enter the data and pull fields from the two tables into the form? And, also when I do queries, pull info from the two tables into the query?

I think I'm going out tomorrow and buy "Access for Idiots".... I think Access is a POWERFUL tool if a person takes the time to read and learn it properly.

 

[sdollen]

Okay... now I realize that "pk" is short for Primary Key. DUH! Slapping myself in the head.

 

[bradcccs]

From one Access Idiot to another

You're getting closer..

First, a quick tip (from one who learned the hard way).
** Avoid using spaces in table / form names etc. When you start to use code to manipulate your data, it is much easier without embedded spaces.

So, given that you are in the inital stages of you db, I will use the correct table names

Tbl_MemberCities - Has fields
MemberCityID - Autonumber (PK - Primary Key) *Note I have changed the Field Name here!!
MemberCity - City Name
District - District Number
AnyOtherCityInfo - etc etc


Tbl_Contacts - Has fields
MemberID - Autonumber (PK)
VisitDate - Date of Visit (Good field name - always avoid word Date by itself!!)
Notes - Notes on Visit
MemberCityID - Looked up / linked / references MemberCityID in Tbl_MemberCities
*Member - Member
*VisitedBy - Who visited
*Visit Type - Type of visit

Notes on * marked fields.
To extend this principle further, I would be creating Tables for Member / Visited by (employee / reps etc) and VisitTypes. Then you would lookup / link / reference this info from the relevant tables. This avoids having to retype the same info, and enables you to use Access' power in querying and reporting on data.

I owe a lot of my (limited) knowledge to a member of this forum. Search for posts by Pat Hartman. She is particularly good at describing table structure and normalisation.

Cheers

Brad.