1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Query a list of data from a form user input

Discussion in 'Business Applications' started by larrydavid, Jun 27, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    Hi, I have a Form created with a text box called "SoldTo_Text"; the user will input multiple SoldTo numbers; I then want the query that is pulling from the "SoldTo_Text" box to bring back data for those multiple SoldTo's. For Example, user inputs 3 soldTo's (1111,2222,3333) then the query takes those sold to's and returns the other values I have set in the query, we'll say they are percentages, so then it would look like (spaces indicate new columns):

    1111 3% 5%
    2222 6% 8%
    3333 8% 3%

    Currently, my query works great when they input 1 soldto, but as soon as I try to put in another soldto with it, it doesn't work.

    Any help is appreciated. Thanks.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    If you have multiple "Sold tos" are they in there own Sub table?
     
  3. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    Hello again OBP, Yes, I have them in their own sub table. I created (well, I added to the one you did showing me the relationships) a quick database to show what I am talking about. But, for some reason it doesn't seem like this site is allowing me to attach a file to this post...i'll check back later and see if it will allow me to add it.

    edit: Okay, I was able to add the attachment, not sure what was going on. Basically, in the form I created, if I put one sold to in the "soldto" text box on the form and then click the button (on the form) to run the query, it works great, but as soon as I try to add another soldto number to the text box on the form, it brings back no results. Thanks for taking a look at this.
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    When you say "but as soon as I try to add another soldto number to the text box on the form, it brings back no results" do you mean more than one in the same text box or just different one?
     
  5. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    More than one in the same box, and it will always be a different one. For instance, when I put in "00100012322" in the soldto text box in the form, the query will run and bring back the info for that one soldto; but if I put in 00100012322 and 00100012333 in the soldto text box in the form, then nothing comes back. Does that answer your question?
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Yes, that is because you can't do so.
    Because the Access Query can't look for more than in the same Criteria, you have to either use an "And" which looks for both or an "Or" which looks for either one or the other. But that can't be handled by the query using the Forms![Formname]![Fieldname] criteria as the query can't decipher the fact that you want he Sent to separated out.

    You will have to use VBA created SQL to do so with a Multi-select list box.
    Or use more than one search field on the form.
     
  7. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    Ok, I was assuming that VBA/SQL would need to be used; would you happen to know of a site which may have some example code like this? Thanks.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I have posted example databases and example code, some use queries and some use Form Filters, which would you prefer?
     
  9. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    I think form filters is more the route I am trying to go. Thanks.
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1058780