Moving Data from 1 Table to Another

[cleg7700]

Hello I actually have a couple questions.

1. Is there an easy way through VBA to move a complete record from one table to another. I have data that expires and when it is I would like to press an "Archive" button and have all data that is expired moved to an Archive Table.

2. Also I would like the expiry dates to change colors as they approach expiry. I currently have set the conditional formatting to Red if they are expired, but I am having an issue figuring out how to display yellow if within 0 to 30 days, yellow if between 30 and 90 and green if between 90 and 180.

Thank you in advance for any possible help!

[HansV]

1) Moving a record requires two steps:
- Use an append query to append the record from the "Active" table to the "Archive" table.
- Use a delete query to delete the record from the "Active" table.
You can run these queries manually or from VBA:

CurrentDb.Execute "INSERT INTO tblArchive SELECT * FROM tblActive WHERE RecordID = " & Me.RecordID, dbFailOnError
CurrentDb.Execute "DELETE RecordID FROM tblActive WHERE RecordID = " & Me.RecordID, dbFailOnError

Replace tblActive and tblArchive with the relevant table names, and RecordID with the name of the primary key field. The above lines of code assume that you select the record to be moved on a form.

In most situations, I would prefer not to move a record to an "Archive" table. Since you already have an expiry date field, it is easy to distinguish expired records from active records. You could create a query that only selects active records, and use that as record source for forms and reports.

2) You can create rules with "Cell value", "less than", and respectively Date(), Date()+30, Date()+90 and Date()+180, each with a different color:

S341.png

[cleg7700]

Thank you! I had the conditional formatting reversed.

I would prefer if the archive was done through an IF statement once an Archive button is pressed. I used IF me.ExpDate < Date Then.....

Would that work?

[HansV]

Yes, that should work.