Prev: sumif function in Access query, possible?
Next: populating a query
From: Silvio on 26 Apr 2010 14:35
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

From: John Spencer on 26 Apr 2010 15:38
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

Silvio wrote:
> 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
>
From: John W. Vinson on 26 Apr 2010 15:47
On Mon, 26 Apr 2010 11:35:02 -0700, Silvio <Silvio(a)discussions.microsoft.com>
wrote:

>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.
--

John W. Vinson [MVP]
From: Silvio on 26 Apr 2010 21:33
Thanks John, your suggestion works great (hiden control).


"John Spencer" wrote:

> 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
>
> Silvio wrote:
> > 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
> >
> .
>
 | 
Pages: 1
Prev: sumif function in Access query, possible?
Next: populating a query