combo box to display record won't display text

[M Costumes]

It's been a couple years since I've dabbled in building a database, and I'm working on one that's nearly complete except for this one issue I'm trying to solve. I'm trying to create a combo box on a form based on a table that displays two text fields (while hiding the PK, so a 3-column box) that when selected will open the matching record in the form.

The table the form is based on: tblReps
fields: RepID (autonumber), Season, ProductionCode

I have two separate joined tables listing the choices for Season (tblSesonYear: Season ID; SeasonYear) and ProductionCode (tblProductionCode: ProCodeID; ProductionCode). So in the Reps table, it's storing the numerical PK for Season (SeasonID) & ProductionCode (ProCodeID)--as it should.

Lots of googling, forum searches, and tutorial watching, and nothing I've tried so far will get the combo box to display text, just the numerical IDs. I'm wondering if it has something to do with trying to pull information from multiple tables? ETA: I've tried editing the RowSource in different configurations to try to point to the other table fields but no luck. Any suggestions on how I can get the text to display? I'm self-taught and I know I'm still bumbling around a bit, so I greatly appreciate any assistance! Many thanks!

 

[bob fitz]

In the query design of the combo box, replace the ID columns of Season and Production Code with their respective text fields.

 

[Gasman]

You need to join those other tables on the keys that you use, then use their text fields.

 

[CJ_London]

In the combo, have you set the width of the id column to 0?

 

[M Costumes]

Got back to it after the long weekend. Thank you all for your replies--I double-checked and tried everything you all suggested. I got kind of close but not quite there. I tried writing a new query to pull the RepID, and the two other fields from their respective tables--it will return one text value, but not the other. I got the error that the query was trying to perform both joins at the same time. I've not done a whole lot with SQL, and I know I've seen an example of how to write the query so it looks at one join first and then the other, but I can't find it anywhere in my notes. Here's the query that I wrote that returned the error code:

SELECT Reps.RepID, SeasonYear.SeasonYear, ProductionCodes.ProductionCode, Reps.SeasonYear, Reps.ProductionCode

FROM ProductionCodes LEFT JOIN (SeasonYear LEFT JOIN Reps ON SeasonYear.SeasonID = Reps.SeasonYear) ON ProductionCodes.ProCodeID = Reps.ProductionCode;

And here's a screen shot of my tables relationship:

Any assistance on how to re-write the query so it's order of operations is correct would be greatly appreciated! As would any other suggestions if you see another solution. Many thanks!