Deleting and transferring records to another table (1 Viewer)

[Ron Mitchell]

Hi. I’m Ron and I’m pretty new to Access. I’ve used Lotus Approach for many years but decided it was about time to move up.
well, Access is pretty powerful and somewhat daunting.

I’m in the middle of transferring a motor club membership database from Approach to Access.

its actually going very well. However I need to hand it over to our membership secretary and want to make it a bit more resilient before I do.

currently, when a member leaves we delete their record and then rekey into the expired database - this is obviously laborious and risky.

I can of course cut and paste in Access but I’d like to be able to automate this via a macro or VBA - I’m not yet as proficient in either macros or VBA as I’d like.

i was wondering if some kind person out there might be able to provide me with a sample macro or VBA code to show me how to do it.

 

[MajP]

Most databases would simply have a field that is "Active" (Yes, No) and then you make a member active or inactive. Then you have to base some of your forms on a query where active = yes. Unless this is a huge DB, it is unlikely you need to move "expired" into its own database.

 

[theDBguy]

Hi Ron. Welcome to AWF!

Actually, the "best practice" approach is to "never" delete a record. Instead, merely "tag" it as deleted. For an employee table, for example, you could add a TerminationDate field, which would indicate the employee is no longer part of the company/organization. You simply use a query to filter them out, if you don't need to see them.

 

[June7]

Don't do that. Have a field that flags member as no longer active. Apply filter criteria to exclude those records from forms and reports. Even better than simple yes/no is a Date/Time field to record date inactivated.

 

[arnelgp]

you can use 2 queries.
one that will insert the record to the expired database.
the other that will actually delete the record.