automatically fill in related field

[Eric]

I am guessing that this form of yours was originally
designed to use a different table than the Facility
Name/ID# table as its record source. We'll call
this "FormTable". FormTable should include a field
named "Facility Name" that exactly matches the name of
the "Facility Name" field in the Name/ID# table.

Open the form in design view. On the upper left-hand
corner of the form, where the two rulers intersect, is a
black square. Double-click that square. The Form
properties window appears. Click the "ALL" tab. In the
line that says "record source", click the "..." button.
The Query builder appears. Add both FormTable and the
Facility Name/ID# table to the query. Create a
relationship between the "Facility Name" field in
FormTable and "Facility Name" in the Name/ID# table by
dragging the name on top of the other. Double-click the
line that appears between the two tables and select the
option that says "Include all records from FormTable and
only those records from Name/#ID where the two values are
equal." Double-click the asterisk under Formtable to add
all fields to the query. Doubleclick "ID#" field under
the Name/ID# table to add it to the query.

Close the query builder and the Properties window. Right-
click the "Facility ID#" field on the form and open its
properties window. Click the "all" tab. Click on the
pull-down menu under "control source" and you should see
the "Facility ID#" field from the Name/ID# table in that
menu. Select it. Close out the properties window. From
now on, when you type in the Name, the ID number should
automatically appear.

Any problems, e-mail me.
Eric

 

[Donna]

Eric,

Thanks for the information. I tried what you suggested,
but now when I go to the form view, the option to
insert "new Record" is no longer available, so I am
unable to try out the changes.

And sad to say, I created the form from the a larger
table that actually included the two field mentioned
(Facility Name and Facility ID). I then created a
separate table that has two field, Facility Name and
Facility ID. What I am trying to do on the form is have
the person input the facility name and then have the
corresponding ID field automatically populate.

Thanks for you help, I really appreciate it.

Donna