Find record by autonumber and move record to different table

[DanJames]

Hi,

Any ideas on how I can move the current record on the form, from one table called "currentclients" to another table called "cancelledclients".

The two tables have the same structure and the autonumber for "currentclients" is client-id. I have client-id in "cancelledclients" but with the format number.

If you know the VBA to move the selected record's row from "currentclients" to "cancelledclients" it would be great!

Thanks in advance,
Daniel James

 

[MarkK]

The status of a record is data. The easiest way to handle your situation is add a field called IsCurrent to your table and set its value to false for a given record.
Then you can write a query called qCurrentClient that looks like ...

SELECT * FROM tClient WHERE IsCurrent

...and for cancelled clients ....

SELECT * FROM tClient WHERE Not IsCurrent

Generally you have a design problem if you need to move records. A far easier solution is always to edit a record's attributes.
Cheers,
Mark

 

[DanJames]

Thank you Mark,

It has worked perfectly!

Cheers,
Daniel