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:
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:
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:
And after companies are selected, the form just shows a CSV of the codes with no descriptions:
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
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:
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:
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:
And after companies are selected, the form just shows a CSV of the codes with no descriptions:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
post the row source of the combo box that you are using.
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?
SELECT NAICSCodes.NAICS, Left$(NAICSCodes.NAICS & " ",7) & NAICSCodes.Description AS Expr1
FROM NAICSCodes
ORDER BY NAICSCodes.Description;
Is that what makes it not work?
how about the combo box settings?
also, can you run the query and export it to excel and upload the excel file.
ASKER
what is the Bound Column?
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"
"You cannot add or change a record because a related record is required in table NAICSCodes"
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.
this problem will also appear if you use the original row source of the combo box.
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?
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?
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.
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.
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]
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]
is the [NAICSCodes].[ID] field an autonumber?
is this the field you want to be in your table Companies?
is this the field you want to be in your table Companies?
ASKER