Prev: Select with Conditions
Next: how do I obtain data for Friday & Saturday on a Monday
From: mbedford1 on 29 Mar 2010 10:28
There are two tables and a form:
tblAssets
tblEmployees
frmAssets

tbleEmployees, among other things, has columns for User FirstName, User
LastName, and EmployeeID.

frmAssets primarily deals with tblAssets but pulls from other locations,
including tblEmployees.

frmAssets has field for EmployeeID, but not employees names.

There are two requirements:
1) When displaying entries in frmAssets, I want to see the employees name
corresponding to the EmployeeID stored for that asset record in tblAssets.

2) When entering new records in frmAssets, I want to be able to select the
appropriate employees name from a drop-down list and have it store the
corresponding EmployeeID value in tblAssets.

Can I do this simply using a query or coding and tblEmployees because it
already has both the EmployeeID and the names?

Or would it be better to build this using a query that concatenated First
and Last names and another update query, pulling from that concatenated name
list and converting names to IDs and IDs to names?

Or is there another way I'm missing altogether?
From: SuzyQ on 7 Apr 2010 12:20
change your employee id box to a combo box if it isn't already. then in the
row source property create a query that has both id and name from the
employee table, put the name first and sort it, set the column count to two,
set the bound property to 2, limit to list will automatically be set to yes
because you are not bound to the first column -

this will store id in the field, but display name and allow you to enter
name instead of id

"mbedford1" wrote:

> There are two tables and a form:
> tblAssets
> tblEmployees
> frmAssets
>
> tbleEmployees, among other things, has columns for User FirstName, User
> LastName, and EmployeeID.
>
> frmAssets primarily deals with tblAssets but pulls from other locations,
> including tblEmployees.
>
> frmAssets has field for EmployeeID, but not employees names.
>
> There are two requirements:
> 1) When displaying entries in frmAssets, I want to see the employees name
> corresponding to the EmployeeID stored for that asset record in tblAssets.
>
> 2) When entering new records in frmAssets, I want to be able to select the
> appropriate employees name from a drop-down list and have it store the
> corresponding EmployeeID value in tblAssets.
>
> Can I do this simply using a query or coding and tblEmployees because it
> already has both the EmployeeID and the names?
>
> Or would it be better to build this using a query that concatenated First
> and Last names and another update query, pulling from that concatenated name
> list and converting names to IDs and IDs to names?
>
> Or is there another way I'm missing altogether?
From: KenSheridan via AccessMonster.com on 7 Apr 2010 17:59
Add a combo box to frmAssets, set up as follows:

ControlSource: EmployeeID

RowSource: SELECT EmployeeID, [User FirstName] & " " & [User LastName]
FROM tblEmployees ORDER BY [User LastName], [User FirstName];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

mbedford1 wrote:
>There are two tables and a form:
>tblAssets
>tblEmployees
>frmAssets
>
>tbleEmployees, among other things, has columns for User FirstName, User
>LastName, and EmployeeID.
>
>frmAssets primarily deals with tblAssets but pulls from other locations,
>including tblEmployees.
>
>frmAssets has field for EmployeeID, but not employees names.
>
>There are two requirements:
>1) When displaying entries in frmAssets, I want to see the employees name
>corresponding to the EmployeeID stored for that asset record in tblAssets.
>
>2) When entering new records in frmAssets, I want to be able to select the
>appropriate employees name from a drop-down list and have it store the
>corresponding EmployeeID value in tblAssets.
>
>Can I do this simply using a query or coding and tblEmployees because it
>already has both the EmployeeID and the names?
>
>Or would it be better to build this using a query that concatenated First
>and Last names and another update query, pulling from that concatenated name
>list and converting names to IDs and IDs to names?
>
>Or is there another way I'm missing altogether?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

 | 
Pages: 1
Prev: Select with Conditions
Next: how do I obtain data for Friday & Saturday on a Monday