Moving a record from one table to another one using the button feature (1 Viewer)

[sdsmith]

Hi! I have built two tables (active and archived) and I have created an "active" form. On the form, I've placed an "archive" button on the form that I would like to be able to click and move the record to the "archived" table. It's not allowing me to put in a code and I am not sure what options need to be selected. Right now, when I click the button, It just takes me to my archived query and does not do anything else. What steps do I put in for this?

 

[theDBguy]

Hi. Welcome to AWF!

Can you please tell us first why you decide to create two tables? How many records are in the first table?

 

[sdsmith]

Hi. Welcome to AWF!

Can you please tell us first why you decide to create two tables? How many records are in the first table?

Thanks! And yes, one table is of active records and the other table is of archived records. When an active record becomes obsolete, I have to archive it. But it still has to remain in the database for historical purposes. So I am wanting to just move the record from the active table to the archived table.

 

[theDBguy]

Thanks! And yes, one table is of active records and the other table is of archived records. When an active record becomes obsolete, I have to archive it. But it still has to remain in the database for historical purposes. So I am wanting to just move the record from the active table to the archived table.

Hi. Thanks for trying to explain, but it doesn't help us understand the reason for using two tables. The common (best) practice is to simply have a field in the table to indicate if a record is active or inactive - no need to move inactive records to another table. That's why I asked how many records were in the table.

 

[Uncle Gizmo]

I agree that the best option is to have a special field, a boolean flag to identify a record as archived and just keep to one table.

However it is sometimes necessary to do this, and I did a blog in answer to a similar question to another poster a while back which is here on my website:-

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

 

[sdsmith]

Hi. Thanks for trying to explain, but it doesn't help us understand the reason for using two tables. The common (best) practice is to simply have a field in the table to indicate if a record is active or inactive - no need to move inactive records to another table. That's why I asked how many records were in the table.

Ok, I understand. Yes, with the particular type of report that I'll have to pull from it and how the client wants it to look, two separate tables are necessary.

 

[theDBguy]

Ok, I understand. Yes, with the particular type of report that I'll have to pull from it and how the client wants it to look, two separate tables are necessary.

Hi. Since we are not familiar with your database, that's what it may look like to you, but we can't agree or disagree with it. Like I said, it's possible to distinguish active from inactive records and use a form or report to work with each one, although they are both stored in the same table. Are you using any queries? If so, you can create two of them, one for active and another for inactive.

 

[arnelgp]

review the two queries on the sample db.
see the Code-behind the "Archive" button.

 

[Galaxiom]

Sorry arnelg but this needs to be said.

Whatever you do DON'T use arnelgp's technique. It would be an excellent way to lose or duplicate records because one of queries failed to insert or delete.

To make matters worse he disables Warnings before running the queries so any errors would simply be ignored.

You certainly would not want those queries out in the open like that where anyone might run them.

Anything where two or more actions need to be completed as a group should always be done inside a database transaction so either they all succeed or those that complete are rolled back if any of the others fail. The success of all the steps should be confirmed before the transaction is committed.

Best not done as a process that is simply run as a normal operation by users. Let the users mark them as archived using a flag and hide the from the forms and reports using a query as the RecordSource like others have suggested. Move them to the archive table as an out of hours maintenance process after backing up the database in case anything goes wrong.

I'm still not convinced that the records ever need to be moved to another table until numbering in the millions.

 

[arnelgp]

I'm still not convinced that the records ever need to be moved to another table until numbering in the millions.

see post #6.

 

[Galaxiom]

see post #6.

Ok, I understand. Yes, with the particular type of report that I'll have to pull from it and how the client wants it to look, two separate tables are necessary.

I can think of no circumstance where a report would require two separate tables rather than being based on two queries reading records from the one table. One query would return the records flagged as Active while the other would return the compliment, records not Active.

Updateable queries are interchangeable with tables for virtually every purpose in a database.

The Archive table could probably simply be deleted and replaced with a query by the same name returning the not Active records. The forms and reports should not need to be modified.

 

[Pat Hartman]

Having two tables isn't wrong per se. It does cause unnecessary work though. Generally, people don't use archive tables unless they have millions of rows and really need to get the main table size down. And I agree with Galaxiom. a report would not require two tables. At worst, you could use two queries based on the same table.

 

[Teri Bridges]

Hi. Welcome to AWF!

Can you please tell us first why you decide to create two tables? How many records are in the first table?

I am faced with a similar situation. There are times when a course may be archived. I have a checkbox for the user to select if the course needs to be archived. Is there a way to deactivate all related records when the course archive checkbox is selected? For example, the course may have three lessons, and 6 events associated. I would need these records archived as well.

 

[Teri Bridges]

I am faced with a similar situation. There are times when a course may be archived. I have a checkbox for the user to select if the course needs to be archived. Is there a way to deactivate all related records when the course archive checkbox is selected? For example, the course may have three lessons, and 6 events associated. I would need these records archived as well.

Wait I think I answered my question. If I use a date field as to when the record was archived then in my query I say is null any associated records will not display. Like DUH! I know don't over think it.

 

[theDBguy]

Wait I think I answered my question. If I use a date field as to when the record was archived then in my query I say is null any associated records will not display. Like DUH! I know don't over think it.

Glad to hear you got it sorted out.

Likewise, if you stuck with the original Yes/No field you had, you could also simply use True or False in your query.