Incorrect cascading combobox VB, help plz

[Chillendw]

So I've been trying to get a "simple" cascading combo box to work but I can't seem to get it correct.

The first combobox lists the fields correctly, but the second combobox comes up blank. There is information in each field within the table (tblFactors).

Basically, I want to be able to pick a FactorName from cboFactorName then the second combobox (cboLotNumber) would list the LotNumbers associated with that FactorName. It's pulling the data from the same table (tblFactors) which has both fields (FactorName & LotNumber) in it.

I've attached pics with my code and the results.

Thanks in advance.

 

[pbaldy]

Is the bound column of the first combo an ID field rather than the name?

 

[Chillendw]

Is the bound column of the first combo an ID field rather than the name?

Thanks for your response pbaldy.

It has the FactorName only. I had tried to select 2 fields (FactorName & FactorID) since it's going into a table that requires FactorID and not FactorName. However, it wouldn't GROUP BY since SELECT DISTINCT had too many columns.

It turns out that that's partially why it wasn't working. I previously had the 2nd combobox with 2 columns (0,1) to capture the FactorID in a separate textbox. I changed it to one column and now it works.

Neither the 1st nor the 2nd have a Control Source. The 2nd combobox doesn't have a RowSource because it's in the AfterEvent of the 1st combobox.

However, I still need to have the FactorID bound to the table. Each different LotNumber has a FactorID attached to it, so I was thinking of creating a separate textbox:
ControlSource: FactorID
RowSource: SELECT FactorID FROM tblFactors WHERE txtLotNumber = cboLotNumber

Would that work? Or is there a simpler solution I'm just not seeing?

I've attached some pics to help clarify anything.

Thanks in advance.

 

[pbaldy]

No, that wouldn't work (a textbox doesn't have a row source). If the combo can't be bound, you can copy the selected value with the second method:

http://www.baldyweb.com/Autofill.htm