Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    27

    Unanswered: Filter data according to many criteria ( On Change event of Text Boxes)

    Hello every body,
    I have four text boxes and a subform on a form, the text boxes is suppose one is for the First Name, Last Name, Country, and Average and the subform includes all the data...

    Wanted:
    I want to filter the subform data at the ONCHANGE event of each text box, suppose if some want type letter A or a in First Name textbox, then all the data filtered which their First Name begins by letter A or a and if type more letter then according to those letter the data be filtered, and also if also type anything in the Last Name textbox, then according to the data in First Name and Last Name, the data must filtered...
    How it is possible?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you sure you want to do this in the OnChange event? It will slow things down to a crawl, especially requerying a subform which could have several matches on a FirstName, LastName, and 2 other fields.

    If you want to try it, you can set the Record Source of the SubForm to a query and put criteria in it for the FirstName and LastName fields (ie. Like Forms!MyMainFormName!MyTextBoxFirstName & "*") and (Like Forms!MyMainFormName!MyTextBoxLastName & "*") and the same with the other fields (use the Like statement and the & "*"). Then issue a requery command to the subform (ie. Forms!MyMainFormName!MySubFormName.Requery) in the OnChange event of the FirstName, LastName and each of the other text box fields. You'll also need to think about what to do if someone enters a few letters in (for example) the FirstName search textbox and what you want to do with those letters if someone then tabs to the LastName (ie. do you want to clear the FirstName search textbox?) - ie. me!MyFirstNameField = null, or tabs to the other fields and starts entering characters.

    But I think you'll find the performance to be extremely slow, especially if you get any size of data. You'll see a big hesitation after each letter is entered and it will soon become unbearably slow. Usually this kind of thing is done on the AfterUpdate command.

    Also, if you're looking for some neat little searches on alphabetical letters and such, see the Searching examples in the MSAccess DB Code Bank (there are 2 or 3 examples). You may want to gander at these and see some different methods on doing something similar.
    Last edited by pkstormy; 07-22-07 at 21:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2007
    Posts
    27

    Re...Filter On Change

    Thanks much for the replying, I did the same what you mentioned, BUT:

    1. Sometimes the subform is not filtered until I move to the next textboxes, or enter something in the next textboxes... what is the problem?

    2. I need to do it without having QUERY, but do everything through VBA and coding...How it is possible?

    Thanbks for your kind help................
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2007
    Posts
    33
    Hi,
    I use something like this

    Onchange:

    Dim VarStr As String
    VarStr = Me.Textbox.Text

    Me.Subform.SetFocus
    Me.Subform.Requery

    Me.Textbox.SetFocus
    Me.Textbox= VarStr
    Me.Textbox.SelStart = Len(VarStr)

    set the Record Source of the SubForm to a query and put criteria in it for the FirstName and LastName fields (ie. Like Forms!MyMainFormName!MyTextBoxFirstName & "*") and (Like Forms!MyMainFormName!MyTextBoxLastName & "*") and the same with the other fields (use the Like statement and the & "*")

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you want to do it through vba verses a query, make the query with all the criteria and take the SQL statement from the query and paste it into your code as the sql statement (editing the syntax as needed) for the recordsource of the form...

    ie..For your code for the OnChange event...

    me.recordsource = "Select....."
    or
    me!MyMainFormName!MySubFormName.recordsource = "Select...." <- your sql statement from the query pasted in the quotes with the syntax fixed.
    or
    Forms!MyMainFormName!MySubFormName.recordsource = "Select...."

    You'll need to figure out the sql statement though and the criteria you want to use but this would be the approach I might use. You can find examples of how to make a sql statement with the "like" keyword in several places on this forum. After you've made a sincere effort on constructing the sql statement, if you need help, post what you've constructed and we will help you.

    Use PedroF's code to update the textbox or you can also try a me.repaint.

    If you put a msgbox "Change." in the OnChange event of the textbox, you should see the message box appear after every character you type. Keep in mind that some users will backspace (another reason I don't like using the OnChange event.) I don't think you'll need it but you can do the me.repaint command in the OnChange event if you have problems of if PedroF's method doesn't work. I still recommend NOT using the OnChange event though.
    Last edited by pkstormy; 07-23-07 at 18:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jun 2007
    Posts
    33
    Hi,
    You can use backspace or space with my code and it works fine OnChange event...
    I used long time and didn't have any problems.

  7. #7
    Join Date
    Jul 2007
    Posts
    27
    Mr. PedroF, thanks for the replying...
    As you see in the attachment, I have four text boxes for filtering, and I am wonder where to put your code at on change event? could you plz kindly do what you mentioned on the attachment and reattach it here...that would be very kind of you...

    also I am thanking Mr. pkstormy very much

  8. #8
    Join Date
    Jun 2007
    Posts
    33
    Here is the db.
    This way is slower but its much nore pretty...
    Attached Files Attached Files

  9. #9
    Join Date
    Jul 2007
    Posts
    27
    Thanks very much for the attachment.......

Posting Permissions

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