Look up Name based on ID

  • Thread starter Thread starter Chris hillman
  • Start date Start date
C

Chris hillman

Hey all,

I have 2 tables of infomation
Table 1:
Department ID (Prim Key), Department Name, etc etc

Table 2:
Department ID, Employee Name, etc etc

I have created a form based of table 2. But im wanting to have a drop down
box of the Department Names, and whatever Department Name is selected it puts
in the relative ID into the table.

Im not sure if im making this more complicated that it should be, im fairly
new to access, and really confused how to get this to work.

Cheers
Chris
 
Chris said:
Hey all,

I have 2 tables of infomation
Table 1:
Department ID (Prim Key), Department Name, etc etc

Table 2:
Department ID, Employee Name, etc etc
Click to expand...
There should be an employee ID as the PK
I have created a form based of table 2. But im wanting to have a
drop down box of the Department Names, and whatever Department Name
is selected it puts in the relative ID into the table.

Im not sure if im making this more complicated that it should be, im
fairly new to access, and really confused how to get this to work.
Click to expand...
What are you trying to do?
A form based on a query relating Table 1 and 2 can display the department
name for a given employee.

A form with the department table in a combo box can display all the
employees for a given department in a sub form.

A list box will allow multiple selections and show the results for more than
one department in a subform. (I use two subforms and checkboxes for this in
most cases.)
 
Hi Chris

This is stock-standard combo box stuff.

Add a combo box to your Employees form and set its properties as follows:
Name: cboDepartment
ControlSource: Department ID
RowSourceType: Table/Query
RowSource: Select [Department ID], [Department Name] from [Table 1]
order by [Department Name];
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the left-most ID column)
 
Hey all,

I have 2 tables of infomation
Table 1:
Department ID (Prim Key), Department Name, etc etc

Table 2:
Department ID, Employee Name, etc etc

I have created a form based of table 2. But im wanting to have a drop down
box of the Department Names, and whatever Department Name is selected it puts
in the relative ID into the table.

Im not sure if im making this more complicated that it should be, im fairly
new to access, and really confused how to get this to work.
Click to expand...

It's Very Simple. <g>

Use the Combo Box tool on the Toolbox in form design mode.
Be sure the "magic wand" icon is selected.
Create a combo box based on Table1. The default options should work.

What you want (if you want to follow all the steps, which would be a good
idea) is a combo box with a Row Source (where it gets its information) based
on a Query of table1, including the DepartmentID and the department name
(sorted alphabetically I'd presume). The combo box's Control Source would be
the department ID in Table2 (I presume your form is based on Table2). The
combo''s Column Count property would be 2 (you want it to include both the ID
and the name), and its Column Widths property would be something like

0";1"

to conceal the ID and display the name. The Bound Column would be 1 to store
the departmentID.

That way the computer sees 13, the user sees "Human Resources", and they both
go away happy.
 
Graham,

Thanks heaps for help, works perfect.
Yeah I haven't had any access training so learning sorta as I go, but this
one couldn't figure out for a while.

So simple, but makes a huge diffrence =D

Cheers

Graham Mandeno said:
Hi Chris

This is stock-standard combo box stuff.

Add a combo box to your Employees form and set its properties as follows:
Name: cboDepartment
ControlSource: Department ID
RowSourceType: Table/Query
RowSource: Select [Department ID], [Department Name] from [Table 1]
order by [Department Name];
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the left-most ID column)

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Chris hillman said:
Hey all,

I have 2 tables of infomation
Table 1:
Department ID (Prim Key), Department Name, etc etc

Table 2:
Department ID, Employee Name, etc etc

I have created a form based of table 2. But im wanting to have a drop
down
box of the Department Names, and whatever Department Name is selected it
puts
in the relative ID into the table.

Im not sure if im making this more complicated that it should be, im
fairly
new to access, and really confused how to get this to work.

Cheers
Chris
Click to expand...
Click to expand...
 
Back
Top