How to show multiple fields from a linked table on a form

I have a form that displays Company information.  There are fields in this form (and the table that it is based on) for

Primary Industry,
Secondary Industry, and
Main Customers Industries.

The values for these fields are picked from a list from another table named NAICS codes, that has a code number in one field AND a description in another field.  When selecting either a Company's Primary Industry or Secondary Industry on the form, the pull down list looks great:

Linked table od codes and descriptions
but once the industry is selected from the list, the resulting entry on the form only displays the code field and not also the description field:

Form display of selected industry
QUESTION 1: Is there a way for me to adjust the form so that the user will still get the dropdown list of codes and descriptions, but change the resulting display so that BOTH the code and description show on the form?

Similarly the form box that shows the industries of the companies customers picks from the same list, but MULTIPLE selections are allowed:

listbox allowing multiple selections
And after companies are selected, the form just shows a CSV of the codes with no descriptions:
list of the industries of the company's customers
QUESTION 2: Can I have these results in a list that show all codes and descriptions instead of a csv list of just the codes on 1 line?

(If this is too complicated for me to do (even with EE assistance), an alternate that might be acceptable would be for the selections in the form to be the DESCRIPTION field from the related table instead of the NAICS code.)

Thanks very much for the help,
Kevin
KevinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
<QUESTION 1: Is there a way for me to adjust the form so that the user will still get the dropdown list of codes and descriptions, but change the resulting display so that BOTH the code and description show on the form?>

use a query as Row Source of the combo box, like this

select code, code & " - " & [description] from naics

set the following property of the combo box

Bound Column 1
Column Count   2
Column Width   0; 3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinAuthor Commented:
Yep! Thanks.
KevinAuthor Commented:
Ray,  I thought that worked because it displayed correctly. But when I select a value from the list it doesn't allow me to to do that.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rey Obrero (Capricorn1)Commented:
post the row source of the combo box that you are using.
KevinAuthor Commented:
I modified the expression so that instead of using a dash, I added some spaces and chose a monospace font so that the "columns" all line up:

SELECT NAICSCodes.NAICS, Left$(NAICSCodes.NAICS & "         ",7) & NAICSCodes.Description AS Expr1
FROM NAICSCodes
ORDER BY NAICSCodes.Description;

Is that what makes it not work?
Rey Obrero (Capricorn1)Commented:
how about the combo box settings?
Rey Obrero (Capricorn1)Commented:
also, can you run the query and export it to excel and upload the excel file.
KevinAuthor Commented:
Attached is the excel file, and the combo box looks like this:
combobox.pngQuery.xlsx
Rey Obrero (Capricorn1)Commented:
what is the Bound Column?
KevinAuthor Commented:
Bound column is 1.  It fills yje box fine, but when I try to move on to a new record, the dialog box that pops up says

"You cannot add or change a record because a related record is required in table NAICSCodes"
Rey Obrero (Capricorn1)Commented:
so the problem is not the one that you posted in your original post.

this problem will also appear if you use the original row source of the combo box.
KevinAuthor Commented:
I am not sure that I understand, sorry. when I used to see just a number in the combobox after making my selection (72 in the example), I think that it would save the record.  But once I changedteh rowsource to a query to show the concatenated string, it does  looks good after I pick it, which is what I wanted, but I thought that it would also be able to save the records.

I have this tables value (Primary Record) linked to the table NAICSCodes that has a number code and a description.  Is that part of the problem?
Rey Obrero (Capricorn1)Commented:
are you using a single form or a main/subform combination?
what are the Record Source of the forms?

what is the Control Source of the combo box?

<I have this tables value (Primary Record) linked to the table NAICSCodes that has a number code and a description.  Is that part of the problem? >

yes I think so.
KevinAuthor Commented:
it is a single form with no subforms.
the source of the form is the table Companies

I want the combo box on the form to pick the field "Primary Industry".  In the design of the table Companies, I used the wizard to select the "Primary Industry" from ANOTHER table named NAICSCodes so that I could see the

NAICS Code number, and the
NAICS Code Description.

When I look at the design of the Companies table, the source for the number Primary Industry field is

SELECT [NAICSCodes].[ID], [NAICSCodes].[NAICS], [NAICSCodes].[Description] FROM NAICSCodes ORDER BY [Description];
Rey Obrero (Capricorn1)Commented:
is the [NAICSCodes].[ID] field an autonumber?
is this the field you want to be in your table Companies?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.