lookup field

[Guest]

I have an emplyeees table with a "reports to" field which
contains the ID number of the employees supervisor. I have
a combo box on my form which allows you to select a
supervisor, but that box only shows the ID number of the
supervisor, unless you depress the spin button. Then it
opens showing the name of the current supervisor and the
supervisors ID number, plus all the names of supervisors
which might be chosen. I would like a text box just to
show the name of the currently chosen supervisor.

 

[Pat Hartman]

The easiest thing to do is to delete the combo and rebuild
it with the wizard turned on. At some point the wizard
will ask if you want to hide the key field, check this
box. Your form will then just show the supervisor's
name. If you want to show several fields from this table,
you should change the RecordSource of the form to be a
query that joins the main table to the supervisor table.
You can then select what ever columns you need from both
tables. Then you can add additional controls to the form
to hold the supervisor information. It is best to set the
locked properties of these fields to yes to prevent
accidental updating. Now when you choose a supervisor id
from the combo, the name and other fields will
AUTOMATICALLY be populated. No code is required.

 

[miaplacidus]

Nah-uh, won't work.

The key field is the employees IDNO
The "ReportsTo" field is a foreign key in the same table.
this field needs to use the foreign key, shown on the form
as the "Supervisors ID" to look up the same value in the
IDNO column and report back the supervisors name. I have
not been able to get the Wizard to do this.

I'm thinking of a text box where the control source is a
SQL statement that says something like Select Employees!
Name From "Employees" Where IDNO = CurrentRecord!
ReportsToID, but I don't know how to do it.

 

[miaplacidus]

I tried to add a ReportsToName field in the employees
table similar to the one used in the NorthWind Database
but couldn't make it work. I f I use the lookup wizard the
employees tabe is not one of the options. If I create a
query, then the wizard disallows it because the query is
based on the employees table.

I copied the SQL code from the NW database and copied it
into the field descriptions, but could not make that work
either.

 

[John Vinson]

I would like a text box just to
show the name of the currently chosen supervisor.

Two ways:

- use the Combo Box, but change its Column Widths property to set the
width of the ID to 0. Set the combo's properties to Enabled = False,
Locked = True so the user can't drop it down.

- Or, use a Textbox with a control source

=DLookUp("[LastName] & ', ' & [FirstName]", "[Employees]",
"[EmployeeID] = " & SupervisorID)

using, of course, your own field and table names.

 

[miaplacidus]

I can't seem to make the combo box work. How is the data
displayed in the combo box synchronized with the other
data showing on the form? The main portion of the form is
based on the employees table, but the combo box can't be
based on the employees table: it has to be based on a
query that links the emplyees table back to a copy of the
employees table so the ReportsToID and the employees IDNO
are linked. When I do that the form apparently has no idea
that the ReportsToID of interest is the same one displayed
in another field on the form. It appears that the combo
box is OK for selecting data, but not displaying it on a
lookup basis.

-----Original Message-----

I would like a text box just to
show the name of the currently chosen supervisor.

Two ways:

- use the Combo Box, but change its Column Widths property to set the
width of the ID to 0. Set the combo's properties to Enabled = False,
Locked = True so the user can't drop it down.

- Or, use a Textbox with a control source

=DLookUp("[LastName] & ', ' & [FirstName]", "[Employees]",
"[EmployeeID] = " & SupervisorID)

using, of course, your own field and table names.


.

 

[Miaplacidus]

The Dlookup worked, but I can only get the Combo box to
display the ReportsToID. Then when you press the spin
button all of the available supervisors appear. I'm I
correct in saying its OK for selection but not
presentation?

-----Original Message-----

I would like a text box just to
show the name of the currently chosen supervisor.

Two ways:

- use the Combo Box, but change its Column Widths property to set the
width of the ID to 0. Set the combo's properties to Enabled = False,
Locked = True so the user can't drop it down.

- Or, use a Textbox with a control source

=DLookUp("[LastName] & ', ' & [FirstName]", "[Employees]",
"[EmployeeID] = " & SupervisorID)

using, of course, your own field and table names.


.

 

[John Vinson]

The Dlookup worked, but I can only get the Combo box to
display the ReportsToID. Then when you press the spin
button all of the available supervisors appear. I'm I
correct in saying its OK for selection but not
presentation?

No, you are not correct; and no, it's not the case that a combo box
can only display the ID!

A Combo Box is normally based on a Query - the "Row Source" property.
This query can have one to ten fields; you would set the Combo's
Column Count property to the number of fields that you want to include
in the combo. In this case your query might contain the ID and the
supervisor's name, and you'ld set Column Count to 2.

The combo's Control Source is the field into which the selected value
will be stored - ReportsToID in this case.

The combo's Bound Column is the value which will be stored into the
Control Source - if the ID is the first column, you'ld set this to 1.

Finally, the ColumnWidths property controls how wide each of the
columns is in the display. If you set the width of the ID field to 0,
by setting ColumnWidths to something like

0;1.25

then it will *store* the ID, but *display* the name from the second
column; what the user sees when the combo is not dropped down is the
contents of the first non-zero width column.

 

[JohnC]

Add a text box with the control source equal to:

(If you have first and last name fields)

=DLookUp('[EMP_LNAME] & ", " &
[EMP_FNAME]',"[tblEmployee]","[EMP_ID_NO]=[Forms]![your_form_name]![cboEMP_I
D_NO]")

(or if you do not have a first name field)

=DLookUp("[EMP_LNAME]","[tblEmployee]","[EMP_ID_NO]=[Forms]![your_form_name]
![cboEMP_ID_NO]")

substitute
the last name field name for EMP_LNAME
the first name field name for EMP_FNAME
the employee table name for tblEmployee
the employee ID field name for EMP_ID_NO
your form name for your_form_name
the name of your combo box for cboEMP_ID_NO

JohnC