Exporting Data using Check-Box

[ddaviglus]

We use Access to request information from a different department. Once the other department has completed the request we want to avoid manually transferring the request over to an archive.

I'm trying to transfer records out of one table to another using a button (preferably check-box but whatever works best) on the form.

Once we select the button on the form, we want all the data from that record moved over to a different table (hence removing it from the previous form).


I'm not a programmer, so I hope I've given you enough to work with here. Thank you for the help.

[twinnyfo]

ddaviglus,

Welcome to Bytes!

First, I would recommend not transferring those archived records to another table, but instead maintaining the records as is, but keeping the "Archived" flag. Then, if you need to query any "current" records, you just use as one of your criteria:

Archived = False

Then, you can still query all records (or just archived records). I had a similar set up to your tables a while back, and thanks to some wise guidance on this forum switched to a one-table structure.

Since you are still maintaining the original data, you are actually using more space with two tables, and the task of querying all records becomes unwieldy.

Perhaps this is not a "solution" to your problem, but a recommendation on good DB structure.

If you should so choose to continue with your current plan, we can also assist with a solution in that vein, but I would highly recommend you weigh your options carefully.

[ddaviglus]

twinnyfo,

Quick and thorough response. Thank you.

I may be using wrong terms so I'll give you a bit more info to help you understand my dilemma.

We use a form labeled APPROVALS that contain records with general requests. Once these records are completed in APPROVALS they automatically transfer over to the ARCHIVE form in 14 days. We don't want to wait for that time if the request is completed. If its done we want it out of APPROVALS immediately. We are running into the problem of duplicating work because completed requests sit in the APPROVALS form for 14 days.

So I was asked to add a button, or check-box, something to move the completed request record from the APPROVALS to the ARCHIVE form once the button is clicked. I also need to remove the rule that is already in place that automatically takes the info from APPROVALS to ARCHIVE after 14 days.

Any help is greatly appreciated.

[twinnyfo]

I think I understand your issue exactly. If you had a Yes/No field in your Table called "Archive", then if it was False, it would show up on the Approvals Form. If it was True, then it would show up in your Archive Form.

You merely filter your various forms based on those criteria. This is really the better way to do things, rather than transferring data between tables.

Hope this helps.

[ddaviglus]

twinnyfo,

I have followed your directions and setup a YES/NO field in my original ARCHIVE TABLE.

Now that I've had some more time to learn how the database is linked I have more info to help you understand my dilemma better. It appears the APPROVAL FORM (where I added the CHECK BOX that is linked to this new Y/N FIELD) gets all its records from a Query titled Archive not from TABLE ARCHIVE. How do I complete your suggestion "if False show up in Approvals form and if True show up in Archive"?

I hope Im asking the right question here. I cant understand how to setup the check box to actually exclude a record on the APPROVAL FORM when the check box is selected.

Thanks for all your insight.

[ddaviglus]

I was able to use your advice and fig it out. Thanks twinnyfo!