Filter by Selection using combo bound column value

[DoktoreNZ]

I need the Filter by Selection feature to create a Filter string based on a
Combo Box using the bound column which is not visible. That is, it needs to
use the field and value from the bound column (a long integer ID field) and
NOT the text is the first visible column.

 

[John Spencer]

Well, the value of the combobox is the value of the bound column.

FORMS![SomeFormName]![SomeComboboxName] will return the value of the bound column

If you are in the form
Me.SomeComboBoxName will returns the value of the bound column

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[DoktoreNZ]

Thanks John but you didn't catch my drift. I can see that my question needs
fleshing out.

I'm using the form's Filter string, which is created by Access when the
"Filter by Selection" command is used on a control, in VBA code. When the
command is used on combo boxes which have a bound column which is hidden (eg,
an ID field) and display an unbound text field, Access creates a Filter
string based on the text field, eg, "((Lookup_ComboWorkplace.[Workplace
Name]="Microsoft"))". This is clever and works fine on the form, enabling the
user to filter on the displayed text (or even part of it in some
circumstances).

However, what I need for the VBA code is a Filter string based on the ID
field similar to what Access creates if the control is a text box or a list
box, eg, "((qryWorkplace.[WorkplaceID]=51))". I've tried making the bound
column the first column but hidden or the second column and visible - the
only thing that works is making it the first column AND making it visible,
which makes the control very unfriendly to the user.

I'm guessing that the "Lookup_" string is built-in functionality without an
option, so I have one or two workarounds in mind. However, it would be great
if there was a simple property setting, registry setting, or adjustment (eg,
like making the bound column the second column but visible ) that enabled the
alternative behaviour.

Cheers,
Doktore.

Well, the value of the combobox is the value of the bound column.

FORMS![SomeFormName]![SomeComboboxName] will return the value of the bound column

If you are in the form
Me.SomeComboBoxName will returns the value of the bound column

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I need the Filter by Selection feature to create a Filter string based on a
Combo Box using the bound column which is not visible. That is, it needs to
use the field and value from the bound column (a long integer ID field) and
NOT the text is the first visible column.

 

[DoktoreNZ]

Thanks for replying but I wanted Access to use the ID in the Filter string -
I can get the value off the control anytime, easy.

try this me.cboCombo.column(0)

that is the first and therfore hidden column in your combo box.

Thanks John but you didn't catch my drift. I can see that my question needs
fleshing out.

I'm using the form's Filter string, which is created by Access when the
"Filter by Selection" command is used on a control, in VBA code. When the
command is used on combo boxes which have a bound column which is hidden (eg,
an ID field) and display an unbound text field, Access creates a Filter
string based on the text field, eg, "((Lookup_ComboWorkplace.[Workplace
Name]="Microsoft"))". This is clever and works fine on the form, enabling the
user to filter on the displayed text (or even part of it in some
circumstances).

However, what I need for the VBA code is a Filter string based on the ID
field similar to what Access creates if the control is a text box or a list
box, eg, "((qryWorkplace.[WorkplaceID]=51))". I've tried making the bound
column the first column but hidden or the second column and visible - the
only thing that works is making it the first column AND making it visible,
which makes the control very unfriendly to the user.

I'm guessing that the "Lookup_" string is built-in functionality without an
option, so I have one or two workarounds in mind. However, it would be great
if there was a simple property setting, registry setting, or adjustment (eg,
like making the bound column the second column but visible ) that enabled the
alternative behaviour.

Cheers,
Doktore.

Well, the value of the combobox is the value of the bound column.

[quoted text clipped - 12 lines]

use the field and value from the bound column (a long integer ID field) and
NOT the text is the first visible column.

 

[DoktoreNZ]

I don't want either of those. I want the Filter string that Access generates
to be based on the bound column, like it is with a List Box.

Thats what I'm saying. You will get the Id and not the value. If the first
field in the combo (the one you set the width to 0) is the ID then cboCombo.
Column(0) will return the Id and not the value. That is assuming you put the
Id in the first field like you should.

Thanks for replying but I wanted Access to use the ID in the Filter string -
I can get the value off the control anytime, easy.

try this me.cboCombo.column(0)

[quoted text clipped - 33 lines]

use the field and value from the bound column (a long integer ID field) and
NOT the text is the first visible column.

 

[DoktoreNZ]

Maybe, but I don't think so. You don't seem to have read or understood the
question properly. If your answer included something about the Filter
property set by Access then you might be addressing the question I asked.

I think you are missing something here. Thats what I'm saying will work. If
the bound ID field is the first field you select when building the combo box,
like you should, then make it not show by setting it's width to zero, you can
then filter on the ID by selecting cboCombo.column(0). the columns in a combo
box are numbered sarting with zero for the first column.

I don't want either of those. I want the Filter string that Access generates
to be based on the bound column, like it is with a List Box.

Thats what I'm saying. You will get the Id and not the value. If the first
field in the combo (the one you set the width to 0) is the ID then cboCombo.

[quoted text clipped - 9 lines]

use the field and value from the bound column (a long integer ID field) and
NOT the text is the first visible column.