Get value of ComboBox column into text box without displaying ComboBox

[Christine]

In a form based on a query, I would like to put the
second column of TeamMemberID into an unbound text box.

In tblTeam, I get the value of TeamMemberID as follows:

SELECT tblEmployees.EmployeeID, [LName] & ", " & [FName]
AS TeamMemberID FROM tblEmployees
ORDER BY tblEmployees.LName, tblEmployees.FName;

In other words, instead of displaying the EmployeeID, I
want to see "Doe, John". The point of this is that I
don't want to display the combo box - just the value.

In the query and the form, I've tried
TeamMemberID.Column(2) without success

Can someone advise? thank you!

 

[Allen Browne]

The column property is zero-based, i.e. Column(0) is the first one, and the
2nd one is Column(1)

Also, the first character in the ControlSource of your text box must be the
equal sign. Try:
=TeamMemberID.Column(1)

Presumably you already know that you can hide the first column in the combo
box by setting its Column Widths property to zero.

 

[Christine]

Thank you, Allen. HOwever, I do not want to display the
combo box. The command =TeamMemberID.Column(1) works when
the combox box is on the form, but not when I delete it.

I have the same question/problem when building a query.

Thanks. Hope to hear from you again soon - this Saturday
has been a complete waste trying to figure this out!
Cheers.

 

[Allen Browne]

I misunderstood, and thought you wanted to hide the combo (i.e. set its
Visible property to No).

If you don't want to use a combo, see:
Getting a value from a table: DLookup()
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html

In the context of a query, it would generally be best to include the lookup
table in the query. Use an outer join so that the rows still show that have
no matching entry. Details regarding the outer join in the 2nd part of this
article:
http://members.iinet.net.au/~allenbrowne/casu-02.html

 

[Christine]

Thank you, Allen. I know about DLookup and will give that
a go. But in the meantime, one last question if I may.

In tblTeam I'm doing a lookup that stores that EmployeeID
*AND* their name (LName and FName) to TeamMemberID. The
EmployeeID is actually stored in the table, but if I
click on the field, both the ID and full name shows.

If it is not possible to reference Column 2 (LName and
Fname), in a query or in a form without having to do a
DLookup, what is the point of getting multiple column
information? Is this a waste of space for what I was
trying to achieve?

Many thanks for spending your Saturday helping out here!

 

[Allen Browne]

If you already have the employees FName and LName in the Employee table,
then yes, you should not have it in the TeamMember table as well.

Not only is it a waste of space to have it both places, but you are creating
unnecessary work and problems trying to make sure that the two remain
correctly synchronized.

 

[Christine]

Thank you, Allen.