Question about Combo Box & Bound Column

[ScubaBart]

So, I use a Combo Box based on a custom query to apply a filter to some of my continuous format forms. Works well.

The combo box is set up presently for two columns but only the first column is visible. The first column is the user friendly description and the second column is the Primary Key for the items in the column list.

When I apply the filter, I use column 2 (Column(1) in the VBA code since it starts at zero for coding purposes) and column 2 is my bound column (the combo box is not bound to a Control Source).

Now, say I want to carry that filter value to the next or even previous form. I decided to make a Public Variable, "ApiaryFilter" to store the value of the Combo Box.

Question, since I use Column 2 as the Bound Column on the various forms, do I still need to explicitly call out Column(1) in the code ?

Would it be better or would it make a difference if I change the order of the columns so that the Primary Key was actually in the first column and the descriptive test in the second column and just adjust my column width values accordingly ? (I currently have column 2 at zero width so only the text shows in the drop down)

My current code doesn't seem to be working correctly

ApiaryFilter = Combo_Filter_by_Apiary.Column(1)

ApiaryFilter is the Public Variable and Combo_Filter_By_Apiary is the combo box name.

If I leave off the .column(1) would it automatically select the bound column ?

I probably made this more confusing by trying to over explain it but, well, hopefully it's understandable.

[Minty]

It's far more common ( I would dare to say normal/good practice?) to bind the combo to the first column (Normally a primary key) than the second or subsequent columns, and simply hide the first column.

Referring to the combo in a query will only refer to the bound value, you can't use the column reference..

[Welshgasman]

Yep, I always had the bound column as the first and hidden.
Often more columns were hidden where I could retrieve associated data from them.

[Micron]

you can't use the column reference.

Pretty sure you can if you wrap the reference in Eval(). Not that I disagree on which column should be bound.

[ScubaBart]

Taking the advice and switching the combo box around so the first column is the Primary key and the bound column and simply hide it to show just the text values.

Tried that and it worked very well.

Thanks !!

[Welshgasman]

Taking the advice and switching the combo box around so the first column is the Primary key and the bound column and simply hide it to show just the text values.

Tried that and it worked very well.

Thanks !!

That is just a matter of preference and standardisation?
The bound column is always the value in the combo.

Make sure you have done it to all combos in that case.

[ScubaBart]

That is just a matter of preference and standardisation?
The bound column is always the value in the combo.

Make sure you have done it to all combos in that case.

Yes, going through and doing it to all combos to be consistent (and proper).

[June7]

Pretty sure you can if you wrap the reference in Eval().

Tried this and just get "Undefined function" error when trying to open query in datasheet.

[Micron]

Pretty sure I have done it; would have to find it just to be sure. Will take a while.
Or as per post 5 (?) here https://www.tek-tips.com/threads/ref...-query.911842/

[June7]

Ah, I forgot the quote marks.

[Micron]

Thought we've been here before. Turns out we have
https://www.accessforums.net/showthr...500#post523500

[June7]

LOL, shows how often I use Eval() - never!