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!
Moving Data from 1 Table to Another
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Moving Data from 1 Table to Another
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:
- 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:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 17
- Joined: 07 May 2016, 11:57
Re: Moving Data from 1 Table to Another
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?
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?
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands