There are two tables. First has about 30 fields. Every entry in this
Quote:
table will be unique. Second table has about 7 fields and is for
reference - strictly a look up type table. I want to use one field,
say FAMILY in the first table to look up any one of the 400 items in
the second table. Based on that one match, I want the form to
automatically show the other 6 fields that coincide with that record
in the second table. Does that make sense?
>
This is all in one form. I want to be able to type in the letters in
field FAMILY and have it seek the appropriate match. Based on that
match, I want the other 6 fields to show up (be visible) but not be
editable. I have no problem getting the form to use the second table
to look up the FAMILY field, but cannot for the life of me figure out
how to display the rest of the data associated with that record in the
second table.
>
Can someone help guide me to get this to work? I've tried using combo
boxes, subforms, adding shared fields to each table, played with
relationships, and nothing works. I managed to get a subform showing
all the table two data in the form, but I couldn't get it to relate to
the field in the first table.
>
To simplfy, say I have two tables. One and Two. First table has five
fields, A,B,C,D,E. Second table has some fields, A,D,M,N,O. These are
all text fields except "A". A would be the autonumber primary field of
each table which has nothing in common. The common field is "D". I
want a combo box in the single form database to look up the value of
"D" in the second table. Based on the chosen value for that record, I
want text fields to show the corresponding values of M, N, and O. How
can this be done?
OK, your mistake here is that you're using a non-primary key field in both
table to relate the two tables. You need to use the primary key field in the
second table (lookup table) to relate to a non-primary key field in the
first table. That non-primary key field in the first table that relates to
the primary key field in the second table is called a "foreign key field" in
the first table.
So, using your above field, field "D" in the first table ("Table1") would
relate to field "A" in the second table ("Table2"). Keep field "A" in the
second table as an autonumber primary key field in that table. But change
field "D" in the first table to be a Number of type Long Integer. Make sure
it has no default value (instead of the "0" that Access puts there by
default).
Now place both tables in your form's underlying query, joining them on
Table1.D = Table2.A. Make sure to make it an outer join from Table1 to
Table2. That way, if Table1 has a Null value in field "D" (user hasn't
entered a value yet), you'll still be able to display records from Table1
Now add the fields from Table1 that you want to use (including field D) to
your query, as well as the six other fields from Table2 you want to use (but
don't add the primary key field, A, since that will be the same as the one
the users are using for lookup).
After all those fields are in your underlying query, simply add them to your
form. Make the controls based on the 6 fields from the second table
read-only by setting their Locked property to True or their Enabled property
to False.
In the combo box based on Table1, field D, use Table2 as its RowSource,
making sure that field A from Table2 corresponds to the bound column of the
combo box. You can make the combo box be as many columns as you'd like, as
long as Field A corresponds to the bound column.
Now use your form. When the user selects a value for field D from the combo
box, the corresponding values from Table2 will "kick in" and the data in the
6 fields will be displayed.
Neil
