Avatar of Kevin
Kevin
Flag for United States of America asked on

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
Microsoft Access

Avatar of undefined
Last Comment
Rey Obrero (Capricorn1)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Kevin

ASKER
Yep! Thanks.
Kevin

ASKER
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.
Rey Obrero (Capricorn1)

post the row source of the combo box that you are using.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Kevin

ASKER
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)

how about the combo box settings?
Rey Obrero (Capricorn1)

also, can you run the query and export it to excel and upload the excel file.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Kevin

ASKER
Attached is the excel file, and the combo box looks like this:
combobox.pngQuery.xlsx
Rey Obrero (Capricorn1)

what is the Bound Column?
Kevin

ASKER
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"
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rey Obrero (Capricorn1)

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.
Kevin

ASKER
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)

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Kevin

ASKER
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)

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