Using Tables In Access

[newuser]

I've created a database in Access to record customers who qualify for a special program. Their eligibility needs to be reverified yearly. Therefore, I have a click box in the CustomerTable if they are removed from the program. I don't want to immediately remove them from the database because I have a monthly report which requires a total of all customers who were removed from the program by month. (My table also includes a Removed Date)

I'd like to query my CustomerTable for anyone who has been removed from the program and put those records in a separate table named CustomersRemovedTable. I know how to write an append query, however, I want the record that I put in CustomersRemovedTable to then be deleted from CustomerTable. (I need the customers who were removed from the program moved out of the CustomersTable because letters are automatically generated to active program customers, based on the records in CustomersTable.)

I'd like for the record to move into CustomersRemovedTable and get deleted from the CustomerTable in one step because several people utilize this database, and many are not very computer savey. (I thought I'd add a button to the main screen to "Move Customers Removed from Program out of Main Table.&quot

Does anyone know how I can populate the CustomersRemovedTable while deleting the record from CustomerTable?

Thanks!!!!!!

 

[Brambojr]

Alrighty, I'm not sure how familiar you are with Access, so I will take the simple approach and hope I don't insult your abilities if you are more experienced.

You need to either put together a macro or insert some code into a form. Easiest way to explain it is by taking the macro road.

Create a new macro. For your first line you will want to start up your append query (OpenQuery - select the query at the bottom). Second line is the delete query. I'm assuming at this point that they will both append/delete the same record.

If this works the way you want, then you'll likely want to insert two more lines - one before evertyhing else, and one after the queries have run. The first line will SetWarnings = False, the last line will SetWarnings = True. This will prevent the annoying pop up messages and anyone answering the questions wrong.

Regardless of whether you want to use a macro or code, you WILL need an event that triggers this process. Perhaps it's as simple as a button to remove someone from the list.

If it's the macro solution, create the button on the form of your choice, right click on teh button and go into Properties. Events. OnClick. Macro. Select the macro from the list. And you should be done.

If you want to insert the code you can have Access convert your macro into code, then you can copy and paste it into the module for the form. How?

To convert the macro slect it in access and the comand is buried in the toolbar - not sure exactly where but you can find it if you know to look. Go to the modules and open the module it creaeted. Select all. Copy.

Now, go to your form and right click on the button - go to the properties, event, onclick. This time select Code. Copy the text into the window. After a little tweaking you'll have it good to go. Brambojr

 

[Guest_imported]

Thanks Brambojr. That worked great!!!