Referencing values from another table

  • Thread starter Thread starter Random
  • Start date Start date
R

Random

Hi there,

Here's what I'm trying to do but I have very little coding experience but I
catch on rather quick.

I'm setting up a database which involves a main form linked to a main table.
On the form and table there is a field called "University" and another one
called "State". Each university in only in one particular state and this
info is listed in a table called Institution.

What I would like to achieve is to be able to enter the University from a
list of values (which I have done using a Combo box) and then when I tab
accross into the next field (which is State), I would like the table/form to
automatically look up the state of that particular university and insert it
into the State field.

How can I go about doing this using code? I need to ensure that each of the
fields can be edited in case a new university needs to be entered or the
state for a particular university needs to be different from what is listed
in the University table.

Any help would be appreciated.
 
Hi there,

Here's what I'm trying to do but I have very little coding experience but I
catch on rather quick.

I'm setting up a database which involves a main form linked to a main table.
On the form and table there is a field called "University" and another one
called "State". Each university in only in one particular state and this
info is listed in a table called Institution.

What I would like to achieve is to be able to enter the University from a
list of values (which I have done using a Combo box) and then when I tab
accross into the next field (which is State), I would like the table/form to
automatically look up the state of that particular university and insert it
into the State field.

How can I go about doing this using code? I need to ensure that each of the
fields can be edited in case a new university needs to be entered or the
state for a particular university needs to be different from what is listed
in the University table.

Any help would be appreciated.
Click to expand...

Best way is to include the State along with the University in the
combo box rowsource:

Select TableName.University, TableName.State From TableName Order By
University;

You can hide the State column by setting the Combo Box ColumnWidths
property to
1";0"

Set the Combo's Bound column to 1.
Set the Column Count property to 2.
Set the Control Source to the University field.

Then, to display the state on the form, code the Combo AfterUpdate
event:
Me![StateControl] = Me!ComboName.Column(1)
As the State is associated with just one university, there is no need
to store the State value in any table field. You can delete the State
field from the table. Just store the University.
To add another university, just add it and it's state to the table.
 
Back
Top