Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Using Text Box for Query Criteria (Access 97)

    I haven't done any Access programming in more than a year and I've apparently forgotten something fundamental about using a reference to a text box on a form as the query criteria! For this question I have a simple form named MyForm1 with one text box named MyText1. I have a table named Customers with one significant column named ContactLastName. I'm using the following SQL statement in the query:

    PARAMETERS [forms]![myform1]![mytext1] Text;
    SELECT Customers.CustomerID, Customers.ContactLastName
    FROM Customers
    WHERE (((Customers.ContactLastName)=[Forms]![MyForm1]![MyText1] & "*"));

    The query does not show any records in spite of the fact that I enter into the text box either the first couple of letters from a name that is in the table or a whole name that is in the table. Of course I run the query after having entered the text into the text box. If I substitute something like "wil*" in the criteria instead of the text box reference all the Wilson records in the table appear when I run the query.

    Can someone tell me what mistake I've made in this design?

    Thanks, Bill

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Text Box for Query Criteria (Access 97)

    If you want to use a wildcard, you must use the LIKE keyword instead of =

    WHERE (((Customers.ContactLastName) LIKE [Forms]![MyForm1]![MyText1] & "*"));

    Also, make sure you have pressed Tab after entering a name in the text box, otherwise the text you entered, otherwise you'll filter on the last "saved" value of the text box instead of the currently entered text.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Using Text Box for Query Criteria (Access 97)

    HansV,

    "Like"! Obviously!! I knew I was making some kind of simple mistake. I was wondering about the second item you mentioned; to tab off the control so the new value would be used. I would like to have the form react as each character is added to the text box. I think there is a save or refresh or something like that that my VBA On Change routine can use to solve that problem and stay in the text box. I'll experiment some but if you know the answer right off I'd appreciate hearing it. :-)

    Thanks for the help.
    Bill

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Text Box for Query Criteria (Access 97)

    You can use the Text property of the text box to capture the text as it is being typed by the user:

    LIKE [Forms]![MyForm1]![MyText1].[Text] & "*"

    but I'm not sure I understand what you're doing - you haven't provided enough information.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Using Text Box for Query Criteria (Access 97)

    Yes, I was trying to keep the explanation simple for the other problem. What I have is a continuous form that contains an unbound text box in the Form Header. The form's RecordSource is a query that uses the contents of the unbound text box and a "*" as the criteria for the Last Name column in the table. I would like for the user to be able to begin entering a last name in the text box and have the Detailed section of the form list all the names from the table that begin with that letter. As the user enters more letters the list of names should be reduced until the name is unique or the list is short enough for the user to pick the desired name out manually.

    Added: I keep wondering if I shoud be using a subform for the list of matching names?

    Any suggestions you may have will be greatly appreciated.

    Bill

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Text Box for Query Criteria (Access 97)

    You can use the On Change event of the text box to requery the form:

    Private Sub MyText1_Change()
    Me.Requery
    End Sub

  7. #7
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Using Text Box for Query Criteria (Access 97)

    Yes, that is what I thought and that is what I set up. And a breakpoint shows that the On Change procedure does run as expected. However I still have two problems: 1) the Detailed section of the form is not showing the records that are shown in the query if I open it and run it manually with some letters in the unbound text box, and 2) the text in the text box is all 'selected' after the Requery runs. I've used some technique in the past to place the cursor at the end of the text in a text box but I can't remember how I did that. I've tried all the combinations of Locked and Enabled on the text boxes in the Detailed section to make sure I wasn't preventing them form displaying data. I don't want to user to be able to move the focus to any of those text boxes or to be able to change their contents.

    Suggestions will be appreciated!

    Bill

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Text Box for Query Criteria (Access 97)

    Why don't you use a combo box instead of a text box + continuous form? A combo box has the feature you want built in.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Using Text Box for Query Criteria (Access 97)

    Yes, that has some merits. This is for an appliance service organization and I'm using this form for them to use to find an old customer's records and it seems like there may be some human advantage in actually reducing the length of the list that is visible as they enter the name, while they are probably on the phone with the customer. I rearranged my form so the list is a datasheet in a subform and that is working fine! I'm not sure why the single form didn't work but this will do fine.

    Thanks for your help.
    Bill

Posting Permissions

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