Combo box showing ID vs. Name (1 Viewer)

[chrizzis]

Hi,
I have a junction table, a reference table, and the primary table set up:

tbl_mbr
(PK) mbr_ID
first_name
last_name
...

tblJoin_mbrSkills
(PK) mbr_ID
(PK) skill_ID
years_exp

tblRef_skills
(PK) skill_ID
skill_name
skill_description

I connected and forced referential integrity:
tbl_mbr.mbr_ID 1-many tblJoin_mbrSkills.mbr_ID
tblRef_skill_ID 1-many tblJoin_mbrSkills.skill_ID

I created queries for the forms:

-query for the fields for the main form
qryMbrInfo:
SELECT (fields) FROM tbl_mbr;

-query for individual member skills from junction table
qryMbrSkills:
SELECT tblJoin_mbrSkills.mbr_ID, tblJoin_mbrSkills.skill_ID, tblJoin_mbrSkills.years_exp, tblRef_skills.skill_name
FROM tblRef_skills INNER JOIN tblJoin_mbrSkills ON tblRef_skills.skill_ID = tblJoin_mbrSkills.skill_ID;

-query for combo box for adding new skill
qrySkillsInfo:
SELECT skill_ID, skill_name
FROM tblRef_skills;

I created the main form based on qryMbrInfo and added fields. Then I created a subform based on qryMbrSkills in Continuous Forms View and added the combo box in the subform with the Control Source set to skill_ID and Row Source set to qrySkillsInfo. I want the combo box to show the name, but it shows the ID. If I set the control source to skill_name, I get duplicate entries in the reference table.

Thanks in advance,
-C

 

[maxmangion]

Set the rowsource property of your combo box to: Select tablename.skill_ID, tablename.skill_name FROM tablename (replace tablename with the actual name of your table).

Also set the column count property to 2 and make sure to put two sizes in the column width property, the first one being 0 to hide the skill_id .. i.e. the column widths property should be something like 0;2.54

 

[chrizzis]

Thanks!
Worked like a charm.

 

[maxmangion]

you're welcome

 

[chrizzis]

I tried to recreate the successful combo box in the same process as before and now when I open the subform, the highlighted portion in the box is the ID. It also shows the name. The other records show only the name, and when I click somewhere else it displays correctly. It's just that initial navigation to the subform or opening of the subform that it displays the ID of the highlighted record.
Any insights?

 

[chrizzis]

The problem is that I have a field behind it that is active when I open the form. In my other form, I have 4 fields, but only 2 can be tabbed into. I forgot how to do that for this form.
Anyone remember how?

 

[chrizzis]

Nevermind. Figured it out.