Fill-up Data automatically

[binary romel]

I have two tables.
School (sch_ID, sch_name, type, cluster, address, phone)
Teacher (tch_id, name, designation, sex, birth_date, join_date, education,
sch_id, sch_name, type, cluster)
I want when I type sch_id in Teacher table Access fill-up data in sch_name,
type and cluster field from School table automatically.
Pls help.

 

[Jeff Boyce]

One more time ... Access is not a spreadsheet. If you want to get good use
of Access' features/functions, do NOT try to feed it 'sheet data.

If you are already storing the [sch_id] in your [Teacher] table, storing the
[sch_name] is unnecessary and redundant.

Instead, use a query that joins the two tables on their common field
([sch_id]) and return the [sch_name] via your query.

(if you are trying to do this because you want to print out a report, use
the query to 'feed' the report)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ken]

Remove the sch_name and cluster fields from your Teacher table for the
reasons Jeff has explained. On your data input form for the Teacher
table add a combo box set up as follows:

Name: cboSchool

ControlSource: sch_id

RowSource: SELECT sch_id, cluster, sch_name FROM School ORDER BY
sch_name;

BoundColum: 1
ColumnCount: 3
ColumnWidths: 0cm;0cm;8cm

If your units of measurement are imperial rather than metric Access
will automatically convert them. The important thing is that the
first two dimensions are zero to hide the first two columns and that
the third is at least as wide as the combo box.

Now add a text box to the form and set its ControlSource property to:

=cbSchool.Column(1)

When you select a school from the combo box's drop down list the
school name will show in the control, but its underlying value will be
the sch_ID and this will be the value in the table. The text box will
show the cluster by referencing the hidden second column of the combo
box, the Column property being zero-based.

If you don't use forms for inputting data then create them. Never
input data directly into a table in datasheet view. You can use the
form wizard to create basic forms, then amend them in design view.

If you are creating a report in which you wish to show the teacher and
school data base the report on a query which joins the Teacher and
School table on the sch_id fields. You can then return the school
name and cluster fields from the School in the query (and any others
for that matter) along with the desired fields from the Teacher table.

Ken Sheridan
Stafford, England