hotathens
asked on
Using a global variable to filter a query
I set a global variable up and it?s value is set using data from a form. I then wish to filter records in a query using this variable. I have tried putting [var name] on the criteria line but this does not work. Has anyone a suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you could also make a public function in a module that returns the variable and use the function in the criteria:
Public Function GetCurrentID() As Long
GetCurrentID = GlobalID
End Function
SELECT * FROM tblYourTable WHERE ID = GetCurrentID()
Public Function GetCurrentID() As Long
GetCurrentID = GlobalID
End Function
SELECT * FROM tblYourTable WHERE ID = GetCurrentID()
cjswimmer's answer will suit you best. you don't need to use a function or a global variable if you reference the data from your form directly.
dovholuk
dovholuk
create a Public function and set its value to your global variable then reference the Function in your query.
Public Function MyVariable() As Long
MyVariable = GlobalVariableName
End Function
In the criteria section of your query field just put in MyVariable()
If your global variable is of numeric type and it hasn't been initialized, MyVariable() will equal zero.
If your global variable is not numeric and has not been initialized, you will probably get an error
Public Function MyVariable() As Long
MyVariable = GlobalVariableName
End Function
In the criteria section of your query field just put in MyVariable()
If your global variable is of numeric type and it hasn't been initialized, MyVariable() will equal zero.
If your global variable is not numeric and has not been initialized, you will probably get an error
dovholuk,
You are right, but there's an advantage to my approach, and it is that in my proposed way hotathens will be able to use this query anytime, he'll just have to make sure he's got the appropiate values in the global variable(s)before he uses his query.
BTW, cjswimmer (in his second proposition) and Mach1pro are both using the same approach I originaly stated.
HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela
You are right, but there's an advantage to my approach, and it is that in my proposed way hotathens will be able to use this query anytime, he'll just have to make sure he's got the appropiate values in the global variable(s)before he uses his query.
BTW, cjswimmer (in his second proposition) and Mach1pro are both using the same approach I originaly stated.
HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela
If you will be using the query for a control on a form or for a report, you could write code to do it. For example:
Dim sSQL As String
'UserName is the Global Variable
sSQL = "SELECT Report, User " & _
"FROM Table2 " & _
"WHERE (User Like 'all' Or " & _
"User Like '*" & UserName & "*');"
'Set the controls RowSource to the Sequel
cboReport.RowSource = sSQL
'Or you can use everything above but change the
'cboReport.RowSource line to this
Report.RecordSource = sSQL
This is just another way to do what has been stated above.
HTH,
Joe
Dim sSQL As String
'UserName is the Global Variable
sSQL = "SELECT Report, User " & _
"FROM Table2 " & _
"WHERE (User Like 'all' Or " & _
"User Like '*" & UserName & "*');"
'Set the controls RowSource to the Sequel
cboReport.RowSource = sSQL
'Or you can use everything above but change the
'cboReport.RowSource line to this
Report.RecordSource = sSQL
This is just another way to do what has been stated above.
HTH,
Joe
ASKER
Joe
where would i put the code on the form ?
where would i put the code on the form ?
You can put the code in the AfterUpdate event in the control that is used as the criteria. Then the control that needs this as a RowSource will automatically be updated with this criteria from your Global Variable.
HTH,
Joe
HTH,
Joe
any update hotathens?
Thanks a lot hotathens ... but why the "B" grade?
SELECT * FROM tblYourTable WHERE ID = [Forms]![YourFormName]![tx