Update query question

[Silvio]

Hello, I would like to update a record by looking at the value from a form
combo box column(1). I have tried Forms![Lab Works Manager]![cmbTo].column(1)
but it does not work. The bond column(0) has the ID, but in this case I need
to use the actual value from column(1) and not column(0). Any idea how?

Thank you,
Silvio

 

[John Spencer]

Add a hidden control to your form and set its value to
=Forms![Lab Works Manager]![cmbTo].column(1)
Then you can reference that hidden control in the query.

Your other option is to write a VBA function and pass in the name of the form,
the name of the control and the column number. Something like the following
UNTESTED idea.

Public Function fGetColumnValue(strFormName as string _
, StrControlName as String _
, intColumn as Long)

fGetColumnValue= Forms(strFormName).Controls(strControlName).Column(intColumn)

End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hello, I would like to update a record by looking at the value from a form
combo box column(1). I have tried Forms![Lab Works Manager]![cmbTo].column(1)
but it does not work. The bond column(0) has the ID, but in this case I need
to use the actual value from column(1) and not column(0). Any idea how?

Thank you,
Silvio

What are the RowSource, ColumnCount and BoundColumn properties of the combo?
Confusingly, the Bound Column property is 1-based while the Column() property
is zero based - it seems you're aware of that but it's a possible source of
confusion.

 

[Silvio]

Thanks John, your suggestion works great (hiden control).

Add a hidden control to your form and set its value to
=Forms![Lab Works Manager]![cmbTo].column(1)
Then you can reference that hidden control in the query.

Your other option is to write a VBA function and pass in the name of the form,
the name of the control and the column number. Something like the following
UNTESTED idea.

Public Function fGetColumnValue(strFormName as string _
, StrControlName as String _
, intColumn as Long)

fGetColumnValue= Forms(strFormName).Controls(strControlName).Column(intColumn)

End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hello, I would like to update a record by looking at the value from a form
combo box column(1). I have tried Forms![Lab Works Manager]![cmbTo].column(1)
but it does not work. The bond column(0) has the ID, but in this case I need
to use the actual value from column(1) and not column(0). Any idea how?

Thank you,
Silvio

.