Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Filtering data based on a text box

    Hi there,

    I have a text box and a subform. In the subform is a list of customers. I would like to be able to filter the customers as I type into the text box.

    I have put the following code in the criteria and it works fine as long as I add a '*' after the last character. I also have to make sure I use the correct CASE.

    Is there a way to append a * to every character in the text box or query so it will list all customers beginning with say M....Mark,Matthew etc then if I type Mar it will only show Mark.

    here is the code so far

    Like IIf(IsNull([forms]![main]![cbo_CustomerSelection]),"*",[forms]![main]![cbo_CustomerSelection])

    I've put it in the query critieria, so nothing in the text box all records are shown,..if I type M* all records begininning with M appear. I just need to get rid of having to input the *

    thanks

    marcusmacman

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Try:

    Like IIf(IsNull([forms]![main]![cbo_CustomerSelection]),"*",[forms]![main]![cbo_CustomerSelection] & "*")

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    spot on that!! I tried to append a "*" but my syntnax was bad. Thanks for your help.

  4. #4
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    On the same issue....
    When i enter text in the combo i have a expression which requeries the subform on every keypress, for some reason it is not updating the subform/query. Any ideas? It work if i requery after update. But i wanted it to be for dynamic. So as you type in the customer name it starts to refresh the subform based on the text.

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    anyone have any advice? I'm nearly there it just doesn't update properly

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Are you updating the RecordSource? If so Access should be refreshing it. The trick is getting to the subform's RecordSource.

    To set the record source for the subform in the main form you need to set:

    Me.subformname.Form.RecordSource=SQL

    Replace the subformname with the actual name you gave to the subform. And replace SQL with your updated SQL string.

  7. #7
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Thank DCKuncle

    I tried putting the Me.subformname.Form.RecordSource=SQL into the text box where i enter the data. I replaced the SQL with the query in the subform. It didn't like that.
    The thing is it works fine if i used after update. I press a return and all records starting with a get displayed. I can't understand why when i put the action on 'Key press' it doesn't work at all.

    any help anyone? been stuck on this for 8 hours now :-(

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You are going to need to post more code. Show me all the code you have for this form.

  9. #9
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    ok here goes...

    I have a form called 'main' In that form is a Tab Control. I've called one of the tabs Customers.
    On the Customers Tab I have a unbound text box called cbo_CustomerSelection.

    within the same Customer Tab control page I have a subform called frm_Main_sub_Customer_Selection.

    The record source for the subform is qry_CustomerSelection
    qry_CustomerSelection is based on a table of customers. one of the fields is called Customer_Name. In the criteria I have

    Like IIf(IsNull([forms]![main]![cbo_CustomerSelection]),"*",[forms]![main]![cbo_CustomerSelection] & "*")

    So back to the text box called 'cbo_CustomerSelection.' i have created an event on the after update

    Private Sub cbo_CustomerSelection_AfterUpdate()
    Me.frm_Main_sub_Customer_Selection.Requery
    End Sub

    As i type in cbo_CustomerSelection, each time i press return it filters the subform which contains all the customers. So i type A <return> and all customers beginning with 'A' show up in the subform.

    What i would like to happen is instead of hitting return after each charactor , i would like it to requery after each keypress.

    I tried to put Me.frm_Main_sub_Customer_Selection.Requery in the onchange and on keypress actions but it didn't work.

    Thanks again for looking into this
    marcus

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You got me on a good day. I was able to tinker with it and I got it working on my system. I also learned some things. So I think you need to change a couple of things:

    1. Change the criteria in the query to:

    Like IIf(IsNull([forms]![main]![cbo_CustomerSelection]),"*",[forms]![main]![cbo_CustomerSelection].[Text] & "*")

    NOTE: I added .[Text] because the default property, Value, is not updated until the user exits the box.

    2. I had to put the code into the KeyUp event. KeyPress allows you to see the key before it is saved to the Text property. So remove the AfterUpdate code and put it in:

    Private Sub cbo_CustomerSelection_KeyUp(KeyCode As Integer, Shift As Integer)
    Me.frm_Main_sub_Customer_Selection.Requery
    End Sub


    And with those two changes I think you will be good to go.

  11. #11
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Oh, I also just noticed that you do not need all the IIF stuff in the criteria if you put the following in the criteria it should work:

    Like [forms]![main]![cbo_CustomerSelection].[Text] & "*"

  12. #12
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    brillient...can't spell it cause i've hada few beers but that's exactly what i've been looking for. thanks for your help. really appriciate it (spelling definatly going down hill)


  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    No problem. I might actually use this for an upcoming project I have. It was an interesting challenge. Good luck with the rest of you project.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •