auto adding of information

I am designing a training database and have created tables of officers with
regimental numbers and names.
I have a form which is working well however i want to be able to put in an
officers regimental and the name of the officer be self populated from the
table into the name flield of the form.

On Thu, 1 Jan 2009 16:29:03 -0800, Andrew
wrote:

I am designing a training database and have created tables of officers with
regimental numbers and names.
I have a form which is working well however i want to be able to put in an
officers regimental and the name of the officer be self populated from the
table into the name flield of the form.

What's the structure of your Tables? The officer's name should exist once and
once only (in the table of officers). That table should have a Primary Key
(which may well be the "regimental number", though I don't know what that
number might be), and should have fields such as LastName, FirstName,
MiddleName, Suffix (e.g. Bailey, Beetle, NULL, Jr.); the name should not be
stored in any other table.

--

John W. Vinson [MVP]

I have two tables at the moment, the main officers table has Regimental
number ( Primary Key) Surname, First Name.

The second table has training completed including Regimental (primary key)
Surname, course completed whch is a dropdown box, completed date and review
date.

Are you saying i should delete the regimental and name from all tables exept
the initial officers details and this will self populate the correct name on
the all forms when a regimental is entered?
"John W. Vinson" wrote:

On Thu, 1 Jan 2009 16:29:03 -0800, Andrew
wrote:

I am designing a training database and have created tables of officers with
regimental numbers and names.
I have a form which is working well however i want to be able to put in an
officers regimental and the name of the officer be self populated from the
table into the name flield of the form.

What's the structure of your Tables? The officer's name should exist once and
once only (in the table of officers). That table should have a Primary Key
(which may well be the "regimental number", though I don't know what that
number might be), and should have fields such as LastName, FirstName,
MiddleName, Suffix (e.g. Bailey, Beetle, NULL, Jr.); the name should not be
stored in any other table.

--

John W. Vinson [MVP]

On Thu, 1 Jan 2009 17:27:14 -0800, Andrew
wrote:

I have two tables at the moment, the main officers table has Regimental
number ( Primary Key) Surname, First Name.

The second table has training completed including Regimental (primary key)
Surname, course completed whch is a dropdown box, completed date and review
date.

I would recommend NOT using "Lookup Fields" in your Tables. See
http://www.mvps.org/access/lookupfields.htm for a critique. They're not
necessary (ever) and can be really confusing.

Are you saying i should delete the regimental and name from all tables exept
the initial officers details and this will self populate the correct name on
the all forms when a regimental is entered?

The other tables should contain only the regimental number (as a foreign key,
a link to the officers table). You can *DISPLAY* the name on any other form by
using a combo box bound to the regimental number (let's call it cboOfficer);
you might base this combo on a query such as

SELECT RegNo, [Surname] & ", " & [First Name] AS Fullname FROM Officers ORDER
BY Surname, [First Name];

This will show (e.g.)

32267 Aarons, Michael
31558 Barnes, Sarah
22601 Carrons, Bob

in the combo, and store the regimental ID in the table.

In a separate textbox on the form you can display the name by using a control
source

=cboOfficer.Column(1)

The Column property is zero based so it will display the second column
(Fullname) from the selected record of the query.
--

John W. Vinson [MVP]