Move a record from one table to another upon an event

[Hashirama]

I don't know where to put this. Moderators please move this to the appropriate section if required


I have 2 tables, a and b.
Then I have records in each table with fields ,Name and Status(Done and Done_s)
I want records having "Done_s" to be moved to b whenever the change has occurred. Is this possible?(I have tried somethings with Macros but didn't work)

I am completely new to access. So any help will be appreciated

[burrina]

Hi, I don't use Macros so no help there. But if you want to copy a record from one table to another, why is my question? Duplicating data is a bad idea! Why not instead use a query for your form based on both tables?

HTH

[Hashirama]

If you want to copy a record from one table to another, why is my question? Duplicating data is a bad idea! Why not instead use a query for your form based on both tables?

No. I don't want to copy data. I want to Move it to b.

[John_G]

Hi -

A "move" actually be thought of as two operations: first, a copy from a to b, and then a Delete from a.

When do you want the move to occur - as soon as a user makes the change from "done" to "Done_S"? Not really a good idea, IMO, because what happens if a user makes a mistake - how do you get the record back?

Assuming are using a form and editing TableA, what you want to do could be done with three lines of code in the On_click event of a command button on the form:

currentdb.execute "Insert into TableB select * from TableA where Status = 'Done_s' ", dbfailonerror
currentdb.execute "Delete from TableA where Status = 'Done_s' ", dbFailonerror
me.requery

line 1 copies the records from Table A to table B
line 2 deletes them from Table A once they have been copied
line 3 updates the records you see on the form to remove the moved ones

HTH

John

[Hashirama]

Hi -

A "move" actually be thought of as two operations: first, a copy from a to b, and then a Delete from a.

When do you want the move to occur - as soon as a user makes the change from "done" to "Done_S"? Not really a good idea, IMO, because what happens if a user makes a mistake - how do you get the record back?

Assuming are using a form and editing TableA, what you want to do could be done with three lines of code in the On_click event of a command button on the form:

currentdb.execute "Insert into TableB select * from TableA where Status = 'Done_s' ", dbfailonerror
currentdb.execute "Delete from TableA where Status = 'Done_s' ", dbFailonerror
me.requery

line 1 copies the records from Table A to table B
line 2 deletes them from Table A once they have been copied
line 3 updates the records you see on the form to remove the moved ones

HTH

John

Thanks. I am actually using just a plain table. Do you think a form is a better way?

[Hashirama]

This is what I am trying to do (Done_no is just another option)
It's just facebook page shares, I want to keep track of them all.


A form shows one record only . So I can't use it

[burrina]

You should NOT be working in your tables directly! YES, a form. Tables are for storing data, forms are for entering data. John_G gave you some good advice.

[John_G]

If you are just entering or updating the data directly in the table, there is not a "better" way - there is "no" way. Tables do not have events, and cannot react in that manner to changes. That's what forms are for.

John

[John_G]

"A form shows one record only . So I can't use it"

Not true - it depends on the "Default View" setting in form design. The "Single Form" shows only one record at a time, yes, but "Continuous Forms" and "Datasheet" views allow multiple records to show at the same time, and you can scroll through them.

John

[Hashirama]

Oh, thanks guys. Thank you very much.
But there is no button in the form. It is a drop down list. So what about using "After update" event?

[John_G]

The After Update of a dropdown list would probably work too - what is in the dropdown list, and what (if any) table field is it bound to?

John

[Hashirama]

The After Update of a dropdown list would probably work too - what is in the dropdown list, and what (if any) table field is it bound to?

John

It's {Done,Done_no,Done_s,No,No_s}

{Done_s,No_s} will always go to "In debt" table(My image above)
{Done,Done_no} will go to "Done" table.
{No} will go to "No answer" table

[Hashirama]

Anyone?
.
.
.

[John_G]

Hi -

Yes, the after update will work. The only change needed is that the table a record is moved to will vary according to the value selected, and the criteria to identify the record will change. But, as I said earlier, you'd have to be careful not to make a mistake, because as soon as you change the status, the record is gone from the table you are looking at.

"Done" and "Done_no" won't have to be moved as they are already in the proper table.

Your code could be something like this:

Select Case Status
  Case "Done_s", "No_s"
     currentdb.execute "Insert into [In Debt] select * from [Done] where ID = " & me!ID, dbfailonerror
     currentdb.execute "Delete from [Done] where ID = " & me!ID, dbFailonerror
     me.requery
  Case "No"
     currentdb.execute "Insert into [No Answer] select * from [Done] where ID = " & me!ID, dbfailonerror
     currentdb.execute "Delete from [Done] where ID = " & me!ID, dbFailonerror
     me.requery
  case "Else"
    '
    '  No action required
    '
End Select

One possible pitfall here - if ID is an Autonumber field, there is no guarantee that it will be the same is the tables you are moving the records to ([No Answer] or [In Debt])

John

[Hashirama]

Is there no way to make an ID which is unique to all the tables?