We help IT Professionals succeed at work.

get reference to listbox column value with an update query?

is anybody aware of how to use a list box column as a criteria in a query?

basically i have a list box based on a query, and the 2nd coloumn (column(1)) i want to use as a criteria....this is so i can update a value in another un-opened table. i have tried the following [forms]![frmOrderData]![lstExtensions.column(1)] but i get invalid use of brackets....so i try [forms]![frmorderdata]![lstextensions].[column(1)] and it doesnt reference it....any help would be appreciated

thanks
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
This would be the correct way to write it:

  [forms]![frmOrderData]![lstExtensions].column(1)

JimD

Author

Commented:
thanks but it is giving me error message "undifined function".........im doing it through design view by the way, not sql
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Set it up with a function call.  Open a module in design view and paste in:

Function  ReturnValue() as variant

   ReturnValue =  forms![frmOrderData]![lstExtensions].column(1)

End Function.

 Save the module.  In your query, put:

 =ReturnValue()

  on the criteria line.  And of course the form needs to be open or you'll get an error.

JimD

Author

Commented:
yep, that works, ur very good.....can i just ask though, using modules, does that slow database performance?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<does that slow database performance>>

  No, you won't notice the difference at all.  Because the call to the function doesn't have any arguments, Access is smart enough to figure out that it needs to call it once for the entire query.  The call to get the value will only take a fraction of a second.

JimD

Author

Commented:
brilliant, thanks alot

Explore More ContentExplore courses, solutions, and other research materials related to this topic.