VBA not recognizing command

[kyuball]

I have a problem where I have a filter string written in the AfterUpdate event (syntax correct since I copied it from a form where it works solidly) but it seems like VBA is not recognizing the Me.FilterOn = True command because when I try using the filter (from a text box) the little funnel in the toolbar does not get highlighted like it would normally when you run a filter.

I wonder if this isn't something like when VBA occasionally does not seem to recognize objects/ controls on forms. It has happened to me in the past where I will start writing code and when I get to the part where I type "Me." the little drop down box with all of the available commands/ controls and obejcts does not pop up; and indeed when I go to use the object/ control in the form, it does not respond. What I have had to do to remedy this in the past is basically just re-do the form from scratch and then VBA would recognize everything I have put on the form.

However, in this case, I have put a lot of work into format and design of form and do not want to start the whoel thing from scratch. Can anyone help?

 

[vbaInet]

Have you set a Filter property before turning it on? If you do let's see the Me.Filter code line.

 

[kyuball]

Yes, I did. Basically, it is a filter that filters based on conditions within a text box and a combo box and it works perfectly on another form. The code is as follows:

Private Sub Text9_AfterUpdate()

Dim stFilter as String
stFilter = stFilter & "([name] Like ""*" & Me.Text9 & "*"") and ([type] Like ""*" & Me.Combo10 & "*"")"

Me.Filter = stFilter
Me.FilterOn = True
Me.Text9.SetFocus

End Sub

I have a similar one for Combo10, as well, so that whenever I update one field, it refreshes the filter.

 

[vbaInet]

Try this instead:

Me.Filter = "[name] Like '*" & Me.Text9 & "*' AND [type] Like '*" & Me.Combo10 & "*'"

By the way, you don't need the variable so you can remove it.

Also, names like "Name" and "Type" are not Access/VBA friendly names. They will cause you serious problems because they are reserved for Access and VBA. Try the filter out first then amend the names to something else.

 

[kyuball]

Thank you for your time and advice, vbaInet, but that still doesn't answer the question of why VBA is not recognizing the Me.FilterOn= True. Whether the filter was good, bad or ugly, the subroutine should still be attempting to turn the filter on; which it does not do. If the filter string was bad or ugly, then I would writing about not getting the results I want or some such where the filter is working incorrectly. My problem is that the filter is not turning on at all because VBA is not recognizing or whatever the problem is. And no, the actual field names are not [name] and [type], it's just some general field names I used for the example...

 

[vbaInet]

It certainly would turn it on if the filter contains something, but if the filter was a zero length string and you turn it on, it wouldn't come on. But obciously that doesn't apply in your case.

You might be experiencing corruption. Have you got another form where the filter is working or is it affecting the whole database? What you need to do is create a blank shell and import all the objects into that.

What version of Access is this?

 

[kyuball]

It certainly would turn it on if the filter contains something, but if the filter was a zero length string and you turn it on, it wouldn't come on. But obciously that doesn't apply in your case.

You might be experiencing corruption. Have you got another form where the filter is working or is it affecting the whole database? What you need to do is create a blank shell and import all the objects into that.

There is one other form in the database that is essentially the same kind of form (a list of inputted data in a continuous form) with similar filters that work perfectly. The only difference is that it pulls its data from a different table. It is only this form that is acting weird. Is there any way of kind of "refreshing" or "resetting" the form to remove the corruption without having to export all of the tables and forms to a blank shell? Not that that would be all that prohibitive to do, but I thought it might be good to see if it's possible now for future reference.

What version of Access is this?

I am on Access 2003.

 

[vbaInet]

The only other way to "try" to get rid of corruption is by performing Compact & Repair which I would imagine you've done already?

 

[The_Doc_Man]

Using debugger, single-step to the me.FilterOn = True. Step one more step and see if the form's properties now reflect that the filter is enabled. See what the filter string says. Also, check the form's "Allowxxx" properties to see if one of them is blocking the setting of a filter. It is possible to define a form such that you can't muck with with by setting what you will and won't allow. I don't know how they would get set in an unfavorable way, but it can happen.

 

[kyuball]

Hey all,

Thank you Doc and vbaInet. I ultimately decided just to re-do the form. I trid the Compact and Repair as you suggested, vba, but it did not remedy the problem and I really can't make heads or tails of the debugger.... Thank you both for your time and effort!

 

[vbaInet]

Did you try creating a new shell and importing your objects into that like previously mentioned? That may have solved the problem.

Here are links on how to use the debugger in the vbe:

http://www.fmsinc.com/tpapers/vbacode/Debug.asp
http://msdn.microsoft.com/en-us/library/aa830852(v=office.10).aspx

No developer can do without it.

Good luck with your db.

 

[kyuball]

Hey vbaInet,

Yeah, I tried moving everything to a new shell and still ran into the same problem... Weird, as that usually works, as well!

 

[vbaInet]

It works in some cases but it seems yours was corrupt "beyond repair". Good thing you had a backup.