Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    22

    Cool Unanswered: Clever Search Form - would like to clean up

    I have created a search form that works pretty well for my purposes. It consists of a main form that has several unbound fields. These unbound fields are used as the criteria for the query that is the record source for the subform. I have used On Change event procedures that update the criteria and requery the record source after every character typed.

    The only real problem that I have run across is that I had to use the refresh command in the On Change event. This made it so that space characters could not be used. If you type a space, the refresh wipes it out and takes you back to the last character you typed. Is there a way to make it so that the space is treated like a literal character? I found some help on this topic but I was unsuccessful at implementing it.

    I use this type of search form for Project Lists, Client Lists, Vendor Lists, and all kinds of other applications. I have attached a version of the form that searches a list of employees.

    ANY suggestions on cleaning up this form would be greatly appreciated. I would love it if my On Change event could work better, especially if there is a way to do it without refreshing.

    Thanks in advance for any help.

    Craig
    rockjock
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I'm puzzled why you need the refresh command - refresh writes data back to the underlying recordsource and should have no impact on unbound fields. When you change the recordsource, the required course of action is to requery the object - the refresh probably triggers a requery, and that's why it works.

    Here's a few things to try:
    1) Instead of the refresh command, use a requery command on the subform
    2) In your On Change event, store the value to a variable prior to the requery/refresh command and then write it back to the field following the command.
    3) Instead of using On Change, use After Update - this will wait until you leave the field before calling the procedure, and only trailing spaces will be cropped.

    Good luck!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Nov 2004
    Posts
    22
    Quote Originally Posted by tcace
    I'm puzzled why you need the refresh command - refresh writes data back to the underlying recordsource and should have no impact on unbound fields. When you change the recordsource, the required course of action is to requery the object - the refresh probably triggers a requery, and that's why it works.

    Here's a few things to try:
    1) Instead of the refresh command, use a requery command on the subform
    2) In your On Change event, store the value to a variable prior to the requery/refresh command and then write it back to the field following the command.
    3) Instead of using On Change, use After Update - this will wait until you leave the field before calling the procedure, and only trailing spaces will be cropped.

    Good luck!
    Todd,

    Thanks for your reply, I appreciate the feedback. I have a couple of responses to your suggestions:
    1) I tried to just requery on the On Change event, but it did not work. It acted like it did not recognize the "change". The ultimate goal of this search form is that the subform list narrows with every character the user types into the main form's unbound text boxes. This works on the version I attached to my original post, it just isn't very clean and it has limitations (like not allowing you to enter spaces). I don't know why refresh worked, but it seemed to do the trick.
    2) I have attempted your second suggestion before but have not had any success, likely due to the fact that I am not very good at writing event codes. Could you provide me with an example of how the code would look to write the value to a variable?
    3) The After Update does not achieve the result that I need. For After Update to occur, the user would have to leave the field. As I stated above, I want the event to trigger with every character that is entered in the text box. Unfortunately, the trailing spaces are cropped when I use On Change, and thus the reason I posted this thread.

    Thanks again for all of your thoughts. I would really like to clean this form up to work better.

    Craig
    rockjock

Posting Permissions

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