Q: changing Table in form

[Mark]

I have a form in which I want to put the results of a query. Normally, I'd
create another form using the autoform: datasheet 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

 

[John W. Vinson]

I have a form in which I want to put the results of a query. Normally, I'd
create another form using the autoform: datasheet 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]

 

[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 spreadsheet 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 spreadsheet
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.

 

[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]

Apologies for the double reply, but I think I made a mistake detailing what
I was looking for in my initial response, and maybe ended up making things
less clear.

Let's make this totally hypothetical. Say I have a form with a dropdown that
has values Table1, Table2, Table3, Table4, Table5. Those values correspond to
table names in the database.

If the user selects "Table1", I want Table1 to show up inside the form (in
datasheet view, basically looking like a attached mini-table inside the
form). If the user selects "Table2", I want Table2 to show up, etc. etc.
Docmd.opentable opens up the table in a new window, but I want it to open
within the form.

Hopefully that makes more sense. And again, thank you.

 

[John W. Vinson]

If the user selects "Table1", I want Table1 to show up inside the form (in
datasheet view, basically looking like a attached mini-table inside the
form). If the user selects "Table2", I want Table2 to show up, etc. etc.
Docmd.opentable opens up the table in a new window, but I want it to open
within the form.

I'd suggest using a Form with a tab control with five pages; put a subform on
each page, based on the desired table. You can set the style of the tab
control to use either tabs or buttons, whichever is more comfortable for your
users.

John W. Vinson [MVP]

 

[Mark]

I'd suggest using a Form with a tab control with five pages; put a subform on
each page, based on the desired table. You can set the style of the tab
control to use either tabs or buttons, whichever is more comfortable for your
users.

John W. Vinson [MVP]

Now, that's a darn great idea! thank you so much!

-Mark