DLookup

  • Thread starter Thread starter Angela M
  • Start date Start date
A

Angela M

Thanks for the help this far!
I know have a pull down list of employee numbers. When the number is
selected I want the first and last name fields to update......
I have used the Dlooup function in the FirstName and LastName control
sources.
The arguments for the expression are; expr,domain and criteria. I understand
that criteria is optional..Can someone explain how to fill the other two. I
just get the good old Name# whichever way I do it.
The employee first name , last name and employee number are in a table
named tblemployee data. The form is being used to input tblworkhistory .

Thanks and sorry to bore everyone!!
 
Angela,

I would recommend that you do not use DLookup for this, but for the
record, it would look like this (and by the way, criteria argument is
not optional if you want it to give the right result!)...
=DLookup("[first name]","tblemployee data","[employee number]=" &
[employee number])

Instead, I would suggest basing your form on a query which includes
both the table that your form in currently based on, plus the employee
data table, suitably joined on the employee number field from both.
Then you can bind the controls on your form to the first name and last
name fileds from the query, and as soon as the data is entered into
your combobox (bound to the employee number), the first name and last
name will automatically be shown.

My second choice would be the suggestion offered previously by Doug
Steele, by making the combobox multi-column, and referencing the
non-bound columns in the first name and last name textboxes.

- Steve Schapel, Microsoft Access MVP
 
Syntax for what you want to do would be something like:

DLookup("FirstName","[Employees]","EmployeeNumber = " & Me.comboEmpNo)

You could change the combobox to have three columns -
EmployeeNumber,FirstName,LastName. Then you could display the FirstName and
LastName on your form by setting the source of the textbox controls to
=comboEmpNo.Column(1)
and
=comboEmpNo.Column(2)

That would display the value of columns 2 and 3 in the respective textboxes.
Combobox and Listbox columns are zero-based when using the column property.
 
I now have a combo box that gives me the Employee First Name, last name and
employee number. When you select the Employee Number and name i.e Fred
Smith #1000 all that remains in the box is the employee number.I there a way
I can have a three field combo box that will update the Form with all the
data no just the Employee number?
John Spencer (MVP) said:
Syntax for what you want to do would be something like:

DLookup("FirstName","[Employees]","EmployeeNumber = " & Me.comboEmpNo)

You could change the combobox to have three columns -
EmployeeNumber,FirstName,LastName. Then you could display the FirstName and
LastName on your form by setting the source of the textbox controls to
=comboEmpNo.Column(1)
and
=comboEmpNo.Column(2)

That would display the value of columns 2 and 3 in the respective textboxes.
Combobox and Listbox columns are zero-based when using the column property.

Angela said:
Thanks for the help this far!
I know have a pull down list of employee numbers. When the number is
selected I want the first and last name fields to update......
I have used the Dlooup function in the FirstName and LastName control
sources.
The arguments for the expression are; expr,domain and criteria. I understand
that criteria is optional..Can someone explain how to fill the other two. I
just get the good old Name# whichever way I do it.
The employee first name , last name and employee number are in a table
named tblemployee data. The form is being used to input tblworkhistory .

Thanks and sorry to bore everyone!!
Click to expand...
Click to expand...
 
Angela,

Yes, there are 3 ways to have a three column combo box that will
update the Form with all the data:

1. Use the method suggested by John Spencer and Doug Steele, namely
set the control source of your First Name and Last Name textboxes on
the form to...
=[EmployeeNumber].[Column](1)
and
=[EmployeeNumber].[Column](2)
Note that this assumes that the name of your combobox is
EmployeeNumber, you will need to change it as applicable if not. It
also assumes that the EmployeeNumber is the first column of the 3 in
the combobox, otherwise you will need to change the column numbers in
the expressions.

2. Set the control source of your First Name and Last Name textboxes
on the form to...
=DLookup("[first name]","tblemployee data","[employee number]=" &
[employee number])
and
=DLookup("[last name]","tblemployee data","[employee number]=" &
[employee number])
Note that if I have misunderstood your earlier posts and the names of
your fields or table are not correctly referenced, you will need to
adjust accordingly. As mentioned earlier, this method will work fine,
but is not recommended.

3. Best and easiest: Make a query which includes the table that
your form in currently based on, plus the employee data table,
suitably joined on the employee number field from both. Use this query
as the recordsource of your form, instead of the table or query you
are presently using. Have your combobox on the form bound to the
Employee Number field from the main table, and the firstname and last
name controls on your form bound to the first name and last
name fields from the query, and as soon as the data is entered into
your combobox, the first name and lastname will automatically be shown
on the form.

- Steve Schapel, Microsoft Access MVP
 
I tried the last option using a query on the main and employee data tables.
When I try to insert a new record or click on the employee field in this new
tForm I get the "Your not allowed" ding.
It seems that because the First and Last name fields are from a diferent
table that the new form is unable to update the employee data table. Make
sense? It does as i type it!!!
Angela M said:
I now have a combo box that gives me the Employee First Name, last name and
employee number. When you select the Employee Number and name i.e Fred
Smith #1000 all that remains in the box is the employee number.I there a way
I can have a three field combo box that will update the Form with all the
data no just the Employee number?
John Spencer (MVP) said:
Syntax for what you want to do would be something like:

DLookup("FirstName","[Employees]","EmployeeNumber = " & Me.comboEmpNo)

You could change the combobox to have three columns -
EmployeeNumber,FirstName,LastName. Then you could display the FirstName and
LastName on your form by setting the source of the textbox controls to
=comboEmpNo.Column(1)
and
=comboEmpNo.Column(2)

That would display the value of columns 2 and 3 in the respective textboxes.
Combobox and Listbox columns are zero-based when using the column property.

Angela said:
Thanks for the help this far!
I know have a pull down list of employee numbers. When the number is
selected I want the first and last name fields to update......
I have used the Dlooup function in the FirstName and LastName control
sources.
The arguments for the expression are; expr,domain and criteria. I understand
that criteria is optional..Can someone explain how to fill the other two. I
just get the good old Name# whichever way I do it.
The employee first name , last name and employee number are in a table
named tblemployee data. The form is being used to input tblworkhistory ..

Thanks and sorry to bore everyone!!
Click to expand...
Click to expand...
Click to expand...
 
Hi Angela,

You are correct that the form should not be updating the employee data
table. However, you should not be getting "dinged", since you are not
trying to update the data in the employee data table.

Can you please check this:
In your employee data table, you have a field which is the unique
identifier/primary key, right? I don't know what this is called, so
let's assume it is EmployeeID. In the other table you also have an
EmployeeID field, or equivalent, right? In your query, the 2 tables
are joined on this field. The EmployeeID field that should be
included in the query should be the one from the "other" table, *not*
the one from the employee data table. Is this what you've got? OK,
so in your query, you have the EmployeeID from the "other" table, and
all the other fields from the "other" table which you want on the
form, plus the FirstName and LastName fields from the employee data
table. Am I right? And then on the form, you have a combobox, which
shows you a list of all employees from the employee data table.
Correct? The Control Source of this combobox is the EmployeeID field
from the query (and ultimately the "other" whatever it is and whatever
it's called table). And the Bound Column of the combobox is the one
which contains the EmployeeID field. How are we doing? So, if this
is your set up, and then you try to enter an employee with the
combobox, the FirstName and LastName textboxes on your form should
update to display the first name and last name of the employee you
selected in the combobox. I can think of no reason why they
wouldn't... this is standard procedure. If it still isn't working,
please post back with some more information about the tables, the
form, and preferably the SQL view of the query you are basing the form
on. We will crack it eventually!

- Steve Schapel, Microsoft Access MVP
 
Hoorah

Got the name to update in the fields ....................but when you enter
a record the first and last names are not entered into the Table. Just blank
for first and last names. All other data is present. Once I fix that
situation I won't bother you any more.
 
Angela,

I am pleased to know that we are apparently making progress! And you
are not being a bother. Although it would be great if you could
provide a bit more specific information about your data structure, as
this would make helping you a lot easier :-)

OK, now reading between the lines, it looks like you have got
firstname and lastname fields in both tables. Is this correct?
Please, what is the nature of the "other" table? In any case, the
firstname and lastname fields should only ever be in one table in your
database, and if I understand you correctly so far, this should be the
employee data table. Other tables should simply refer as required to
the EmployeeID field. That is the key, number one core concept of
relational databases. Therefore, if the picture I have in my head of
what you have got there is correct, the way to deal with the situation
you have asked about is simply to delete the firstname and lastname
fields from the "other" table.

But really, the best thing to do, if you are still not sure, is to
post back with a list of all the fields in both tables, and a
description of what the purpose of the two tables is.

- Steve Schapel, Microsoft Access MVP
 
Back
Top