List and Select Tables

[sdamlo]

Hello,
I have a question that may be simple, but I can't seem to find where to start. I have a form I am creating that a user will be entering data into. They enter 15-20 records a week and each week will be entered on a new table.

What I am trying to create on the form is a list of my created tables and then having the user be able to select a table off of the list. By select I mean having the form data source be that selected table so they view/edit data on that table.

I do have a combo box that is running the query:

SELECT MsysObjects.Name AS
[List Of Tables]
FROM MsysObjects
WHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*")


AND ((MsysObjects.Type)=1))
ORDER BY MsysObjects.Name;

And that list all of my tables but how would I edit the double click (or single Click) to have that selected table become the data source?

Thank you

[boblarson]

They enter 15-20 records a week and each week will be entered on a new table.

I hope you realize that is a very bad, bad thing. You shouldn't be using a new table for each week. That is horrendous as far as databases go because then getting data back out (and as you are seeing for entry) it is not fun trying to get it working for data entry.

So, WHY do you have to have a new table each week? What is the purpose? How can that be useful?

[sdamlo]

Each week of 15-20 entries is sent to a Word Doc for mail merge. Sometimes they have to reprint out the merge for the week and don't want every entry, just the last weeks one. So they will select the week's table they want to mail merge again.

If there is a better way of doing this please let me know.

[boblarson]

Each week of 15-20 entries is sent to a Word Doc for mail merge. Sometimes they have to reprint out the merge for the week and don't want every entry, just the last weeks one. So they will select the week's table they want to mail merge again.

If there is a better way of doing this please let me know.

You can use a query for the merge instead of a table.