There will be plenty of room for error if users type in the EmployeeID. I
still don't know if you need to add the employee name to a record, or if you
intend to find an employee's record. I also don't know what you mean when
you say you have the three fields "loaded into a form". A description of
the real-world situation behind the database would be a help.
Since I don't know the details, I will make some up. The Employee table is
named tblEmployee. If you are adding an employee to a purchase order
record, the PO table (tblPO) needs an EmployeeID field. Since each employee
can have many POs, there is a one-to-many relationship between the
EmployeeID fields. Note that they do not need to have the same name. That
is something I did for convenience. However, they do need to be the same
data type (as established in table design view), except that if EmployeeID
in tblEmployee is autonumber, EmployeeID in tblPO is Number (Long Integer).
tblEmployeeID
EmployeeID (primary key, or PK)
FirstName
LastName
etc.
tblPO
PO_ID (PK)
EmployeeID (foreign key, or FK)
PO_Date
etc.
Base a form (frmPO) on tblPO, or on a query based on tblPO. Add a combo box
to the form. Click the combo box to select it, and click View > Properties.
What appears is called the property sheet. Click the Data tab, and set the
ControlSource to EmployeeID. Click Row Source, and click the three dots. A
query design grid will appear. Click tblEmployee in the dialog box that
appears. Click Add, then Close. Drag EmployeeID from tblEmployee to the
first column. In the second column you can do something like:
LastFirst: [LastName] & ", " & [FirstName]
Close the design grid. Still on the Data tab of the property sheet, be sure
the Bound Column is 1. Click the Format tab. Set the Column Count to 2,
and the Column Widths to something like 0";1.5". This hides the EmployeeID
column, so that all the user sees in Last, First. Since the Bound Column is
1, the EmployeeID is stored.
If you want to go to the employee record (to edit the information, for
instance), the combo box row source is set up the same way, but the combo
box does not have a control source. Instead, it uses code to go to the
record for the selected person.
If you need more information, you need to be specific about what you need to
do. Note the questions I asked in the first reply, and the additional
information I sought in this reply.
molly said:
Thank you for the response. The form is for an end user. I currently
have
the ID numbers, Last Name and First Name front loaded into the form where
the
end user can select from the drop down list. However, I am trying to
figure
out if there is an easier way for the end user. I thought if it would
automatically populate that there would be less room for error.
BruceM said:
Assuming that the table with First_Name, etc. is the Employee table, do
you
intend to type the number in order to go to that person's Employee
record,
or is the idea that you wish to associate the Employee with, say, a
Purchase
Order? In the first case you are not storing the Employee number. In
the
second, the Employee number is being stored in the Purchase Order record.
In either case you would use just the EmployeeID number, unless there are
unusual circumstances.
Most people prefer to select the name from a list, rather than
maintaining a
separate list of EmployeeID numbers. If you are one of those people, you
could use a combo box (drop-down list) to select the name. If you want
to
proceed exactly as you stated, make sure the First_Name and Last_Name
fields
are in the form's Record Source.
If you clarify whether you are looking up the name or storing the
Employee
information, I can offer a specific suggestion about how to proceed.
molly said:
Is it possible to have some fields filled in automatically as soon as a
certain value has been entered into another field?
For example I would like the [Last_Name] and [First_Name] fields to
automatically fill when the ID_Number is typed in. I have a table with
ID_Number, Last_Name and First_Name data. I just can't figure out how
to
make it happen.
