Re: Q: changing Table in form



"John W. Vinson" wrote:

On Wed, 7 Nov 2007 08:46:01 -0800, Mark <Mark@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

I have a form in which I want to put the results of a query. Normally, I'd
create another form using the autoform: data*** wizard and then attach that
subform in my main form.

However, this time, I have a drop down of a list of tables names and
depending on which table name the user selects, I want to show the data for
that table. Note, these tables are not bound to the form. The form itself is
not bound to any tables.

How would I go about doing that? I don't want to use docmd.opentable,
because it opens up another window.

Thanks much!
-Mark

Whoa. Your database design is off in the wrong direction!

Having multiple tables with the same structure is NOT a good idea. How will
the users remember tablenames? Much better would be to have ONE table with an
additional field indicating which subset of the data each record belongs to.

You can - if you wish - use a bound table, and change its Recordsource
property to a different table name. But I still think you're on the wrong
track!

John W. Vinson [MVP]


Hi John,

Thanks for the response. No, not the same structure. Totally different
tables. Maybe some background will help. My department has a set of reports
that they pull from a system, create an Excel spread*** and email to
various people. I created a template db that automates most of those tasks
and just requires them to enter in some basic information.
The first table (SOURCE_TASKS) has the source data filenames and the tables
in Access they are imported to.
The second table (UPDATE_TASKS) has a list of update queries to run
The third table (EXCEL_COPY_TASKS) has the list of tables/queries to copy to
an Excel spread***
The fourth table (ADHOC_TASKS) has a list of ad-hoc cell updates
the last table (EMAIL_TASKS) has the list of people to email/cc.
Each table has a TASKNAME that categorizes what steps belong to which tasks.

I separated them because putting those tasks in one table seemed to
overcomplicate things.

So, what I want on the form is for the user to be able to be able to select
any of those tables and add/remove/change records depending on the task
they've selected. For example, if they want to change the people to email,
they'd select the EMAIL_TASKS table and update the data. If they want to add
an update query for the task, they'd open up the UPDATE_TASKS table. I can do
that by having the user select their task (the TASK table) and then select
which admin table to update, but I'm using a docmd.opentable, which opens up
the table as another window. What I'd like to do is, when the user selects a
table to update that the table shows up inside the form.

Hope that makes things a bit clearer.
.

Loading