General discussion

Locked

Access - Moving records...

By jgilbert ·
At a button's click, I need to be able to move a single Access 2000 record from one table to another. For example: move record from "Active Data" table to "Expired Data" table. Also, this is a simple local DB, nothing fancy.
Thanks in advance!
Jen

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access - Moving records...

by howardhenderson In reply to Access - Moving records.. ...

Technically, you can move a record from one table to another in a couple of mouse button clicks by cutting and pasting. That obviously isn't too elegant and I imagine that isn't what you meant.

Depending on exactly what you mean by 'button's click', the simplest way would be to create an SQL query that selects the record, inserts it into the new table, and then deletes (or flags it deleted) the record from the original table.

Another way would be to add a command button to an Access formand build a VBA event behind that button to do your processing.

Purely based on your example of Active/Expired, you might also want to consider whether you even want to 'move' the record. In other words, why not just have a field in your table that is an 'active or expired' field? Of course, you would need to adjust your reports to ignore expired data as appropriate.

As an old database hand, I am always reluctant to actually kill records! You'd be amazed how handy the audit trails can be from 'dead' records...

Hope this helps,
Howard H.

Collapse -

Access - Moving records...

by jgilbert In reply to Access - Moving records.. ...

Somewhat helpful but a bit vague. Help, send code! SQL or VB

Collapse -

Access - Moving records...

by jgilbert In reply to Access - Moving records.. ...

Point value changed by question poster.

Collapse -

Access - Moving records...

by howardhenderson In reply to Access - Moving records.. ...

Well, neither SQL or VBA is really anything to be explained in 1930 lines or bytes or whatever.

To send an example probably wouldn't help a lot, since it is entirely dependant on your table/field names and the way they are linked.

Plus, a person needs to be very cautious when testing 'delete records' code. Depending on how elaborate your data structure is, a couple of unruly delete commands can sure wreck your database.

Probably the simplest thing in your case would be to just add afield to your table that takes a yes/no value and represents 'is this record active or not?'. That effectively would accomplish the same thing as moving a record from an 'active table' to a 'expired table'.


Hope this helps,
Howard H.

Collapse -

Access - Moving records...

by jgilbert In reply to Access - Moving records.. ...

Cool, thanks. I'll consider that. Sometimes the simple way is the one that works the best.

Collapse -

Access - Moving records...

by jgilbert In reply to Access - Moving records.. ...

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums