Lookup fields

[Guest]

Hi,

I have a table called Leads. I then have two additional tables called Payments and Sponsorhip. Each of these two tables has a Leads column which has a drop down look up box taking the names from the Leads table created with the lookup wizard. I have created forms for the Payments and Sponsorhip tables, again both having drop down lists for the Lead name. What I found is that if I fill out a record in the Payments or Sponsorhip table picking a name from the Leads drop down list, then for whatever reason in the future have to change the information (such as spelling) of a Lead in the Leads table, the Payment and/or Sponsorhip form does not automatically update this information. For example, if I initially put the Lead in as Samuel in the Leads table, and then filled out a payment record using this information, then went back to change the Lead to Samuels, the payment record would still show Samuel, the old information. Is there a way to update this information when the Payments form is opened? The primary key in the Leads table is the Leads name, as this is always unique and does not repeat. I hope I explained this correctly. Thanks.

Dorothy

 

[Nikos Yannacopoulos]

Dorothy,

In order to have your PK changes automatically cascade to other tables, you
need to go to the relationships view, create relationships between the
tables (if you haven't already) and enforce referencial integrity.

A note on your design for future databases (and this one, if not too late):
it's good practice to use an ID field (probably an autonumber field) as your
PK, and join tables on that one, rather than on a name (text) field. It
makes a database smaller (less actaul data duplication), faster and easier
to maintain.

HTH,
Nikos

Hi,

I have a table called Leads. I then have two additional tables called

Payments and Sponsorhip. Each of these two tables has a Leads column which
has a drop down look up box taking the names from the Leads table created
with the lookup wizard. I have created forms for the Payments and Sponsorhip
tables, again both having drop down lists for the Lead name. What I found is
that if I fill out a record in the Payments or Sponsorhip table picking a
name from the Leads drop down list, then for whatever reason in the future
have to change the information (such as spelling) of a Lead in the Leads
table, the Payment and/or Sponsorhip form does not automatically update this
information. For example, if I initially put the Lead in as Samuel in the
Leads table, and then filled out a payment record using this information,
then went back to change the Lead to Samuels, the payment record would still
show Samuel, the old information. Is there a way to update this information
when the Payments form is opened? The primary key in the Leads table is the
Leads name, as this is always unique and does not repeat. I hope I explained
this correctly. Thanks.

 

[John Vinson]

Hi,

I have a table called Leads. I then have two additional tables called Payments and Sponsorhip. Each of these two tables has a Leads column which has a drop down look up box taking the names from the Leads table created with the lookup wizard. I have created forms for the Payments and Sponsorhip tables, again both having drop down lists for the Lead name. What I found is that if I fill out a record in the Payments or Sponsorhip table picking a name from the Leads drop down list, then for whatever reason in the future have to change the information (such as spelling) of a Lead in the Leads table, the Payment and/or Sponsorhip form does not automatically update this information. For example, if I initially put the Lead in as Samuel in the Leads table, and then filled out a payment record using this information, then went back to change the Lead to Samuels, the payment record would still show Samuel, the old information. Is there a way to update this information when the Payments form is
opened? The primary key in the Leads table is the Leads name, as this is always unique and does not repeat. I hope I explained this correctly. Thanks.

Dorothy

I'll agree with Nikos, but just want to emphasize two things:

- Table Lookups are really disliked by many developers. See
http://www.mvps.org/access/lookupfields.htm for a critique. Combo
Boxes on Forms can be built very readily, without encountering the
disadvantages of the Lookup Wizard.

- Using the Lead's name as a primary key is very risky. Names are NOT
unique; I once worked with Dr. Lawrence David Wise and his colleague,
Dr. Lawrence David Wise. You may well at some point have two "Samuels"
(or two Smiths) both working as leads, and you'll be in trouble. Use a
unique ID instead.