Value Table

[Andrew]

I'm trying to figure out how to link a field in one table to other tables.
For example say you have the following tables:

Table1
field:Weather_Conditions

Table2
....
field:Weather_Condition
.....

Table3
......
field:Weather_Condition
.....

Tables 2 & 3 have a weather condition field combo box based off of the
values found in Table 1. I figured out how to set up this relationship but
my problem comes into play when I enter a new weather condition value (not
found in Table 1) into combo box in Table 2 or 3. It says that the value I
entered is not found in Table 1. So, how do I set up the relationship so
that if I enter a new weather condition in Table 2 or 3 it gets added to
Table 1?

Putting it in different words.... I want the weather conditions found in
Tables 2 & 3 to drive the values found in Table 1.

Any Ideas???

Thanks,
Andrew

 

[bcap]

You need to program the NotInList event. Examples here:

http://support.microsoft.com/kb/197526/

n.b. the bit regarding the DAO 3.6 object library may not be applicable, it
depends on what version of Access you are using (which you omitted to
mention!)

 

[Klatuu]

If what you are saying is that table 1 will contain a list of possible
weather conditions and you want to use a value from that list in tables 2 and
3, then I would suggest a combo box on the forms where you enter data for 2
and 3.
If the weather condition is a descriptive value "Sunny", "Cloudly",
"Overcast", or something of that nature, then the proper design is to use two
fields in table 1. An Autonumber Long Integer primary key to identify the
condition, and a text field to display the descrition.

Then in 2 and 3, the field should be a long integer numeric field that would
carry the value of the primary key in table 1 for the selected condition.
This is a Foreign Key. When you want to show the descriptive test associated
with table 2 and 3, you use a query that joins table 1 to table 2 or 3 on
table1's primary key, but you include the descriptive field from table 1 in
the query.

Now, back to the combo.

You want a two column combo box based on table 1 with a row source something
like:
SELECT wcID, wcDescription FROM tblWeatherConditions;
The bound column should be 0.
Column Count should be 2.
Column Widths should be 0"; 2" ( The 0" makes the key value hidden. The 2"
can be whatever you need to show the descritption.
The control source should be the field in table 2 or 3 that stores the
related value.

If you want to be able to add new weather conditions at this point in your
application, you would use the Not In List event. First, you have to set the
Limit to List property to Yes (true) for the Not In List event to fire.
Then when a user enteres a new value, you need to ask if they want to add
the new value to the list. If they do, you can open a data entry form that
lets the user add a new record to table 1.