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

Kevin
Kevin used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
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

Author

Commented:
Yep! Thanks.

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
post the row source of the combo box that you are using.

Author

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?
Top Expert 2016

Commented:
how about the combo box settings?
Top Expert 2016

Commented:
also, can you run the query and export it to excel and upload the excel file.

Author

Commented:
Attached is the excel file, and the combo box looks like this:
combobox.pngQuery.xlsx
Top Expert 2016

Commented:
what is the Bound Column?

Author

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"
Top Expert 2016

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.

Author

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?
Top Expert 2016

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.

Author

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];
Top Expert 2016

Commented:
is the [NAICSCodes].[ID] field an autonumber?
is this the field you want to be in your table Companies?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial