Getting third column value from a Combobox

Hello Experts,

I am trying to get the value from the third column of a combo box to use a criteria in a query.  Thus far I'm using [Forms]![frmComments]![NHID], but that gives the first column and I need the third.

Thanks
LVL 3
eshurakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
[Forms]![frmComments]![NHID].Column(2)
Column nmbers are started from 0
0
Rey Obrero (Capricorn1)Commented:
aside from using  .column(2)
you need to make sure that the Column Count property of the combo box is set to 3
0
eshurakAuthor Commented:
Can't use "[Forms]![frmComments]![NHID].Column(2)" in a query.   Gives error "Undefined function '[Forms]![frmComments]![NHID].Column(2)' in expression."
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

eshurakAuthor Commented:
I don't like it but I'm going to use a dlookup instead.

dlookup("MastFacID","tblFacility","NHID = " & [Forms]![frmComments]![NHID])

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eshurakAuthor Commented:
Given solutions did not work so I went with my own.
0
als315Commented:
You are correct. It is a new bug (feature) in Access 2010.
You can use syntax .Column(2) in VBA, in form it is now .[Column](2)
And it is impossible now to use column in query.
Very bad news.
0
Nick67Commented:
Much more elegant is to use a wrapper function.
Dlookup can be a performance pig.

You can't pass in the column 2 value directly

In a module, add this

Public Function TheWrapper() As Long 'or whatever data type it may be
TheWrapper = Forms!YourFormName!YourControlName.Column(2)
End Function

In the query criteria, you put
=TheWrapper()

and it works quite nicely.
This is also a good technique to pass in Global variables and other things to the query editor

See the sample
Column-Syntax.mdb
0
eshurakAuthor Commented:
Thanks Nick.  That's a good idea as well.
0
Nick67Commented:
Will you be assigning points?
0
eshurakAuthor Commented:
Hi Nick,

Again, thanks for your suggestion, but I'm using the dlookup and not a wrapper.  As much as I'd love to give everyone points, I think it's important for the integrity of EE and the usefulness to those searching for solutions on EE that points are giving to the solutions that are actually used.

Thanks for your understanding.
0
Nick67Commented:
Your call.
You have posted objections and not submissions, which made me think you might be assigning points.
And I myself avoid using DLookup for anything for performance reasons.
Any solution that avoids a DLookup is usually superior in the long run.
YMMV

<points are giving to the solutions that are actually used.>
Since the solution used is usually the best solution presented, that follows.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.