Bound Column with additional view

[Don]

I have a field in which I request the employee ID number. The combo box lists
the emplyee ID, their name, department, etc. When I select the employee of
choice, I have the employee ID as the bound column. Is there any way to
display, not save, any of the other fields available in the combo box? In
other words, I want to select employee John Doe and have his ID of 1234 saved
in the table, but on the form I want to see that 1234 is John Doe by having
his name appear in a non-bound field next to it.

I will need a similar action to occur in the related report.

 

[Don]

FYI, I am using Access 2003

 

[John W. Vinson]

I have a field in which I request the employee ID number. The combo box lists
the emplyee ID, their name, department, etc. When I select the employee of
choice, I have the employee ID as the bound column. Is there any way to
display, not save, any of the other fields available in the combo box? In
other words, I want to select employee John Doe and have his ID of 1234 saved
in the table, but on the form I want to see that 1234 is John Doe by having
his name appear in a non-bound field next to it.

Include all the fields that you want to see in the Combo's RowSource query,
and be sure the combo's ColumnCount property is big enough to include them
all.

Put textboxes on the form with Control Source like

=comboboxname.Column(n)

where n is the *zero based* index of the field you want to see.

I will need a similar action to occur in the related report.

Base the Report, not on your Table, but on a query joining the table to the
employee table. That will give you all of the fields in both tables for
display as needed.


John W. Vinson [MVP]

 

[Don]

John,
Thank you for the assistance. Not being an Access expert, I am afraid that
your response went over my head when you started talking about zero based
index.

In my form, I have a combo box with a control source of 'strAssigned',
wherein the column count is set to 4, the bound column is 1 (strAnalystID),
and and the row source for the bound column is:

SELECT Reviewer.strAnalystID, Reviewer.strMRELastName,
Reviewer.strMREFirstName, Reviewer.strInitials FROM Reviewer ORDER BY
[strMRELastName];

Now, next to that I have attached a text box and given it a name of 'txtMRE'

For the new text box, e.g. txtMRE, I want to display the "strMRELastName"
from the 'Reviewer' table that correlates to the bound column of
'strAnalystID' so that people can see who belongs to the ID number that is
being stored.

In accordance with your instruction, I have assigned the new txtMRE a
control of
=Reviewer.strMRELastName

As you can see, I don't think that I did it correctly as all I get as a
result is #Name?

 

[John W. Vinson]

John,
Thank you for the assistance. Not being an Access expert, I am afraid that
your response went over my head when you started talking about zero based
index.

Sorry... zero based just means that the first column is Column(0), the second
column is Column(1), and so on.

In my form, I have a combo box with a control source of 'strAssigned',

What's the Name property of this combo box? That's what you need.

wherein the column count is set to 4, the bound column is 1 (strAnalystID),
and and the row source for the bound column is:

SELECT Reviewer.strAnalystID, Reviewer.strMRELastName,
Reviewer.strMREFirstName, Reviewer.strInitials FROM Reviewer ORDER BY
[strMRELastName];

Now, next to that I have attached a text box and given it a name of 'txtMRE'

For the new text box, e.g. txtMRE, I want to display the "strMRELastName"
from the 'Reviewer' table that correlates to the bound column of
'strAnalystID' so that people can see who belongs to the ID number that is
being stored.

In accordance with your instruction, I have assigned the new txtMRE a
control of
=Reviewer.strMRELastName

That doesn't in the least resemble what I suggested... <g>

If the combo box is named strAnalystID (which I hope it isn't... it's really
best to rename controls so that you can distinguish the fieldname from the
control name!) then you want

=strAnalystID.Column(1)

As an alternative, perhaps more beneficial for the user, you could base the
combo on a Query

SELECT Reviewer.strAnalystID, Reviewer.strMRELastName & ", " &
Reviewer.strMREFirstName & " " & Reviewer.strInitials FROM Reviewer ORDER BY
strMRELastName, strMREFirstName;

Set the Column Count to 2 (the ID and the concatenated full name) and
ColumnWidths to

0;1.25

to suppress the ID from view and display the full name; the bound column
should still be 1.


John W. Vinson [MVP]