Auto fill information

[Paul]

Hi,
I have a simple form that I use to insert data into a table. On my form, I
have 3 text boxes that have an employee number, first and last names
manually input. I have another table that includes all this information.
What i would like to do is: input the employee number and then have the
first and last names auto fill in. I have been messing with DLookup but to
no avail. Is this possible?
any help would be appreciated.

thanks

 

[Guest]

Why insert the name when it exist in the other table and they both have
employee number?
Use the employee table, employee number left joined to your table where you
are adding records on employee number.

 

[Guest]

In the related table you should not be storing any data from the Employees
table other than the employee number. It introduces redundancy and leaves
the door open to update anomalies as the table is not properly normalized, so
get rid of the first and last name fields from the related table. You can
join the tables in a query as Karl suggests or you can base your form, as
now, on the related table and include a combo bound to the Employee Number
field. The RowSource would be something like this:

SELECT [Employee Number],
([First Name] + " ") & [Last Name] AS FullName
FROM Employees
ORDER BY [Last Name], [First Name];

Set the combo box's BoundColumn property to 1, its ColumnCount to 2 and its
ColumnWidths to 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first column. You'll see the names in the
list and in the control when you make a selection, in the format John Smith,
but the value of the control will be the hidden Employee Number. If you need
to see that on the form too add an unbound text box with a ControlSource of:

=cboEmployeeID

where cboEmployeeID is the name you've given the combo box.

Ken Sheridan
Stafford, England

 

[Paul]

Thanks Ken, This works fine.
After entering my Employee Number the Employee Full Name now appears in the
combo box. But, the Full Name is not writing away to my related table???

Regards
Paul

In the related table you should not be storing any data from the Employees
table other than the employee number. It introduces redundancy and leaves
the door open to update anomalies as the table is not properly normalized,
so
get rid of the first and last name fields from the related table. You can
join the tables in a query as Karl suggests or you can base your form, as
now, on the related table and include a combo bound to the Employee Number
field. The RowSource would be something like this:

SELECT [Employee Number],
([First Name] + " ") & [Last Name] AS FullName
FROM Employees
ORDER BY [Last Name], [First Name];

Set the combo box's BoundColumn property to 1, its ColumnCount to 2 and
its
ColumnWidths to 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first column. You'll see the names in
the
list and in the control when you make a selection, in the format John
Smith,
but the value of the control will be the hidden Employee Number. If you
need
to see that on the form too add an unbound text box with a ControlSource
of:

=cboEmployeeID

where cboEmployeeID is the name you've given the combo box.

Ken Sheridan
Stafford, England

Hi,
I have a simple form that I use to insert data into a table. On my form,
I
have 3 text boxes that have an employee number, first and last names
manually input. I have another table that includes all this information.
What i would like to do is: input the employee number and then have the
first and last names auto fill in. I have been messing with DLookup but
to
no avail. Is this possible?
any help would be appreciated.

thanks

 

[Guest]

Paul:

You are missing the point. You must not store the name in the related
table, only the employee number. The names are stored only in the employees
table. To store the name in both tables is redundancy, which is 'a bad
thing'. Storing just the employee number in the related table means you can
pull in the name at any time from the employees table via the relationship on
the employee number columns.

Ken Sheridan
Stafford, England

Thanks Ken, This works fine.
After entering my Employee Number the Employee Full Name now appears in the
combo box. But, the Full Name is not writing away to my related table???

Regards
Paul

In the related table you should not be storing any data from the Employees
table other than the employee number. It introduces redundancy and leaves
the door open to update anomalies as the table is not properly normalized,
so
get rid of the first and last name fields from the related table. You can
join the tables in a query as Karl suggests or you can base your form, as
now, on the related table and include a combo bound to the Employee Number
field. The RowSource would be something like this:

SELECT [Employee Number],
([First Name] + " ") & [Last Name] AS FullName
FROM Employees
ORDER BY [Last Name], [First Name];

Set the combo box's BoundColumn property to 1, its ColumnCount to 2 and
its
ColumnWidths to 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first column. You'll see the names in
the
list and in the control when you make a selection, in the format John
Smith,
but the value of the control will be the hidden Employee Number. If you
need
to see that on the form too add an unbound text box with a ControlSource
of:

=cboEmployeeID

where cboEmployeeID is the name you've given the combo box.

Ken Sheridan
Stafford, England

Hi,
I have a simple form that I use to insert data into a table. On my form,
I
have 3 text boxes that have an employee number, first and last names
manually input. I have another table that includes all this information.
What i would like to do is: input the employee number and then have the
first and last names auto fill in. I have been messing with DLookup but
to
no avail. Is this possible?
any help would be appreciated.

thanks