I have created a database that reports on the equipment
for military units.  The actual information is downloaded
from an Army website.  The data is imported in a maintable
for the user to edit information via a form.

There's a button on the form that opens a make table query
so the user can export the changed data to a new table,
which the user names, and then the user can go and
download information on another unit which overides the
information in the maintable.

Now comes the part I need a better plan for.  On the form
is a button to open a report.  It opens the report in
design view and I've shown the user how to change the
recordsource for the report, so the user can select which
table (unit) he needs to report on.  I really don't like
this method as it can lead to confusion.  What I'd like to
do is have a "Choose table button" that will list all the
tables in the database and then make the chose table the
source for the report.  

1) is this possible?
2) is it very hard?
3) is there an easier alternative?

Thanks for any asstance

I think the answers to your questions are:

1 Yes
2 Depends (on your skill level and how many other tables are in your
database)
3 Yes

There are various ways of getting lists of all the tables in a database. In
your case if you stick with the current system the you will want to fill a
list box or combobox with the names of the tables so the users can select
the one they want from the list. The way I would do this is to generate a
delimited list of table names and then use this list as the rowsource for
the list box. The code for this is not that difficult. For example the
following function will return a list of all tables in the database:

Public Function GetListofTables() As String

Dim strTableList As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb

For Each tdf In db.TableDefs
    If Not Left(tdf.Name, 4) = "msys" Then
        strTableList = strTableList & tdf.Name & ";"
    End If
Next

GetListofTables = strTableList

db.Close
Set db = Nothing

End Function

You could then use code in the open event of a form to set the row source
type of the list box to "value list" and the row source to GetListOfTables,
and then the form will always show all the tables in your database. Then use
code to base the recordsource of the report on the table selected.

You will have some problems with this method if there are tables in your
database that you don't want listed. The function above already filters out
the system tables by eliminating tables whose names begin with "msys", so
you could use a naming convention to make the list selective.

This answers your first two questions. The answer to the third is that I
think it would be better to store all the downloaded data in a single table
with an extra field to indicate the army unit. (Ideally probably you'd have
a units table and just store the primary key field from the units table in
your equipment table). You would need to use an append query rather than
your make table query, and you'd need to add the extra field. After that the
report problem would be much simpler, and you'd have much more flexibility
in using the data in other ways as well.

On Thu, 12 Jun 2003 10:08:51 -0700, "laura.reid"

Well... actually, the previous step is where you need a better plan. I
would *never* use maketables in this manner! It DOES lead to
confusion; if you think that users will be able to reliably name their
tables and keep track of which name is which unit, you're dealing with
a better class of users than I've encountered!

Why not just append all the data to one big table? The entire list of
servicepeople in the U.S. Army would be a *big* table, but one which
Access is quite capable of handling (a few million rows). Then use
Queries to select the values for a particular Unit, and base the
reports *on the Queries*.

Your "choose table" option is in fact possible with some effort, but I
don't think it's going to be *practical*. If you really want to do it
though, post back.

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

John and Andrew,

Thank you for your replies.  I would classifiy myself as a
low level intermediate user...Unfortunately I know just
enough to make myself dangerous.  I use mostly macros and
whatever VBA I can cut and paste and I know enough
terminology that I can research things I want to do.

For this particular project I have only one user who
doesn't know anything about Access and while the program
works for him, I just see potential for confusion by not
being able to keep track of what table he's looking at.  
As long as I can lock him into using forms and reports
without having to worry about changing properties, he
should be ok.  

Appending all the data into one table is not feasable for
this project.  It would be too time consuming as it's
masses of information and the data changes frequently.  
Therefore would need constant downloading of all units
versus focusing on one unit at a time.  I'm hoping
eventually the keepers of the Web database will allow ODBC
access so I can just link to their tables, but it's
doubtful.  

So I will stick with trying the choose table option for
now. But will keep the other option in mind and chew on it
for awhile.

Thanks again,
Laura