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]![NHI
Thanks
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]
aside from using .column(2)
you need to make sure that the Column Count property of the combo box is set to 3
you need to make sure that the Column Count property of the combo box is set to 3
ASKER
Can't use "[Forms]![frmComments]
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Given solutions did not work so I went with my own.
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.
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.
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!YourControlNa
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
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!YourControlNa
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
ASKER
Thanks Nick. That's a good idea as well.
Will you be assigning points?
ASKER
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.
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.
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.
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.
Column nmbers are started from 0