Archiving old records through a form

[andreas_udby]

I'm not even sure which forum to put this in, as it involves a form, two tables, some queries, and possibly a macro. I've poked around the message boards here and haven't quite found a method that works for my situation. So here we go!

I have a recruiting database that I've developed for some other users in the office who are not Access builders. They have asked if there is a way to archive old records into another table for safekeeping, which I can do through the database window, but which they probably couldn't. I want to give them the ability to tuck these records away as needed, and without them having to ask me to do so every time.

The main table in the DB is called tblPosition. There is a form called frmPositionManagement which is bound to this table. It's through this form that the recruiters make all their updates and whatnot to records stored in tblPosition. I also have a table called tblArchive which I have created using the exact same fields as tblPosition so I can store the outdated records there.

I'm looking for a programmatic way to do this. I was hoping to put a button on frmPositionManagement that would let the user remove that record from tblPosition and send it to tblArchive. I had figured on creating a macro (mcoArchive) that took the following steps:

* MsgBox "Are you certain you want to archive this record?"
* Run an Append Query to add the selected record to tblArchive
* Run a Delete Query to remove the record from tblPosition
* Close the Append Query
* Close the Delete Query

Then I started building those two queries and the macro, and the wheels fell off my brain.

The difficulty I'm having is getting the system to say to itself, "The record currently displayed on the frmPositionManagement is the one I need to append and delete." I have a specific record selected on the form, so how do I pass that record's unique ID through to the two queries to make this record the one which is appended/deleted?

I had tried setting the criteria for the unique key in the Append/Delete queries to =[frmPositionManagement]![AutoID], but that just led to a pop-up box that asked me to input frmPositionManagement!AutoID, which is not what I wanted at all.

My VB isn't all that great, so I was trying to stick to macros, but if someone has an idea for a programmatic solution for this conundrum, I'd appreciate hearing it!

Thanks,
Andreas

 

[KeithG]

You are referencing the form incorreclty in your query. try the below

forms![frmPositionManagement]![AutoID]

 

[Simon_MT]

I have a similar problem with "high value stock" and I used to Archive these records but going back to them (differing tables) was a real pain. So I asked myself why have separate tables at all? It was so much easier to flag the records with "C" for current and "H" for History. From the Recruiters point of view, it doesn't really matter how the records are tucked away. Current and History programmatically is straightforward, your only consideration is when to show Current or Archive records.

You then decide when and what to (bulk) archive yearly but allow the users archive individual positions, with a button to Archive and back again (in case of an unintended archive.

This enables the recruiters to see recent positions but if you looking from a Clients perspective ALL positions can be shown. I go even further on the Client and have a separate tab for Current Positions and Archive.

Simon

 

[andreas_udby]

Thanks for your help, KeithG! That did it.

I can appreciate the beauty of your solution, Simon. I was thinking about trying something like that, except I have my form bound directly to the table and I couldn't think of how to set something like that up. The way I came up works just fine, though.

My mighty macro looks something like this:

* MsgBox: "You are about to archive this record. Push "No" on the next pop-up box to cancel this action."
* OpenQuery qryArchiveAppend
* OpenQuery qryArchiveDelete
* Close qryArchiveAppend
* Close qryArchiveDelete
* Close frmPositionManagement
* OpenForm frmPositionManagement

I had to add those last two because otherwise every text box on the form would display #DELETED until I closed and reopened the form. So I built that into the macro; doesn't slow things down at all.

Thanks for listening to my mad ramblings!
Andreas

 

[Simon_MT]

1) You should have your Form frmPositionManagement linked to a Query instead.
2) When you re-open the Form what record will be displayed?
3) What happens if a Position is accidentally archived?
4) Will you have to drag out of the archive?
5) Archived records how will they be accessed - another Form?

To me Queries are the best thing about SQL not only for Actions and Reports but for Forms as well - once you have created your Query:

frmPositionManagement Record Soruce is qryPositionManagement rather than tblPositionManagement

The point about the the Flag is even in a macro (I use VB so forgive the syntax) its dead easy:

SetValue [StatusFlag] = "C" [StatusFlag] = "H"
SetValue [StatusFlag] = "H" [StatusFlag] = "C"

For what its worth, your initial thought is a much better idea and so simple to implement.

Simon