Moving records

[BobLincs]

Is it possible to move a record from one table to another without physically copying, pasting and cutting.

I.e. we have two membership lists. One for current members one for past members. (It's a history thing)

If I set a current member's status to lapsed, is there a way that the record can be deleted and moved by an instruction.

Those who saw a previous request of mine will know that I'm not switched on about VB so if code is involved can you show how.

Nothing I've read so far suggests that this is possible but as some of you seem to be able to the impossible, I just thought I'd ask.

 

[CEH]

Here's a couple of links... and some thoughts on if you really need to do it that way.... "archive records" vs say an "Inactive" checkbox......

http://office.microsoft.com/en-us/access/HA010345681033.aspx

http://allenbrowne.com/ser-37.html

 

[missinglinq]

The way this is usually done is by having a Checkbox bound to a Yes/No field and ticking it when the membership is lapsed. In the AfterUpdate event of the Checkbox you would requery the form, using Me.Requery.

You would set up your query for the "Active" form to only show records where

LapsedMembership = 0

Likewise, you can have a form to show lapsed membership by having the query criteria

LapsedMembership = -1

In this way simply ticking/unticking the checkbox will "move" the record from one form to the other.

 

[BobLincs]

Thanks, that's a great help.

Bob

 

[missinglinq]

Glad we could help!