October 5th, 2011, 10:22 AM
Access - VBA Variables in Stored Query and Display
I'm still pretty new to Access and SQL but I'm slowly getting a better grasp on things and I could use some help.
My project currently is attempting to create a system tracker for hardware using MS Access. There is a report needed which, when a variable is entered, a saved Access query returns the results in a DataSheet view. Any help on answering the question or directions on where to look to find the answer would be greatly appreciated.
I have the saved query working when I hard code the searchable information. I have my Access Form call VBA code to update a global module variable with the text entered on the form.
Question: How can I have the query recognize this variable and begin to search on it?
SELECT .... FROM ....
WHERE log1.name IN (GetSearchVal())
querySearchVal is my global variable and can have multiple values. I am reformatting the entered data before it is saved as the global variable to encapsulate the entries in single quotes and be separated by a comma. Ex. Name1,Name2 would be transformed to 'Name1','Name2'
Public Function GetSearchVal()
' Return the value of the module variable
GetSearchVal = querySearchVal