Use DLookUp to Enter Data into a Table

[Sara]

Hello,

I have created a form that is essentially a profile of each employee's
information. Currently the form is pulling the job code and the job title
from what is entered in the table (Employees). My question is, whenever I
have to enter a new employee or change a current employee's job code, I have
to look up the corresponding job title to manually enter it. What I would
like to be able to do is enter the job code and have the job title populate
from a table I have (Job Codes) which has all job codes and their
corresponding job titles (I have a relationship between table Job Codes and
table Employees through the primary key in Job Codes (Job Code) and the Job
Code field in table Employees - a One-to-Many Relationship). When it pulls
the job title, I would like it to enter the information into the "Employees"
table. Can this be done? I've tried to use DLookUp but it always returns
"Error#". I tried the following DLookUp formulas:

=DLookUp("[Job Title]","[Job Codes]","[Job Code]=" &[Forms]![Employee
Management]![Job Code])

=DLookUp(""[Job Title]","[Job Codes]","[Job Code]="
&[Forms]![Employees]![Job Code])

There's somewhere around 400 Job Codes and Titles so it's very time
consuming to have to manually look up titles. Thanks in advance for your
help!

-Sara

 

[vbasean]

You can try implementing the Dlookup on the 'After Update' event of your job
code combobox/textbox

BUT speaking about good database design. This is a perfect example of the
need to 'Normalize'

Your Employee table just needs ONE field 'Job ID' and have that linked to
the Job Codes table

you can run your 'Employee' from from a query that has a join to the Job
Codes table and add those fields to your form.

 

[Sara]

Thank you! I deleted those duplicate fields and did what you said! Works great!

You can try implementing the Dlookup on the 'After Update' event of your job
code combobox/textbox

BUT speaking about good database design. This is a perfect example of the
need to 'Normalize'

Your Employee table just needs ONE field 'Job ID' and have that linked to
the Job Codes table

you can run your 'Employee' from from a query that has a join to the Job
Codes table and add those fields to your form.
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Hello,

I have created a form that is essentially a profile of each employee's
information. Currently the form is pulling the job code and the job title
from what is entered in the table (Employees). My question is, whenever I
have to enter a new employee or change a current employee's job code, I have
to look up the corresponding job title to manually enter it. What I would
like to be able to do is enter the job code and have the job title populate
from a table I have (Job Codes) which has all job codes and their
corresponding job titles (I have a relationship between table Job Codes and
table Employees through the primary key in Job Codes (Job Code) and the Job
Code field in table Employees - a One-to-Many Relationship). When it pulls
the job title, I would like it to enter the information into the "Employees"
table. Can this be done? I've tried to use DLookUp but it always returns
"Error#". I tried the following DLookUp formulas:

=DLookUp("[Job Title]","[Job Codes]","[Job Code]=" &[Forms]![Employee
Management]![Job Code])

=DLookUp(""[Job Title]","[Job Codes]","[Job Code]="
&[Forms]![Employees]![Job Code])

There's somewhere around 400 Job Codes and Titles so it's very time
consuming to have to manually look up titles. Thanks in advance for your
help!

-Sara

 

[Mrs. Ugh]

Sara-
Where are you putting your code? I would put what you have (with minor
changes in the AfterUpdate property of the Job Codes control. It will look
something like this (depending on yoru actual control names).

Private Sub Job_Code_AfterUpdate()
Dim stWhereCond As String
stWhereCond = "[Job Code]=" & Me![Job Code]
Me![Job Title]=DLookUp("[Job Title]","[Job Codes]", stWhereCond)
End Sub

You should probably add a check to make sure the Job Code is not Null to
avoid errors.

Why do you want to store the Job Title in the Employees Table? If you have
the Job Code in the Employees Table and a relationship to the Job Codes
Table, there is normally no need to save the Title in the Employees Table as
well. The only reason I can think of to save the title would be if the
title/code relationship only is true when an employee is entered into the
database - meaning their job title might change (and not their code), but you
don't want the title to change for all employees with the same job code.
Normally, the title would change for all employees with the same code, and if
your relationships are set up properly, all you have to do is change the
title in one place (Job Codes table). Otherwise you have to change each
employee manually!
Jill