Variable not keeping value

[mrblonde]

I posted this on the General board, but I haven't gotten a response today, I just realized there was a Forum for VBA code...


This is for a Command button that apply's a filter to my records. Finished is a checkbox. blnFilter is a boolean. I can't get the Filter to turn off. The form comes up and it's unfiltered and the caption reads "Turn Filter on" I click, and the filter goes into effect and the caption changes to "Turn Filter Off" However, if I click it again the filter stays on and the caption is still "Turn filter off" Anyone see the error?


Private Sub Command42_Click()
If blnFilter = False Then
blnFilter = True
Command42.Caption = "Turn Filter Off"
Me.Filter = "[Finished] = False"
Me.FilterOn = True
Me.Requery
Else
Command42.Caption = "Apply Filter"
blnFilter = False
Me.FilterOn = False
Me.Requery
End If


On Error GoTo Err_Command42_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub





RickSpr (MIS) Apr 30, 2001
Have you Dim'ed blnFilter anywhere, or are you using implicit definition?

If you haven't provided a Dim for it, it's being implicitly defined within your Command42_Click procedure, which is where it's used. But that won't work for you, because variables defined within a procedure are destroyed when the procedure exits (unless they are declared with the Static keyword). When the command button is clicked again, blnFilter will be recreated with a False value.

If this is the problem, the solution is to explicitly Dim blnFilter As Boolean in the Declarations section of the module.

Learn from your mistakes. Always Dim your variables, and give them specific types whenever possible. Better yet, go to Tools>Options, click the Module tab, and check the Require Variable Declaration box. Practicing this discipline forces you to understand what is happening, which makes it much easier to debug this kind of problem.
Rick Sprague


Mrblonde (TechnicalUser) Apr 30, 2001
This has been in my module.

Option Explicit
Dim blnFilter As Boolean


kathryn (Programmer) Apr 30, 2001
Do you know how to use breakpoints to see what the code is doing? I would recommend setting a breakpoint on the first line of executable code: "If blnFilter = False Then" and then step through the code and use the command window to check on values.
Kathryn


PaulF (TechnicalUser) Apr 30, 2001
try resetting the Filter to Nothing
Me.Filter = ""
Me.FilterOn = False

PaulF


mrblonde (TechnicalUser) May 1, 2001
I've stepped through the code ( a new experience for me - thanks kathryn) and I also added a variable watch to blnFilter. The blnFilter variable changes from True to <Out of Context> as soon as it hits the Exit Sub. Also the default value when I click the Button is Empty. I have the blnFilter Variable declared as Option Explicit. Why is it not retaining it's value, and what should I do?

Also,
I'd like to thank everyone who takes they're time to respond. I have learned so much in the last couple of weeks.

Mat

 

[Rohdem]

Try this:

Instead of using a boolean to keep track of whether the filter is on, use the caption on the button.

Private Sub Command42_Click()
If me.command42.caption = &quot;Apply Filter&quot; Then
Command42.Caption = &quot;Turn Filter Off&quot;
Me.Filter = &quot;[Finished] = False&quot;
Me.FilterOn = True
Me.Requery
Else
Command42.Caption = &quot;Apply Filter&quot;
me.filter = &quot;&quot;
Me.Requery
End If Mike Rohde
rohdem@marshallengines.com