Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
27-Jun-2012, 11:53 AM #1
Access 2007 Query a list of data from a form user input
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
27-Jun-2012, 01:04 PM #2
If you have multiple "Sold tos" are they in there own Sub table?
larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
28-Jun-2012, 09:55 AM #3
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
File Type: zip Example Relationships_Form.zip (37.6 KB, 23 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
28-Jun-2012, 12:03 PM #4
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?
larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
28-Jun-2012, 01:23 PM #5
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?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
28-Jun-2012, 01:32 PM #6
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.
__________________
OBP
I do not give up easily
larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
28-Jun-2012, 02:36 PM #7
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
29-Jun-2012, 04:42 AM #8
I have posted example databases and example code, some use queries and some use Form Filters, which would you prefer?
larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
29-Jun-2012, 09:00 AM #9
I think form filters is more the route I am trying to go. Thanks.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
29-Jun-2012, 10:29 AM #10
OK, here is a link to a post where I attached Allen Browne's Search2000 example database at post #10, it uses a subform to display the data.

http://forums.techguy.org/business-a...ss-search.html
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access 2007, form, query

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑