Yikes, I’m in over my head again. Short version of the long story – I have moderate Access skills (setting up fairly complex tables, forms, queries and reports) but not anywhere enough skills to be considered an Access programmer / designer. But what little I do know compared to those around me astounds them, so they hand me all sorts of projects. I’ve tried telling them that I am on very shaky ground. My favorite analogy is that of someone who took high school Spanish. Sure, he sounds impressive to his friends, but that does not mean he can act as a translator. They never listen, do they?
Anyway, here is the latest thorn in my side. This should be simple, but after a few hours of searching the net, help files, etc. I can’t find what I’m looking for. Here we go –
How do I move a complete record from one table to another?
I have two tables Active Employees and Terminated Employees. The table designs are identical. They are related by their primary key Employee Number. I have an edit/update forms based on each of the tables. I want to place a button on each form that will transfer an employee’s information from one table to another. That is, on the Active Employee Edit Form, I want a ‘terminate’ button that will cut the record from the Active Table and Past it into the Terminated table. Because of mistakes or rehires, I need to put the same type of button on the Terminated Edit Form.
I can do this manually, but I need to set up the form so that anyone (i.e. my boss) can use it without knowing squat about Access. I apologize if there is an easy answer out there (which, actually is what I’m hoping for) but I just haven’t found it. Please help!
Thanks!
Rhythm (using Access 2000 on an NT machine)
(If it makes a difference, I split the employees to the two tables because of differences in forms and reports. It was cumbersome to keep adding [term]=yes/no to each one. Also, there are different calculations based on the employee’s status, and the nesting of iifs was becoming problematic, especially when changes needed to be made.)
Hmmm…simplest way? Create an append query that will take the value from the current form, and paste it into the other table. Then it’s nothing to delete the current record.
Create a query, change it to an append query. In the criteria field, put in the field on the form that it can use to give a specific record. Such as Forms![tblExercise]![exerkey] where [exerkey] is the field that has an unique value. Be sure to test this with a copy of the database, not the original. Once you have this part, all you have to do is run that query from a button, and add in a docmd.deleterecord after the docmd.runquery and it should work…if you like, let me know and I’ll send you a little sample database, showing you exactlly how to get it to work. Although someone else may have a better solution. Good luck
I think it would be far simpler to have the records in the same table. The different forms and reports should be based on different queries that specify in the criteria whether the Terminated field is True or False. Then your boss can simply change the value of the Terminated field instead of moving records from one table to another.
Rhythmdvl, if you insist on keeping the data in two separate tables, then you should have your command button execute these 2 SQL statements (or queries) in sequence:
{query1}
INSERT INTO [TerminatedTable]
SELECT *
FROM [ActiveTable]
WHERE ([ActiveTable].EmployeeNumber)=“theNumber”
{query2}
DELETE *
FROM [ActiveTable]
WHERE ([ActiveTable].EmployeeNumber)=“theNumber”
Of course, replace “theNumber” with the actual number, and switch the order of the table names to delete from the TerminatedTable and add to the ActiveTable.
Let me know if you are unsure how to program the command button to execute these statements. However, I still maintain that you would be better off keeping all the data in one table if the field names are identical.
I feel your pain! How about a macro using the macro builder? Something like:
Edit
Cut
Close form (active employees)
Open form (terminated employees)
Go to new record
Edit
Paste
Considering the early hour of the day and my current lack of caffeine, I might be missing a few steps (or be totally out in left field).
Make a copy of your database and plunk around with it until you’re sure it works, then copy and paste the macro into the real database once you have it right.
What you really need to do is merge the two tables back into one as hardcore suggested. Create two queries, one for active employees and one for terminated employees:
qryActiveEmps:
SELECT * from Employees WHERE Term = False;
qryTerminatedEmps:
SELECT * from Employees WHERE Term = True;
Then base your forms and reports on one of the two queries. Now you don’t need to worry about copying records back and forth, you just set the [Term] switch.