Getting values of non-bound Recordsource Columns 1

[gaffonso]

I've got a combo box whose Row Source is a query. That query has 4 fields:

* CardType
* CardTypeID
* NumOfPrivates
* NumOfIndependents

The bound column is #2 (CardTypeID), it's the foreign key for this field. The only column shown to the end-user as a pop-up menu choice is the CardType (it's a textual description).

When an end-user makes a choice, the ControlSource field value is set to that of the bound column (Access copies the value automatically from the Row Source bound column to the Control Source field). What I need to do is retrieve additional values from the Row Source query, not just the one for the bound column, and copy those to other fields in the form.

More specifically, I need to copy the "NumIndependents" and "NumPrivates" values in the Row Source query described above, into two fields on the local form. But I want to do this in response to a single selection on the main combo box.

Is there an easy way to reference the columns of the query that's being used as a Rowsource? I tried grabbing the ComboBox.RowSource property but that just returns a string with the query name. I need the query itself (and the selected row in that query).

I could do this the hard way and create a new recordset programatically, find the row that matched the value chosen in the combo box, and then grab the corresponding NumOfPrivates, NumOfIndependents values but I'm hoping there's an easier way.

Any ideas?

Thanks!

- Gary

 

[Maquis]

You can reference any column with the following syntax:

me!textbox1 = me!combobox1.column(2)

This line will set the textbox = the value in the 3rd column of your combobox. Columns are zero based, so to reference the 1st column use .column(0) and so on.