Solved

How to create a dynamic filter

Posted on 2009-06-29
68
1,609 Views
Last Modified: 2013-11-28
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: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24522261.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

0
Comment
Question by:jackrabbitman
  • 41
  • 27
68 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24740150
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

0
 

Author Comment

by:jackrabbitman
ID: 24740358
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.  
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24740430
oops sorry about that.  It should be:
         If .cname <> ""  
0
 

Author Comment

by:jackrabbitman
ID: 24740489
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.
0
 

Author Comment

by:jackrabbitman
ID: 24740525
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......
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24740625
If nz(.cname,"") <> "" Then
        .cname.setfocus
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24740645
change event to afterUpdate instead of Change
0
 

Author Comment

by:jackrabbitman
ID: 24744875
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.
0
 

Author Comment

by:jackrabbitman
ID: 24744933
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?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24745446
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

0
 

Author Comment

by:jackrabbitman
ID: 24745495
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.....
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24745755
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.


0
 

Author Comment

by:jackrabbitman
ID: 24746011
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24746677
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

0
 

Author Comment

by:jackrabbitman
ID: 24746785
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?
0
 

Author Comment

by:jackrabbitman
ID: 24746807
also I don't think i'm anywhere close to the 255 character limit, i'm only working with a few letters right now.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24747270
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

0
 

Author Comment

by:jackrabbitman
ID: 24747335
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24749100
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

0
 

Author Comment

by:jackrabbitman
ID: 24749526
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....
0
 

Author Comment

by:jackrabbitman
ID: 24749540
Is there a way to see what control does have focus?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24749707
Yes....Screen.ActiveControl.Name.  The active control is the one that has focus.

Dim strActiveControlName As String
 
strActiveControlName = Screen.ActiveControl.Name
0
 

Author Comment

by:jackrabbitman
ID: 24749728
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
0
 

Author Comment

by:jackrabbitman
ID: 24749812
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.
0
 

Author Comment

by:jackrabbitman
ID: 24749867
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24749932
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
0
 

Author Comment

by:jackrabbitman
ID: 24754072
Alright when I changed the code to that I get the "error" every single time.
0
 

Author Comment

by:jackrabbitman
ID: 24754085
And cname is also the name of the textbox
0
 

Author Comment

by:jackrabbitman
ID: 24754132
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
0
 

Author Comment

by:jackrabbitman
ID: 24754268
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?
0
 

Author Comment

by:jackrabbitman
ID: 24754355
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
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24754420
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.
0
 

Author Comment

by:jackrabbitman
ID: 24754462
Okay well as per the above post....it does have focus , it seems to be an access bug
0
 

Author Comment

by:jackrabbitman
ID: 24754607
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?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24754614
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.
0
 

Author Comment

by:jackrabbitman
ID: 24754664
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?
0
 

Author Comment

by:jackrabbitman
ID: 24754761
...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?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24754787
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.
0
 

Author Comment

by:jackrabbitman
ID: 24754948
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24755048
<<<< 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?
0
 

Author Comment

by:jackrabbitman
ID: 24755062
its coming from a union query that is the RecordSource for the form.
0
 

Author Comment

by:jackrabbitman
ID: 24755329
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

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24755574
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
0
 

Author Comment

by:jackrabbitman
ID: 24755772
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24755872
sounds like it encountered a txtCname or CompanyName it did not like? can you post the value of your input variables at error point.
0
 

Author Comment

by:jackrabbitman
ID: 24755920
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?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24756337
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.
0
 

Author Comment

by:jackrabbitman
ID: 24756682
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?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24757775
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)
   
0
 

Author Comment

by:jackrabbitman
ID: 24757974
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.
0
 

Author Comment

by:jackrabbitman
ID: 24757977
I'll wait to hear from you before doing anything tho....
0
 

Author Comment

by:jackrabbitman
ID: 24758180
and perhaps your opinion on how to preventing extra changes from occuring would be helpful
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24758184
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        
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24758222
If Me.RecordsetClone.RecordCount = 0 then
   Exit Sub
End If
'_____________________________________________________
0
 

Author Comment

by:jackrabbitman
ID: 24758232
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
0
 

Author Comment

by:jackrabbitman
ID: 24758252
and putting the exit sub code at the begining does prevent the error, but also prevents anymore data entry.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24758283
try this:
If Me.RecordsetClone.RecordCount = 0 then
   Me.Undo
End If
0
 

Author Comment

by:jackrabbitman
ID: 24758311
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?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24758394
forgot fields are not bound; try DoCmd.CancelEvent
0
 

Author Comment

by:jackrabbitman
ID: 24758418
DoCmd.CancelEvent was to no avail.....
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24758493
.txtCname.Value = ""
Me.Requery
0
 

Author Comment

by:jackrabbitman
ID: 24758571
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24758596
.FilterOn = False
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 24760122
OK, see if this works:

'clear the controls on the form so that it is ready for another search
If Me.RecordsetClone.RecordCount = 0 then
   .txtCname = ""
   ,Filter = ""
   ,FilterOn = False
End If
0
 

Author Comment

by:jackrabbitman
ID: 24763089
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.
0
 

Author Closing Comment

by:jackrabbitman
ID: 31598096
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24763397
Glad I was able to help you.  Thanks for all the positive feedback.
0
 

Author Comment

by:jackrabbitman
ID: 24787770
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....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now