By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

MULTILPLE SELECT list box

P: 98
I would like to know how to use a mulitple selection list box to (1) select tables (2) pull data from each table using a query (3) and append this data to a temporary table to ultimately be used in a report. My guess is that is will look something like this...

do until "listbox number of items selected"
create query with first table selected
append data to temp table
loop
docmd.openreport.......

Thank you to anyone who can fill in my copious blanks!
Feb 9 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I would like to know how to use a mulitple selection list box to (1) select tables (2) pull data from each table using a query (3) and append this data to a temporary table to ultimately be used in a report. My guess is that is will look something like this...

do until "listbox number of items selected"
create query with first table selected
append data to temp table
loop
docmd.openreport.......

Thank you to anyone who can fill in my copious blanks!
You make it sound so easy. ;)

Truth is what you are talking about I believe is dynamic querying where you allow the users to create their own query though a form. This is actually extremely complicated. My advice is don't go near it. Unfortunately, I won't have the time to assist you with this one as I believe it will get very involved and my admin duties keep me very busy on site. One of the other experts may be willing to help.

However, I would advise ...
  • Think very seriously about tackling this. Is it worth the effort and time?
  • Provide a lot more information than you've done so far about exactly what you are trying to do and why.
    • Why do you need to be able to dynamically choose the table?
    • What is the report for?
    • What kind of data do you need from the tables for the report?
If you still want to go ahead then provide the extra information and if some of the experts decide to help you with this they will probably have a lot more questions as time goes on.

Mary
Feb 11 '07 #2

NeoPa
Expert Mod 15k+
P: 31,492
I am just posting to register with the thread and keep an eye on it.
INeedaHelp,
Mary is right.
You will frighten off any interested expert by asking a question with such scope. You're almost asking for your whole project to be done for you (I'm sure this is not your intention).
Your best way of getting help is by breaking down your requirements into much smaller and self contained chunks (questions). You will also learn more this way I think.
Feb 11 '07 #3

P: 98
I apologize for being so vague. I think what I need is actually quite simple, I just haven't done much with mulitiple selection list boxes. What I am hoping for is after the user has chosen "multiple" tables that I have made available through this statement in the row source of my list box:

SELECT MSysObjects.Name FROM MSysObjects WHERE (MSysObjects.Name Like "LaB*") OR (MSysObjects.Name Like "PAR*") ORDER BY MSysObjects.Name;

I want to run a DO UNTIL loop that will "pull" data from each table if it exists...something like:

Do Until "items selected count" = # of items selected in list
The code I know how to write goes here
Loop

I really only need help with how to loop through the items selected in my multiple selection list box. Sorry for making it look like I wanted someone to write all my code. I'm lazy but not that lazy!!! :)
Feb 12 '07 #4

P: 98
I think I figured it out...for anyone who might have the same question, I used the following code to access the items selected in the multiple select list box:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbRunSymbolSearch_Click()
  2.     Dim varTable As Variant
  3.  
  4.     For Each varTable In Me.lstChooseTable.ItemsSelected
  5.        Debug.Print Me![lstChooseTable].ItemData(varTable)
  6.        Debug.Print varTable
  7.     Next varTable
Sorry again for the confusion in my first post!
Feb 12 '07 #5

NeoPa
Expert Mod 15k+
P: 31,492
It's not that we're upset with you. We're just trying to help you to understand how you will get the most benefit from these forums (and make our life easier at the same time).

About your question :
I'm afraid I think it is anything but simple. Working with multiple tables as you're doing implies that the database structure is actually quite wrong. Similar data should not be stored in multiple tables but in a single table with a reference field in the record added to enable the records to be distinguished from each other (See Normalisation and Table structures).

Thank you for posting your solution. That is an attitude we appreciate. Processing the data though, is more complicated than simply finding the table names from your list. It is possible to do, but is laborious and not for the feint-hearted.

If I'm right, you've progressed in your understanding since you first visited this site (TSDN), so I recommend that you try to get to grips with the link I included.
Feb 12 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.