Populating a list box with table names

[Ascentient]

I have a report that generates haul charges based on a couple of calculations. No problem there.

However, the data is split into tables for each month. The user would like the ability to select multiple months to generate this report on.

I currently have a macro that is running an append query to a table I created. The macro contains info to open my table, clear it out, and then append all three months worth of data into it. (Well its actually filtered down, but its not important at this point.) The report is then run based on a date range the user enters from a form.

My first thought was to create a list box and populate it with a list of all tables in the current database. Then I would take the selections the user entered from it and run an sql query to append data from those tables into my table. And then let the user run the report.

I think I can handle writing the sql append statements, but I am not sure how to populate the list box with the table names.

Any suggestions on where to start would be appreciated.

Ascent.

 

[M8KWR]

you can use the following code within a query, and then assign the query to a combo box or list box etc

I hope this helps

SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((Left([name],4)<>"MSys") AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;

 

[Ascentient]

Thanks M8KWR,
That gave me exactly what I needed. I just completed all of the changes and gave the "finished" product to the end user for review.

Ascent.

 

[dhookom]

I can't think of too many uses for showing a list of tables to your users.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Ascentient]

I would typically agree with your statement. I would very rarely show an end-user a list of tables, but for this case their monthly tickets are broken down by month. The Haul Charge report they run can expand multiple months, so they needed an easier way of viewing this information without having to export each table to Excel, and then manipulate the data to get what they wanted.

Ascent.