Link to home
Start Free TrialLog in
Avatar of jackrabbitman
jackrabbitman

asked on

How to create a dynamic filter

The saga continues....

I'm not sure exactly how this one will work, but I'm working on creating a search form that will automatically filter the results as a user types in information into unbound text boxes.  To see more of a history about this check out this link: https://www.experts-exchange.com/questions/24522261/SetFocus-Event-doesn't-work.html
I've followed the advice and changed the code to use the filter property and orderby property now rather than changing the recordsource everytime, but I am still having the same issue with updating the filter property, using the .text property is what I have to do but I can't get the problem with SetFocus to work since focus is required to use the .text property.  Is this the only way to do this or is there another way?  Or is there a better way to update the filter than the way I'm currently going about it.  I'll put my code below. Any ideas?

Let me know if there are any more details that you need.
With Me
 
.cname.setfocus
If .cname.Text <> "" Then
        
        length = Len(.cname.Text)
    
        criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search]![cname])) "
        
        condition = True
        
    End If
 
End With

Open in new window

Avatar of puppydogbuddy
puppydogbuddy

try the attached
With Me
 
 
If .cname.Text <> "" Then
        .cname.setfocus
        	        
        length = Len(.cname.Text)
    
      criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search].cname)) "
   
      condition = True
        
    End If
 
End With

Open in new window

Avatar of jackrabbitman

ASKER

I plan to eventually reuse the code so I cannot assume that the focus will already be on the cname textbox....and I can't evaluate "If .cname.Text <> "" then" without the focus.  This is the line the error occurs on.  I have tested the code without using SetFocus and I get the same result.  It only works on some occasions which seem to occur randomly.  
oops sorry about that.  It should be:
         If .cname <> ""  
I just tried it and it came up as null everytime for the value of cname....so the code didn't execute.  While debugging if I put in the .Text I could see the full value of the text box.  This event occurs in the on change event, if that helps at all.
I am wondering if it really is a set focus error or if I'm getting a runtime error that is actually caused by something else.  It seems to happen when you enter data that is more than 1 character passed all possible values for example, if the longest company name is ABC and you enter in ABCDE you get the error (most of the time) or sometimes if you backspace you can sometimes get the error as well so either way....

Truely confused......
If nz(.cname,"") <> "" Then
        .cname.setfocus
change event to afterUpdate instead of Change
I tried the If nz(.cname,"") <> "" then and it seemed to work better than the other method.  It doesn't update until you leave the text box and come back to it, but I can live with that for now.  The problem is that with the length = Len(.cname.Text) piece of code it still doesn't have the focus set so once again a problem.  As far as using the afterUpdate event...the goal is for the list to change as the user types each letter to facilitate searching if you are unsure of a certain value.  So I'd really rather avoid that route.
I think that I have figured out the problem now....but not how to fix it.  From a previous question that I had with setFocus LSMConsutling said " ... in order to SetFocus, you must "save" the control first." So I guess the real question is how can I save the control?
You are a little bit confused about set focus.  The main requirements are that the control has to be enabled, not locked, and visible.  

 see   http://msdn.microsoft.com/en-us/library/aa205181(office.10).aspx

I have modified your code accordingly.  See snippet,
With Me
   .cname.enabled = True
   .cname.locked = False
   .cname.visible = True
   .cname.setfocus
 
   If nz(.cname,") <> "" Then
      length = Len(.cname.Text)
    
      criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search].cname)) "
   
      condition = True
        
    End If
 
End With

Open in new window

alright, tried the new code with the same result.....after a few tries I still get runtime error 2185 saying that I can't use that property unless it has the focus.....
Somehing is preventing one or more of your controls from getting focus.
for example, here is somehing that could prevent a control from getting focus.
             http://allenbrowne.com/ser-34.html
You need to incorporate the above into your code.  
Also, do you have a subform ?  That has to be handled differenly also because focus has to be set on the subform control before it can be set on the subform itself, and its controls.


What exactly do you mean by incorporate...use both the kestroke and change events because text could be pasted into the text box?

And its not a sub form, Its just on the form header.
No, I meant you need to check for 255 character limit, but before you do anything seewha happens when you run the revised code in the snippet below.
With Me
   .cname.enabled = True
   .cname.locked = False
   .cname.visible = True
   .cname.setfocus
 
   If .cname = Screen.ActiveControl.Name and nz(.cname,") <> "" Then
      length = Len(.cname.Text)
    
      criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search].cname)) "
   
      condition = True
    Else 
      MsgBox "Control does not have focus. " & .cname     
    End If
 
End With

Open in new window

Alright I tested it and ended up going a step backwards......without using the .text property i'm using the value again and it comes up one letter short everytime (sorry I missed this last time) and I get the Control does not have focus everytime....however interestingly enough this did prove why once the error occurs once it happens everytime.  Once the MsgBox popped up everytime the same value was reported back for the value of .cname....perhaps again this needs to use the .text property?
also I don't think i'm anywhere close to the 255 character limit, i'm only working with a few letters right now.
Not sure what you meant about coming up 1 Char short, but adjusted length in your code.  You need to determine why certain controls can't get focus. Are they labels?
With Me
   .cname.enabled = True
   .cname.locked = False
   .cname.visible = True
   .cname.setfocus
 
   If .cname = Screen.ActiveControl.Name and nz(.cname,") <> "" Then
      length = Len(.cname.Text)+ 1
    
      criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search].cname)) "
   
      condition = True
    Else 
      MsgBox "Control does not have focus. " & .cname     
    End If
 
End With

Open in new window

Sorry, I should have explained that better....the whole reason I'm using .text is because it need what is currently in the text box at the time the onChange event occurs....without the .text I get the value which is one charcter less than what is actually in the text box.  So if I have ABC in the text box, I end up with AB as the value.  I can't setFocus on any control and they are all unbound textboxes.
ok, it is not correct to say you can't set focus to any of your controls.  Whether a textbox is bound or unbound,  focus can be moved to it.  If focus is not moved to the control, you will not be able to <<<<read>>>> any of its properties. The value property of a textbox control is the most recent saved value of the control's text property.  The text property is the current contents of the control.   The Text property is always current while the control has the focus.

I researched this and found an example that should clear some things up for you.  Note that this link shows that the text property of text1 can be read while assigning its text to another control.
              http://bytes.com/groups/ms-access/201312-counting-characters-entered-text-box

Looking at your code with the above in mind, the code does not have a loop to go through all the textboxes.  Could that be part of the problem??  please show me the output of the first two textboxes.
With Me
   .cname.enabled = True
   .cname.locked = False
   .cname.visible = True
   .cname.setfocus
       'check to see if control has focus.  If it does then you can
       'read the control's text property to get its current content  
   If Me.cname = Screen.ActiveControl.Name Then   
      length = Len(.cname.Text) 
      criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search]!]![cname])) "
   
      condition = True
    Else 
      MsgBox "Control does not have focus. " & .cname     
    End If
 
End With

Open in new window

As of right now I have commented out all of the other If statements for the other textboxes because its the same code and keeps running into the same problem....eventually I want to be able to get to that stage, but I think that it will be easier to isolate the problem here and figure it out.  I also have only set up the onChange event for the cname textbox.  All of the rest of them are null and not read by the code as of now.

That link helped a little bit....I copied the code from the link and it worked without a problem so thats nice.  The code above is a sub so I thought that might be the problem, but I copied the code into the onChange event directly and still got the same result, it works sometimes and not others...so something is causing it to lose focus and not work anymore....
Is there a way to see what control does have focus?
Yes....Screen.ActiveControl.Name.  The active control is the one that has focus.

Dim strActiveControlName As String
 
strActiveControlName = Screen.ActiveControl.Name
ok I'll see where the focus is going and then maybe we can figure this out.....

thanks for all of your help so far

be back in a jiffy
Alright the results are in.....cname does have the focus so there must be some other reason for the error.  I still get the Runtime error 2185 "You can't reference a property or method for a control unless the control has the focus."  So there must be something else causing this to happen.
I also added error handling now to check and see what has the focus after the error occurs and it is still the cname textbox so something else must be causing it to go crazy.
this could be part of the problem:
     change this:
              If Me.cname = Screen.ActiveControl.Name Then
     to this:
            If Me.cname.Name = Screen.ActiveControl.Name Then

if .cname is the textbox control object.......then .cname.Name is the name of the control object
Alright when I changed the code to that I get the "error" every single time.
And cname is also the name of the textbox
and with playing around with it more.....its the Nz(.cname,"") <> "" that is causing it to skip the If statement.....i'll keep playing around with it, but any input would help alot
and finally THE BEST PART YET using the statement

If .cname.Name = Screen.ActiveControl.Name And .cname.Text <> "" Then

I still get the runtime error 2185....but if i comment out the .cname.text <> "" the .cname.name = screen.activecontrol.name works just fine....so how can it be  the active control and not have focus?
and upon further review.......

http://www.eggheadcafe.com/conversation.aspx?messageid=31619464&threadid=31619460

shows that
this error can occur if these conditions are met:
a) The combo is in the Form Header or Form footer section
b) The form is filtered such that no records match (or there are no records)
c) No new record can be added.

In this case, the Detail section of the form goes blank. The combo is still
visible, but Access gets really confused and can throw the error you
describe.

and
More info:
http://allenbrowne.com/bug-06.html

way to go google
if you look at the last code I sent you, Nz(.cname,"") <> ""  was removed. How come you still have it in your code. You don't need it with the new check that I added to test if .cname is the name of the active control. Nz(.cname,"") <> "" does not test for focus.....it is just testing if the control exists ...but it contains an error in syntax, it should have been:
Nz(.cname.Name,"") <> ""
but as I said it is not needed now that we added the new test for the active control.
Okay well as per the above post....it does have focus , it seems to be an access bug
and....I guess that the next step would be to not use the text property...is there a way to save the value in an unbound textbox?
you still get an error 2185 even with the changes?

My advice now is to make things easier to follow in the code by adhering to naming conventions.
you you change prefix the name of your textbox controls with txt to distinguish references to the textbox from references to the field in your table.

example:
txtCname would be the name of the textbox control
Cname would be the control source (field) name

remember before you change it in the code, you have to change it in the property sheet and table.
Yes, naming conventions will make this process easier.  I'll work on setting everything up again with the correct naming conventions.

Any ideas however as far as a work around for this problem?
...but on the same token why did the code snippet with
http://bytes.com/groups/ms-access/201312-counting-characters-entered-text-box

Is it because I'm updating the filter property? and that confuses it?
I am confused as to why you want to remove the text property.  I know the general purpose of the code is to construct a filter, but could you expain to me in more detail, the criteria line, including where CompanyName is coming from.  I think you may have a syntax error on that line.
Sorry.....I'm flying a mile a minute and need to slow down before posting every little thing that comes to mind....

I don't want to remove the .Text property, but it seems like there is a problem in Access with referencing the .Text property when the object is in the  Form Header...which mine is.   This is according to Allen Brown's website above.  companyName is the field name that the criteria is to be applied to and I have used all of the criteria code in SQL statements before without a problem so the syntax should be okay...once again naming conventions could be better.  The idea behind it is to take however many characters are in the unbound textbox, cname and reference them against the text in the textbox.

Thanks again for all your help, your patience is invaluable.
<<<< companyName is the field name that the criteria is to be applied >>>>

but where is it coming from?  Your code is behind a form.  Is this the name of a bound textbox control?
its coming from a union query that is the RecordSource for the form.
I've done some more testing with more results.
I have set up the following code:

 With Me
       .txtCname.SetFocus
       
        Dim strActiveControlName As String
 
        strActiveControlName = Screen.ActiveControl.Name
        MsgBox strActiveControlName

        .txtCname2 = .txtCname.Text        
        If .txtCname2 <> "" Then
       
            length = Len(txtCname2.Text)
   
            criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search].txtCname)) "
   
            condition = True
       
        Else
       
            MsgBox "Control does not have focus. " & .txtCname
       
        End If

Which works perfectly and I never recieve an error no matter what I do.

When I add the If statement below to apply the filter it goes back to its old messed up behavior.
        If condition Then
    
            .Filter = criteria
            .FilterOn = True
    
        Else
    
            .FilterOn = False
    
        End If

Open in new window

is Me is a different form than [Forms]![Search].txtCname] ?
if that is the case and both forms are opened, Access is confused which form to apply the filter, unless you set focus on the form that you want the filter applied.

Forms!theform.SetFocus
     .Filter = XXXXXXXXXX
     .FilterOn
Me. is the same form.  I went ahead and changed it however to have the full criteria of Forms!Search.filter = criteria and it worked for a while and then went back to runtime error 2185.  It seems that whenever I change the code it works pretty well for thefirst time and then when I open the second time in only took 3 keystrokes so....bizzar behavior.
sounds like it encountered a txtCname or CompanyName it did not like? can you post the value of your input variables at error point.
I can try, but it always changes....for example

I enter "a" into txtCname and it runs fine.  Then I hit backspace and error.
Close the form and open it again.
Follow the exact same sequence and no error.


Basically what I'm trying to say is I have no idea what input values cause the error because it changes everytime.  Sometimes it works, sometimes it doesn't.
Would a copy of the database help to explain this better?
It sounds like your problem is due to illegal characters or delimiter characters that require special handling to make them legal in the context of a field name.

For example Access treat a space in a field name as a delimiter (end of field name), unless it is enclosed in brackets.  That is why you are getting an error on the backspace.
  A Company has to be referenced as [A Company]

Your code has to deal with that.
Okay, well I think I might have finally isolated when it occurs.  If I enter a character as a filter say 'A' and it returns 0 results (so no companies start with 'A') then type another character or backspace or anything (basically another change occurs) then I get the error 2185.  However if it still returns a result then everything is okay.  That being said I still need to be able to account for a space in a company name and all that jazz so I will work on referencing things as [A Company] in my code.  So do I just need to count the number of results in the form to figure out when 0 are returned and not allow any more characters to be added?
No, if 0 results are returned when certain characters are typed in, it really is not an error in this context so I would just put in MsgBox or Resume Next as the next line of code:

If Me.RecordsetClone.RecordCount = 0 then
    MsgBox " No records returned"         'if you don't want Msg, use >>>Resume Next
End If
'_____________________________________________________
To avoid the problem with spaces acting as a delimiter, try embedding the brackets in the control name and see if it helps.
    length = Len([txtCname2].Text)
   
Alright, well....my thinking is that if I can prevent that event from occuring, then the next error won't occur.  I think I can get it to work.

Using the [ ] helped a lot and worked quite nicely...I still loose the space in .txtCname so I will have to figure that out yet, but I think I'm going to put that on a new question since its off topic and you've deffinatly earned the points.  So I'll post a link to the next question and award the points.

Thanks again for help.
I'll wait to hear from you before doing anything tho....
and perhaps your opinion on how to preventing extra changes from occuring would be helpful
you should try my recordcount solution...if user types "A' and gets no records, then user needs to type "B" to get list starting with ABXXXXXXXX

try this also.
.txtCname2 = .[txtCname].Text        
If Me.RecordsetClone.RecordCount = 0 then
   Exit Sub
End If
'_____________________________________________________
okay I think I'm missing something because the new criteria needs to be applied to the filter correct? so I would put the recordcount after that which is after everything has changed? and If a brings back no records AB will also bring back no records.  So what needs to happen is that it goes back to what it was prior to being entered
and putting the exit sub code at the begining does prevent the error, but also prevents anymore data entry.
try this:
If Me.RecordsetClone.RecordCount = 0 then
   Me.Undo
End If
Me.Undo doesn't do it....nothing happens.

one thing I could do is store all the variables and then if it comes back 0 then set all the fields back to what they were before the entry?
forgot fields are not bound; try DoCmd.CancelEvent
DoCmd.CancelEvent was to no avail.....
.txtCname.Value = ""
Me.Requery
well it does fine as far as the txtCname= "" but the requery doesn't make any visable changes.  Its almost like we need to make it forget that it changed the filter....we're making progress down the path thats for sure.
.FilterOn = False
ASKER CERTIFIED SOLUTION
Avatar of puppydogbuddy
puppydogbuddy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
HAHA YES!!!! That did the trick.  I might play around with it a little bit to see if I can do it without resetting the whole form, but at least it works now.  Finally!

Thanks again for all your help.
Thanks for helping over the course of 4 days and sticking with me to finally get something that works.  Give yourself a pat on the back.
Glad I was able to help you.  Thanks for all the positive feedback.
For anyone else that has this problem....I've finally set up my filter  to just undo the last change and some more stuff that I figured out.

If you run this code
Me.Filter = ""
Me.FilterOn = True
Then you will get the error the next time the code is run, and resetting the filter and setting it right seems to be what actually prevents the error from happening....