Primary Keys & Forms

[jdickerson]

All,

I have a table set up for New Employee Information. This table has an auto
number primary key field(which will become the Employee ID). I have a second
table for Employee Training which includes a foreign key. I have a
relationship set up between these two tables (Employee ID, First Name and
Last Name fields).

I have created a form to enter training information. I would like the
Employee ID to automatically enter once First and Last name are completed.
Is this possible? If so how?

 

[Jeff Boyce]

I may not be visualizing your table structure correctly...

It sounds like you have EmployeeID, FirstName and LastName in BOTH tables
([NewEmployee] & [EmployeeTraining]). If this is your design, why? In a
spreadsheet you might redundantly store data like FirstName and LastName,
but Access is a relational database. You generally DON'T want to force your
users to re-enter first names and last names just to get the appropriate
employeeID. (besides, what happens when you employ two "John Smith's"?!)

Instead, in the form you are using for Training info, add a combobox that
lists employeeID, LastName and FirstName (concatenated). Hide the first
column (employeeID) by making its width = 0. Bind this combobox to the
field in your Training table that holds the employeeID (in this use, it's
considered the foreign key).

Now when you pick an employee, the form stores the employeeID and displays
the concatenated LastName and FirstName.

Or have I misunderstood?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeanette Cunningham]

Hi jdickerson,
You only need a relationship on the field EmployeeID.
New Employee Information has its primary key = EmployeeID.
Employee Training has EmployeeID as a foreign key related to EmployeeID in
New Employee Information..
Remove the First Name and Last Name fields from Employee Training - you only
store this info in one table - New Employee Information.
You use the foreign key EmployeeID to look up the first name and last name
whenever you need them in a query or for a form or report.

To enter training information, use a combo that shows first and last names.
You don't need to store the first and last names in Employee Training but
you can show them on the form.
The combo to select employee needs 2 columns - the first column is employee
id - it is the bound column.
The second column has a calculated field called FullName - which is first
and last name concatenated together.
When a user chooses an employee for training, the full name shows in the
combo, the database stores the employee id from the hidden bound column of
the combo.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Tony Toews [MVP]]

I have a table set up for New Employee Information. This table has an auto
number primary key field(which will become the Employee ID).

Are the users ever going to see this Employee ID? If not that's fine.

But if they are going to see it then HR/Payroll may be the department
which assigns the Employee ID. Also if you start to enter a record
and then hit escape that autonumber is "consumed" and won't be
re-assigned. Thus your HR/Payroll dept may be asking you where they
disappeared to.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Larry Linson]

I would add to the comments the following: you cannot control when the
Autonumber is set. Its purpose is to provide a field on which to join or
link data "behind the scenes", not to create an identity that is displayed
to the user. That is why the behavior that causes occasional gaps in the
sequence were considered of no consequence to the developers of that
feature.

Larry Linson
Microsoft Office Access MVP