Combo Box text display question

[Lostguy]

Hello.

I have a combo box on the form. On the properties for that box, I
select the query using fields employeeIDpk, FName, LName. The bound
column is 1 and the widths are 0,1,1. The bound column is tied to a
employeeIDfk on another table.

The FName/LName combo (John Smith, Mark Jones, etc.) are in the
dropdown part, but all that shows up in the text box itself is the
employee ID (3, 4, etc.) associated with that combo.

The number is good to store in the underlying table, but I would like
when the user is scrolling through the records via the form, she says
names in that block, rather than just a number.

Thanks for all your help!

VR/Lost

 

[Dorian]

You probably have 'number of columns' property set to 1 instead of 3.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[KenSheridan via AccessMonster.com]

Or you could concatenate the names into one column by setting up the combo
box like this:

ControlSource: employeeIDfk

RowSource: SELECT employeeIDpk, FName & " " & LName FROM Employees ORDER
BY LName, FName;

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

 

[John W. Vinson]

Hello.

I have a combo box on the form. On the properties for that box, I
select the query using fields employeeIDpk, FName, LName. The bound
column is 1 and the widths are 0,1,1. The bound column is tied to a
employeeIDfk on another table.

The FName/LName combo (John Smith, Mark Jones, etc.) are in the
dropdown part, but all that shows up in the text box itself is the
employee ID (3, 4, etc.) associated with that combo.

The number is good to store in the underlying table, but I would like
when the user is scrolling through the records via the form, she says
names in that block, rather than just a number.

Thanks for all your help!

VR/Lost

I'd suggest concatenating the names in the rowsource query of the combo. Try
using a Rowsource of:

SELECT employeeIDpk, [LName] & ", " & [FName]
FROM employees
ORDER BY LName, FName;

Set the combo's properties to:
Column Count 2
Bound Column 1
Column Widths 0;1"

This will show (onscreen when dropped down) names in the form

Aarons, Kathy
Baldwin, Doug
Berry, Janet

If you want firstname-lastname just reverse the order of the fields in the
expression and omit the comma.

 

[Lostguy]

All,

Thanks! John and Ken got it.

When I do the Query Builder on the RowSource, it looks like
employeeidpk in the first column, and then Expr1: LName & ", "&FName
in the second column. Column Count is 2, you hide the first column
with a 0" width, but make it the bound column. Cool! It was that
Expr 1 part that was confusing me.


OK. Same situation. Different combo box.

EmployeeIDpk, StNumber, StName

So, 1 Elm St, 2 Elm St, etc.

I used the same method as above, but I can't get it to ascend by house
number. When you ascend the Expr1, it lines it up like 1 Elm St, 11
Elm St, 12 Elm St.......2 Elm St, etc. So can I use that concatenate
method but ascend based on the StNumber rather than the whole Expr1?
( a part of the column rather than the whole column)

VR/Lost

 

[John W. Vinson]

OK. Same situation. Different combo box.

EmployeeIDpk, StNumber, StName

So, 1 Elm St, 2 Elm St, etc.

I used the same method as above, but I can't get it to ascend by house
number. When you ascend the Expr1, it lines it up like 1 Elm St, 11
Elm St, 12 Elm St.......2 Elm St, etc. So can I use that concatenate
method but ascend based on the StNumber rather than the whole Expr1?
( a part of the column rather than the whole column)

Here's the SQL of a query to do so:

SELECT EmployeeIDpk, [stNumber] & " " & [StName]
FROM Employees
ORDER BY Val([StNumber]), [StName]

Reverse the order of the ORDER BY if you want all the Elm Street addresses
together followed by all the Emmet Avenue...