Help with a button in a form (1 Viewer)

[methodmza_uk]

Hi guys can some one help me.

I have 2 tables 1 called current 1 called previous

I have created a form to view the details of the current table.

This table has all our current users and when one leaves I want to be able to move them across from the Current to the Previous table. Ideally I would like a button to do this.

Does anyone have any examples of code to do this behind a button.

I am a real beginner with access etc so be gentle.

Much appreciated guys, thanks

 

[Fornatian]

Don't repeat yourself

I haven't visited awf for a while so you must bear with me, but I will try and help but it might be painful.

You have two tables and the only difference between these two tables is that some records are current ('current') and some records are old ('archive'). This is not right because the status of the record is an attribute of the record itself.

If I were you I would create a new boolean (Yes/No) field in both tables called tStatus, which would indicates the status of the record ('current' or 'archive')

Then run an update query against every record in your table of old records setting tStatus to Yes.

Then run an append query adding all records in your table of old records to your table of current records so you have one table of records with a field tStatus showing whether a record is active or not. Obviously don't delete your archive records until you are satisfied they have all been moved over.

In the end, this will make your life simpler for a number of reasons:

1. Queries can be run against one table
2. Updates and corrections need only be made at one location instead of two.
3. It is easier to set and reset the archive status of the field AND reverse it if necessary.
4. It is just better design unless you are talking about millions of records across a network then unloading tables might be a worthy consideration.

Once this has been done continue basing your form on the current table.
Use the Filter property of the form to filter the records you are viewing.

I know it seems like a pain in the a**, but you'll thank me in the long run.

 

[ghudson]

Your data is not normalized based on what you have stated.

You should only have one table and use a Yes/No field to mark if the person is current or not.