how to specify row in combo box?

[jhaganjr]

I have a combo box on a form based on an inner join query, as follows:

cboFindProspect.RowSource = SELECT tblProspects.ProspectID, SalesAttemptID FROM tblProspects INNER JOIN tblSalesAttempts ON tblProspects.ProspectID=tblSalesAttempts.ProspectID;

tblProspects is the one side and tblSalesAttempts is the many side or the table relationship. Thus one prospect could be represented several times in this combo box with a different SalesAttemptID in each row.

I use the combo box to choose the correct prospect to display on screen based on the ProspectID - Column(0). No problem.

I use column(1) in the combo box, SalesAttemptID, to display the selected record in a subform for Sales Attempts. Everything works perfectly except for this ...

If a prospect has multiple related sales attempts, and thus multiple rows in the combo box, regardless of which row is selected from the combo box the Column(1) property always returns the SalesAttemptID of the first row for the same ProspectID.

To clarify ... for example, ProspectID 1 has 4 related SalesAttemptID's - A, B, C, and D - and shows 4 rows in the combo box.

1 A
1 B
1 C
1 D
2 E
etc.

When I select the row D, for example, Column(1) returns A instead of D.

If I change the sorting of the combo box, it always returns the first SalesAttemptID in the list for the same ProspectID.

Any idea how to get it to recognize the SalesAttemtpID Column(1) value of the selected row, instead of the first row for the same ProspectID?

Thanks.
Joe

 

[mndrlion]

To work around this, you will need 3 columns in your combo box. The first column will be a concatenation of the ProspectID and the SalesAttemptID. Hide this column by setting the column widths to 0";1";1".

Then reference column(1) for the ProspectID and column(2) for the SalesAttemptID.

 

[jhaganjr]

That did it.

Thanks a million!