Auto-Cut and paste from one table to another

[tobydobo]

Hello,



I may not be asking this correctly but I have a contact db that shows all my contacts in list view. The db contains a list of active technicians. I currently have a drop down that allows me to select their status. When a tech becomes inactive, I want to be able to select "inactive" from the drop down and have it automatically remove that tech's entire record "active" list view and populated into a completely seperate "inactive" table. Is something like this possible?

[boblarson]

Why not just save yourself some problems and just mark the record as InActive? What happens to any records that they have data for? Normally you want to leave them in there but marked as InActive and then in the forms and reports you can continue to display them if there are existing records that need that, otherwise all of a sudden there is a blank spot where there would have been data.

[tobydobo]

When its inactIve i want it to move to another table. The same action as cutting the line and pasting it somewhere else. There wouldnt be a blank line if its cut out completely.

[boblarson]

There wouldnt be a blank line if its cut out completely.

If this technician's ID is on any records then there will be a blank, not in the table, but in any record that is displayed on a form or report unless another technician is put in their place. So what I mean is something like this:

If I have a table of technicians and I have only the active ones in there and I have a table with a form that captures their ID when I assign them to a trouble ticket in my other table which tracks trouble tickets, and then I go back to that trouble ticket in the form after that technician has been removed from the table, then you would see a combo box (where I would select the technician) that is blank - it doesn't show anything, even though my technician is still actually assigned to it but because he isn't showing up in the combo's row source, then he doesn't display on any of the records where he was used.

So the fix is to just mark them inactive in the table and then in the combo box's row source you sort on the InActive field first in descending order and then ordered by name the Active ones will be first on the list and then the inactive ones at the very bottom. You can even mark them as InActive as you can change the name to display with something like **** LastName, so that they are very noticeable which are inactive.

This is what I mean.

[tobydobo]

Well thats sort of what I have now. I have a tech status drop down with conditional formatting that turns the field red when they are inactive, but I dont really like it that way. I want all the records active in the list, and when I select Inactive from the drop down, have cut the line out of the active table and append it to my inactive table. Iv heard such a thing was possible, I just dont know how to do it.

[boblarson]

Okay, if you must.

First an Append query to append the record to the archive table.

Second a Delete query to delete the record from the first table.


But I think you're making a mistake (this is from 14 years of Access experience talking). But you are the one who has to make the call.

[tobydobo]

The way my current db is setup, it will make it easier to use for others on my team with read only access. If you could provide the steps to do the append queries that would be most helpful.

[boblarson]

See if this will help:
http://office.microsoft.com/en-us/ac...010076526.aspx