Lookup table problem

[Reggie]

I posted this question to another newsgroup but did not get an answer.
Hopefully, I will get one here.

Yes, I am a novice. I am home for two weeks on Christmas leave so I thought
I would teach myself Access. I am signing a database to retain information
obtained during historical research. I have one main table that I use to
keep track of every new individual I discover in my research. The table is
as follows(some fields I will omit for this discussion):

Citizen ID # (autonumber)
Citizen name (test)
Date of Birth (text)
Birth Place (text)
City Name (lookup)
State Name (lookup)

The city and state Lookup fields draw information from tables listing
cities
and states.

I have another table in which I have family data. One of the Lookup fields
is "Citizen Name" and it draws its information from the Citizen Name field
mentioned above. I thought that using Lookup fields for citizen names would
save time and cut down on errors so I have used them in several tables. I
assumed that I would be able to regularly update the Citizen Name table as
new names were discovered while at the same time accessing its Citizen Name
field via Lookup fields in other tables. However, I get an error when I try
to add a new name. Have I sinned? Is the Citizen Name table permanently
locked so that I can no longer add names to it. I really only want to have
to type each name one time as I am talking hundreds of names.

The relationship between the tables are: City Name table -1, to many
(Citizen Name table). I have a copy of the relationship table page if anyone
needs to see it. I do not know if newsgroups can accept attachments. If an
attachment can be sent in I will do so if needed to clarify. I have
simplified this situation to, hopefully, make it easier to understand.

I clearly see why Access drives people nuts!

Thank you for your time. I appreciate any insight you can give.

Reggie

 

[Scott McDaniel]

Your "family data" table should reference the [Citizen ID #] field in the
Citizens table, not the name. In a relational database, you should store the
PK of the foreign table ... how are you trying to add new Citizens? Your
form's combo supports a NotInList event that allows you add records to the
underlying recordsource (there's a pretty good example in Online help
regarding this, IIRC).

 

[Reggie]

Thanks, I will look up the example.

Reggie

Your "family data" table should reference the [Citizen ID #] field in the
Citizens table, not the name. In a relational database, you should store the
PK of the foreign table ... how are you trying to add new Citizens? Your
form's combo supports a NotInList event that allows you add records to the
underlying recordsource (there's a pretty good example in Online help
regarding this, IIRC).

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

I posted this question to another newsgroup but did not get an answer.
Hopefully, I will get one here.

Yes, I am a novice. I am home for two weeks on Christmas leave so I thought
I would teach myself Access. I am signing a database to retain information
obtained during historical research. I have one main table that I use to
keep track of every new individual I discover in my research. The table is
as follows(some fields I will omit for this discussion):

Citizen ID # (autonumber)
Citizen name (test)
Date of Birth (text)
Birth Place (text)
City Name (lookup)
State Name (lookup)

The city and state Lookup fields draw information from tables listing
cities
and states.

I have another table in which I have family data. One of the Lookup fields
is "Citizen Name" and it draws its information from the Citizen Name field
mentioned above. I thought that using Lookup fields for citizen names would
save time and cut down on errors so I have used them in several tables. I
assumed that I would be able to regularly update the Citizen Name table as
new names were discovered while at the same time accessing its Citizen Name
field via Lookup fields in other tables. However, I get an error when I try
to add a new name. Have I sinned? Is the Citizen Name table permanently
locked so that I can no longer add names to it. I really only want to have
to type each name one time as I am talking hundreds of names.

The relationship between the tables are: City Name table -1, to many
(Citizen Name table). I have a copy of the relationship table page if anyone
needs to see it. I do not know if newsgroups can accept attachments. If an
attachment can be sent in I will do so if needed to clarify. I have
simplified this situation to, hopefully, make it easier to understand.

I clearly see why Access drives people nuts!

Thank you for your time. I appreciate any insight you can give.

Reggie