Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Location
    UK
    Posts
    44

    Unanswered: Updating subform from field (Search)

    Hi Guys, was wondering if you could help me.

    I have set up an unbound form with an unbound text field (search)
    Within the form is a sub form which is bound to a query
    Query uses the unbound text field (search) as criteria with "*"
    Button on form re queries the sub form

    Everything works great at the moment (when user enters text and clicks search button the results are displayed)

    What i would like to do is have the sub form re query every time a character is added to the search field

    i.e if use types A the subform will filter all records starting with A then press L subform displays AL* etc...

    I have seen this done somewhere but cant for the life of me find it.

    I have tried to requery the subform on key press etc... to no avail.


    Thanks in advance

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You would use the change event of the textbox. You would also need to use the .Text property of the textbox, because the default .Value property will not be updated while the user is typing. Because I don't think you can reliably use the .Text property in a query, I would set the source of the subform:

    Me.SubformControlName.Form.RecordSource = "SELECT...WHERE FieldName Like '" & Me.TextboxName.Text & "*'"
    Paul

  3. #3
    Join Date
    Nov 2009
    Location
    UK
    Posts
    44
    thanks very much for your reply, i have just managed to resolve using code i found on here (i.e performing the update on change the setting the focus back to the text field)

    Code:
    Private Sub Text0_Change()
    Dim VarStr As String
    VarStr = Me.Text0.Text
    
    Me.frmsearchcustomerstep3subform.SetFocus
    Me.frmsearchcustomerstep3subform.Requery
    
    Me.Text0.SetFocus
    Me.Text0 = VarStr
    Me.Text0.SelStart = Len(VarStr)
    End Sub
    Do you think this will be ok?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Offhand it looks like it would work. It's not how I would do it, and my untested gut feeling is that it would be less efficient, but who knows? Use it and see if you have any performance problems.
    Paul

  5. #5
    Join Date
    Nov 2009
    Location
    UK
    Posts
    44
    performance is good at the moment but that's because there aren't many records in the table, how could i make the sub form open initially without finding any records?

    i.e at the moment because i have the query set to [field]&"*" even if i don't type anything in the filter fields the sub form loads with ALL records. Would be nice if the subform only started to update when i start to type (i presume this would speed up the process when many records are stored)

    Main reason i have gone for this format is so that is the user doesnt spell the name correctly it still filters the results rather than being empty (they can see real time the results)

    Might not be possible but worth asking :

    Thanks for spending time to help Paul

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If the subform requery does give you problems using this method (which I've had in the past), keep in mind that the 'search' field may need to be "Refreshed so to speak" before the subform can see the changes. You can try a me.searchfieldname.repaint or just a refresh command (or me.searchfield.requery) to refresh this field on the form somehow and THEN requery the subform if this happens. Note though that you'll then most likely need to RE-position the cursor back into the search field at the end of the field.

    In regards to an answer to your question, you could try just removing the sourceobject property (clicking on the subform in the main form in design view), remove the sourceobject name and then set the soureobject = "MySubFormName" when the character is entered. This will give you the fastest loading time of the main form but it may or may not be what you want since there may be a delay after the 1st character is entered. If the search field is blank, set the sourceobject = "". I usually create a blank (unbound) form with just 1 text box saying "Enter Search Criteria Above" and have this as the default sourceobject or when the search field is blank. I then set it to the appropriate subform after the search is entered (ie. me.MySubFormName.SourceObject = "MySubFormName".)

    The requerying after every character method tends to take a heavy toll on the query/form refreshing, especially if you're dealing with mass records (or have a lot of Format Conditions on the subform fields.) I wouldn't recommend it against any large recordsets. I found users like the "A" "B" "C" search buttons similar to the one in the example below versus records changing as you type. Here's a couple of other different search examples if you're interested:

    (by Nckdryr)
    http://www.dbforums.com/6274726-post18.html
    http://www.dbforums.com/6250864-post11.html
    (by pkstormy)
    http://www.dbforums.com/6274809-post27.html (has ABC)
    http://www.dbforums.com/6289596-post39.html
    http://www.dbforums.com/6325742-post63.html
    http://www.dbforums.com/6342970-post69.html
    Last edited by pkstormy; 11-09-09 at 22:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2009
    Location
    UK
    Posts
    44
    Thanks for your advice, i do also have a "phone book" type form to do the searching via starting letters.

    The crooks of it is its going to be a customer search form (just like what you would see on the internet or postcode anywhere) so that the user can filter out by postcode, house number, surname etc.. this is mainly because the customer would have probably forgotten their booking reference so we need to be able to quickly find a customer with their personal details.

    I think it would work if i could just stop the sub form from initially populating (ALL records) and only requery once the user starts to type in the search critera fields.

    Please note that the code does already work and is really fast but am concerned that when/if there are thousands of records the sub form will take ages to initially load up before being filtered by the search fields

    Thanks for taking time out to help me, really appreciate it
    Attached Thumbnails Attached Thumbnails search form.JPG  

  8. #8
    Join Date
    Nov 2009
    Location
    UK
    Posts
    44
    great links, thanks guys will have a look through the examples.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I just finished re-editing my previous post. You may want to re-read it as I finished answering your original question. - sorry about that.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2009
    Location
    UK
    Posts
    44
    Quote Originally Posted by pkstormy View Post
    I just finished re-editing my previous post. You may want to re-read it as I finished answering your original question. - sorry about that.
    Thanks pk, sorry for the delay in replying.

    This solution worked better than i could have expected you are amazing

    Thanks to all who have helped

    Keep up the good work

  11. #11
    Join Date
    Jan 2012
    Posts
    1

    I cant open the links

    Do you have 2007 version of links, I cant open them?





    Quote Originally Posted by pkstormy View Post
    If the subform requery does give you problems using this method (which I've had in the past), keep in mind that the 'search' field may need to be "Refreshed so to speak" before the subform can see the changes. You can try a me.searchfieldname.repaint or just a refresh command (or me.searchfield.requery) to refresh this field on the form somehow and THEN requery the subform if this happens. Note though that you'll then most likely need to RE-position the cursor back into the search field at the end of the field.

    In regards to an answer to your question, you could try just removing the sourceobject property (clicking on the subform in the main form in design view), remove the sourceobject name and then set the soureobject = "MySubFormName" when the character is entered. This will give you the fastest loading time of the main form but it may or may not be what you want since there may be a delay after the 1st character is entered. If the search field is blank, set the sourceobject = "". I usually create a blank (unbound) form with just 1 text box saying "Enter Search Criteria Above" and have this as the default sourceobject or when the search field is blank. I then set it to the appropriate subform after the search is entered (ie. me.MySubFormName.SourceObject = "MySubFormName".)

    The requerying after every character method tends to take a heavy toll on the query/form refreshing, especially if you're dealing with mass records (or have a lot of Format Conditions on the subform fields.) I wouldn't recommend it against any large recordsets. I found users like the "A" "B" "C" search buttons similar to the one in the example below versus records changing as you type. Here's a couple of other different search examples if you're interested:

    (by Nckdryr)
    http://www.dbforums.com/6274726-post18.html
    http://www.dbforums.com/6250864-post11.html
    (by pkstormy)
    http://www.dbforums.com/6274809-post27.html (has ABC)
    http://www.dbforums.com/6289596-post39.html
    http://www.dbforums.com/6325742-post63.html
    http://www.dbforums.com/6342970-post69.html

Posting Permissions

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