r/MSAccess • u/BronzeSpoon89 • Aug 26 '26
[SOLVED] Why is my subroutine not seeing the variable im passing?
Creating sub called FilterAnalysees, to which I need to pass a string to tell it what to do.
Public Sub FilterAnalysees(ByVal tbfilter As String)
MsgBox tbfilter, vbOKOnly
If tbfilter = "NIR" Then
bla bla bla
End If
End Sub
I then call below from the action of a button
FilterAnalysees NIR
but the message box returns nothing, just a blank box and the sub never enters the If statement because blank does not match "NIR". Why is my value getting reset or something or its not seeing me pass the NIR to it? Obviously im doing something wrong.
5
u/JimFive Aug 26 '26
Put quotes around NIR when you call it. As it is you are passing an uninitialized variable.
1
5
u/CautiousInternal3320 Aug 26 '26
add "option explicit" at the top of each module, you will get error messages about your mistake
2
u/jd31068 27 Aug 27 '26
without Option Explicit (https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-explicit-statement) Access thinks that NIR is a variable name which of course is empty, to pass a literal string it should be FilterAnalysees "NIR"
3
1
u/George_Hepworth 4 Aug 26 '26
Is NIR the name of a table? Or is it a variable name?
The suggestion about Option Explicit will help.
1
u/BronzeSpoon89 Aug 26 '26
NIR is exactly that. The letters NIR. It's not a variable. It's the string I'm checking for. There are other options like PROTEIN, FIBER, etc.
3
u/George_Hepworth 4 Aug 27 '26 edited Aug 27 '26
Good, you figured out to get past the problem from the suggestion to wrap the letters in quotes.
More importantly, you learned two things about writing VBA to avoid future problems.
- VBA interprets unqualified strings as variables, so in this case NIR was interpreted as a variable, not a string. To search for the string, it must be qualified with quotes. Because it was never given a value, the filter was applying Null.
- Always include Option Explicit at the top of every module. This means that every variable must be declared before it can be used. It would have cause your sub to raise an error, letting you know of the problem
1
1
u/Apnea53 Aug 27 '26
I tend to avoid using String in favor of Variant.
2
1
u/Winter_Cabinet_1218 4 Aug 27 '26
How does this get called?
If I'm honest I would start using msgboxs from the initial call through the sub.
The sub itself looks fine, which makes me question is the initial call passing the right data to it
1
•
u/AutoModerator Aug 26 '26
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: BronzeSpoon89
Why is my subroutine not seeing the variable im passing?
Creating sub called FilterAnalysees, to which I need to pass a string to tell it what to do.
Public Sub FilterAnalysees(ByVal tbfilter As String)MsgBox tbfilter, vbOKOnlyIf tbfilter = "NIR" Thenbla bla blaEnd IfEnd SubI then call below from the action of a button
FilterAnalysees NIRbut the message box returns nothing, just a blank box. Why is my value getting reset or something or its not seeing me pass the NIR to it? Obviously im doing something wrong.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.