• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2254
  • Last Modified:

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
0
mrBrightside
Asked:
mrBrightside
  • 3
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
This would be the correct way to write it:

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

JimD
0
 
mrBrightsideAuthor Commented:
thanks but it is giving me error message "undifined function".........im doing it through design view by the way, not sql
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mrBrightsideAuthor Commented:
yep, that works, ur very good.....can i just ask though, using modules, does that slow database performance?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
0
 
mrBrightsideAuthor Commented:
brilliant, thanks alot
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now