Need update event to run query for results to populate another field

[fritterdog]

I'm struggling with a problem I originally posted on the forms discussion board (thread702-613570 provides details) but now I am writing vb code...

Essentially I want to use data from Table A, field 1 that is in a combo box X to do a query in Table A to return Table A field 2 that is then used to populate text box Y that corresponds to Table B, field 1.

I think the best way to do this is to have the update event for combo box X run the query and somehow get the value I want to stick in box Y. The problem is I cant figure out how to do this. I want something like

private sub box_X_change

box_Y.value = doCmd.runSql "select A.field_2 from A where A.field_1==box_X.value"

end sub

 

[LarryDeLaruelle]

fritterdog:

If I read your post correctly, I think an easier way to do what you want would be to include the value with the combo box, hid the field by setting it's column width to zero and then reference that field's value in the combo box's After Update event

ControlName = cboName.Column(2)

This assumes you have a PK in the first column of the combo, a display value in the second column and the value you want to use in the third column. Column counts on combo boxes are 0-based so the third column would be referenced as 2.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[SQLBI]

Fritterdog,

I use this a lot and haven't had any problems, you'll obviously need to rename the control values to that on your form.

Paste this code into the "OnChange" event code

Dim rst as ADODB.Recordset
Dim cnn as ADODB.Connection
Dim rstArray as Variant
dim return as variant

rst = New ADODB.Recordset
cnn = CurrentProject.Connection

return = Me.cboComboboxName

rst.Open "SELECT A.Field_2 FROM tblTable WHERE A.Field_1 = return:", cnn

rstArray = rst.GetRows

Me.txtTextBox = rstArray(0, 0)

Regards,


Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk