MULTILPLE SELECT list box

[ineedahelp]

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!

[MMcCarthy]

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

[NeoPa]

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.

[ineedahelp]

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!!! :)

[ineedahelp]

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:

Private Sub cmbRunSymbolSearch_Click()
    Dim varTable As Variant
 
    For Each varTable In Me.lstChooseTable.ItemsSelected
       Debug.Print Me![lstChooseTable].ItemData(varTable)
       Debug.Print varTable
    Next varTable

Sorry again for the confusion in my first post!

[NeoPa]

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.