Copy one row after checkbox is checked from one table to another

DarkBlue140

New to using VBA in MS Access, please be nice.

When the user click's a button, I need the button to move only that row onto an existing table.

Example, something needs to be recycled, user clicks the recycle button and it moves that row of data to a recycle table that holds all the recycled rows.

I've spent 2 days working on this new database for work and this is the last portion that I need to finish, any help would be much appreciated.

3 Weeks Ago #1

isladogs

IMHO, that's not a sensible approach.
You should not need to move data from one table to another.
Instead, if you want to deactivate a record, this is easily done using a boolean (Yes/No) field which could be called Active.
Set the field True by default.
When you want to 'recycle' a record, just set the Active field = False

3 Weeks Ago #2

ADezii

I totally agree with isladogs in that your approach is not a sensible one. That being said, there are some circumstances under which an unorthodox approach is warranted, or dictated by higher ups. If this is True, then a couple of questions to clarify.

1. On a Form, you wish to click a Recycle Button that will copy the Current Record to a Recycle Table, then DELETE it?
2. What is the structure of your Main Table (Record Source for the Form), Field wise?
3. Does the Recycle Table contain the same Fields as your Main Table, and if not, what are the Field alignments between the two?

3 Weeks Ago #3

NeoPa

As has already been hinted at - it seems you're designing simple attributes into separate tables. Newbie? Maybe that needs some explanation.

Databases aren't human and thinking about data should not be done simply as a human would see & understand it. Set theory is what underpins database technology, so it makes sense for your designs to be based on the understanding of sets.

Tables are for similar items - not for different statuses. The status of an item is simply an attribute of that item. It doesn't make it into a different item. In the same way when something is flagged for recycling than a simple Recycle attribute (or Field as we use in databases) in your table designed for that type of item, just changes its value.

You may find a perusal of Database Normalisation and Table Structures to save you a great deal of time later on.

3 Weeks Ago #4

DarkBlue140

So the recycle field is in fact a boolean, currently it just checks off the box but doesn't remove the record. I was trying to use some VBA on the field so when it's clicked, that row of data would be removed and moved to a different table, but from what you're saying that isn't viable. What would you recommend?

2 Weeks Ago #5

isladogs

If the question is aimed at me, I never said it wasn't viable. Its perfectly possible but not a good idea

My point is that its not a sensible approach to remove 'deactjvated' records to a different table
The boolean field Recycle is fine. Default value=false and ticked if records is for 'recycling'

Once ticked, leave the record(s) in the same table.
You should just use a query or sql statement for your form record source to only show records where the field is false.
If you need to view the recycled records instead or indeed all records, this is easily managed using code from option group choices or button click events

2 Weeks Ago #6

DarkBlue140

Ah ok, that makes more sense isladogs, thanks for the feedback from everyone.

2 Weeks Ago #7

isladogs

Glad to have helped.
One of my biggest mistakes over 20 years ago was moving all student leavers to a separate leavers table and all associated data e.g. Attendance, academic grades, reports etc etc to associated tables. By the time I'd realised my mistake, it would have been far too much work to reverse.

However it meant, from that tome on, I had to ensure all tables maintained the same structure whenever any field were added/modified/deleted together with corresponding queries and code. Over the years, that decision added hundreds if not thousands of hours to development time.

I'm glad to have helped you avoid making a similar mistake.

2 Weeks Ago #8