copy table with prompt for new name

[ralphyehle]

Once at the beginning of each month I extract all new cases from last month into a table. The table has a generic name as I repeat this step monthly. I would like to save or archive the data each month without having to manually copy and paste the table and rename it, prior to the new extraction, so that I have a record of each month. The archived table will not be used in any queries or reports.

The table I want to copy and rename is named 'tblSampleFrame'

At the beginning of the month before I delete the contents of the 'tblSampleFrame' to pull the new data, I would like to copy the old table and include a date in the new name. How would I set up a button on a form to copy the file and prompt me for the new file name? The new name would be 'tblSampleFrame_mmyyyy' where I can specify which month and year.

 

[burrcm]

In the on click event of a button on a form based on tblSampleFrame, select Event Procedure and paste this code - DoCmd.CopyObject , "tblSampleFrame" & Date, acTable = acDefault, "tblSampleFrame". This creates a copy of the table with the date attached. Then you can runsql and delete from the original table.

Chris B

 

[neileg]

I wouldn't do it that way. I would have a single table and append each months archive to that tabe. If the month it was archived was significant you could add that as a field in the table. Creating separate tables means you have to know when the data was archived in order to search for it.

 

[ralphyehle]

help most appreciated

The sample frame with date table we create each month is not used again, it is a record we are required to keep for three years. A quality control sample of cases is drawn from the sample frame each month. I like the idea of the button to create a copy with todays date. I can create the file then back it up right away. That makes sense to me. The code seems simple enough not to give me problems. I've been off on another task and will test this next week.

Thank you,

Ralph