Access 16, how to backup table from selected dropdown data

[akika]

Hi,

i have an access form 2016 for backup of data maintenance, i have a drop-down fields which contains the years (2005 to 2025).

When user select e.g 2018 from the list and click on backup data button.
then all data for 2018 from the maintable should move and append to table maintable_backup. The year selected from the dropdown should be appended to the new table name. Date is in format DD-MON-YY, 7-Jun-18.
there is only 1 table and no other tables associated with it.



How can i do it pls??

[June7]

What field has the date value? Is it a date/time type?

Why copy records to another table? Why don't you just make a backup of the database file?

[isladogs]

Agree with June7. Why create a separate table?
When you need data for e.g 2018 just create a query and filter for those records.

If you really want to transfer these records, Crete a select query as above then convert to an append query to copy the records to a new table you have created already.
Or use a make table query if it doesn't exist.

You should then delete the records from the original table to avoid duplication.
Join the old and new tables and select all fields in the old table by using the .* notation. Run to check this has been done correctly.
Then convert to a delete query and run it.

Isn't the original idea much simpler.

[akika]

yeah inddeed the original idea is simpler...
but i still neeed that the main table should contains only latest data of 2019.. and previous year data shouldnot be in same table but historisied..

Can u pls help on below .. how to include the drop-down values in the insert

ive created an backup button & the backup table same structure.

Private Sub Command137_Click()


On Error GoTo Err_Command137_Click

DoCmd.RunSQL "Insert Into [Customer_backup] Select * from [customer] where [status] = 'Completed'"

Exit_Command137_Click:
Exit Sub

Err_Command137_Click:
MsgBox Err.Description
Resume Exit_Command137_Click



End Sub

[June7]

Didn't answer my other question. What data is in the field - a full date value?

If the combobox lists only year values, maybe:

CurrentDb.Execute "Insert Into [Customer_backup] Select * from [customer] where [status] = 'Completed' AND Year([fieldname])=" & Me.comboboxname

[akika]

field date is of data type date/time. with data format of e.g 1/9/2019
should the delete of original table be included in same query or another one?

Im getting an error when running only the select & insert ...

too few parameters expected 1. Sample file attached. Can u pls help.

[June7]

There is no field named backup_year in Customer, it is Date.
CurrentDb.Execute "Insert Into [Customer_backup] Select * from [customer] where [status] = 'Completed' AND Year([Date])=" & Me.Combo3

You can delete records from Customer with:
CurrentDb.Execute "DELETE FROM Customer WHERE [status] = 'Completed' AND Year([Date])=" & Me.Combo3

The combobox is pulling ID from List_of_Values and that is the value passing to query. Change the combobox properties.
RowSource: SELECT [backup_year] FROM List_Of_Values ORDER BY [backup_year];
ColumnCount: 1
ColumnWidths: nothing

ID field in Customer_Backup should be a Number type, not autonumber.

Should have conditional code so procedure does not execute if no value in combobox.

Date is a reserved word (it is a function) and really should not use reserved words as name for anything.

[akika]

okie thnxs for detail.. i will try it out and let u knw if ok or any issue
Thansk a lot