Autofill form from linked table

[DMH]

I have a form (linked to a table) that I fill out any time a person enters my
facility. Is there any way to autofill the rest of the person's information
(text boxes in the same form) when I type their name in the corresponding
text box?

 

[Arvin Meyer MVP]

It's not clear why you would want to do this. Are these people already in
your database? If so, picking/typing their name from a combo box along with
their PersonID key, should be sufficient, except perhaps for a date/time
field. If they have data that you want to print, create a report with a
query based upon their data.

If you are just populating the form for display purposes only, include the
additional information in columns in the combo. Then you can fill in other
text boxes by setting them = to the column (minus 1 because it's zero based)
So for instance, their phone number was the 4th column, you'd use a
controlsource in the text box like:

= cboComboBoxName.Column(3)

 

[æŽå»ºå›½]

I have a form (linked to a table) that I fill out any time a person enters
my
facility. Is there any way to autofill the rest of the person's
information
(text boxes in the same form) when I type their name in the corresponding
text box?

 

[Ken Sheridan]

You should not store the name, nor any other attributes of the person in the
table underlying your form. Instead store a unique numerical ID, such as
PersonID. A number should be used because names can be duplicated, even in
quite small workgroups; the other attributes should not be stored because
they are already held in a separate 'People' table, so duplicating them
introduces redundancy, which puts the table at risk of inconsistent data
being entered. The primary key of the People table should also be a PersonID
number, and can conveniently be an autonumber, so if your People table
doesn't already have one just add an autonumber column and it will be
automatically filled with unique values. Note that the foreign key PersonID
column in the table underlying your form must not be an autonumber, though.

To show the other attributes of the person on your form make the form's
RecordSource a query which joins the tables on PersonID and includes all the
columns from the form's underlying table and those from people which you want
to show on the form. Don't include the PersonID column from People though,
only the foreign key PersonID column from the form's underlying table. You
can then include controls in the form bound to the columns from the people
table. Set the Enable property of these controls to false (No) and their
Locked property to True (Yes) to prevent them being updated.

On your form add a combo box bound to the foreign key PersonID column, set
up as follows:

ControlSource: PersonID

RowSource: SELECT PersonID, FirstName & " " & LastName FROM People ORDER
BY LastName, FirstName;

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

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

You can then simply select a person from the list and the controls on the
form bound to the columns from people will show the relevant values for the
selected person.

You can also enter a new person, i.e. one not currently represented in the
People table, by typing their name in the combo box. Code in the combo box's
NotInList event procedure then opens a form to add the new person to the
people table, passing the name you entered into the combo box to the form,
the returning you to the combo box with the new name added after you close
the People form. We can come back to how you do this later if you wish.

Ken Sheridan
Stafford, England