
Need a Plan to choose table for report record source
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.
Quote:
> 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