Transfer records between two Access tables
I put a small database together (attached) quickly to hopefully give enough to illustrate what I'm trying to do.
Please Open Form1 in design mode. You'll see there are 2 subforms on it.
What I'm trying to do is:
1. Display all of the Source Table records in the top subform.
2. Then allow the user to select (one record at a time) any record in the top subform by clicking the select button next to the selected record.
3. Populate the bottom subform (one record at a time) with the selected records from the top subform and have the selected records inserted into the Target table (and thereby displayed in the bottom subform).
4. Have the records (one record at a time) that are inserted into the Target Table deleted from the Source Table (effectively transferring records from the Source Table to the Target Table).
5. Allow the user to delete records from the bottom subform (Target Table) and "repopulate" the Source Table with the deleted records.
6. Suppress messages (such as "you are about to append blah blah blah) when the user clicks the select or delete buttons.
7. Suppress the "# Deleted" row that appears in the subform when a record has been deleted such that all that will be displayed are the records in the table; not any indicators that a record was deleted.
In plain English, what I'm trying to do is display source records to the user (in the top subform). The user will then select records one at a time from that subform to produce a list of source records (s)he has taken care of. That's what the purpose of populating the lower subform is. The top subform is "here is the list of records that need to be taken care of." And the bottom subform is "here is the list of records that you are indicating have been taken care of." As the user takes care of records, I need them removed from the top subform (Source Table). However, if the user makes a mistake and selects the wrong record (which will delete the record from the Source Table and populate the Target Table), I need a way of putting it back (i.e. removing it from the Target Table and putting it back in the Source Table). So I need to, for lack of a better description, be able to "toggle" individual records back and forth between the Source Table and Target Table by using the command buttons next to each record in the subforms.
Thank you.
test-db.accdb
Please Open Form1 in design mode. You'll see there are 2 subforms on it.
What I'm trying to do is:
1. Display all of the Source Table records in the top subform.
2. Then allow the user to select (one record at a time) any record in the top subform by clicking the select button next to the selected record.
3. Populate the bottom subform (one record at a time) with the selected records from the top subform and have the selected records inserted into the Target table (and thereby displayed in the bottom subform).
4. Have the records (one record at a time) that are inserted into the Target Table deleted from the Source Table (effectively transferring records from the Source Table to the Target Table).
5. Allow the user to delete records from the bottom subform (Target Table) and "repopulate" the Source Table with the deleted records.
6. Suppress messages (such as "you are about to append blah blah blah) when the user clicks the select or delete buttons.
7. Suppress the "# Deleted" row that appears in the subform when a record has been deleted such that all that will be displayed are the records in the table; not any indicators that a record was deleted.
In plain English, what I'm trying to do is display source records to the user (in the top subform). The user will then select records one at a time from that subform to produce a list of source records (s)he has taken care of. That's what the purpose of populating the lower subform is. The top subform is "here is the list of records that need to be taken care of." And the bottom subform is "here is the list of records that you are indicating have been taken care of." As the user takes care of records, I need them removed from the top subform (Source Table). However, if the user makes a mistake and selects the wrong record (which will delete the record from the Source Table and populate the Target Table), I need a way of putting it back (i.e. removing it from the Target Table and putting it back in the Source Table). So I need to, for lack of a better description, be able to "toggle" individual records back and forth between the Source Table and Target Table by using the command buttons next to each record in the subforms.
Thank you.
test-db.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I haven’t had a chance to review the attached databases but since there are a couple of comments about there being an easier way to do this, I’ll explain exactly what I’m trying to accomplish. My question has to do with Employee #2’s function only. It should make sense after you read what I wrote below.
From the beginning:
Employee #1 is the one who always creates the records that go into the source table. There is a field in the source table that records Employee #1’s ID so that you’re able to reference who CREATED the record.
Just for illustration, let’s say Employee #1 creates a list of clients’ tax returns that need to be completed and Employee #2 will be the one who actually complete them.
Let’s say Employee #1 enters 100 records into the source table (i.e. created a list of 100 clients’ tax returns that need to be completed).
Now it’s Employee #2’s turn.
All I’m trying to do is present a list of records (in this example the 100 clients whose tax returns need to be completed) to Employee #2. Employee #2 will make selections from the list, one by one, to indicate (or verify) that (s)he has completed so and so’s tax return. The selections do not need to be made sequentially (i.e. don’t need to work from the top of the list down). They can be made in any order. For example, Employee #2 may see that the first record listed is Mr. Johnson’s. Employee #2 from past experience knows Mr. Johnson’s tax return is complicated, so he’ll get to that one later and will work on the next one’s in the list first.
As Employee #2 completes the tax returns (which is a function completely outside of the database), (s)he will mark the ones completed by making selections (one at a time) from the “To do” list. Those selections will be removed from the “To do” list and transferred to the “Done” list.
As each record is marked by Employee #2 as having been completed (again, this is a one record at a time process), I need the source table’s record updated with a marker indicating that it has been completed (I will also be writing Employee #2’s ID to the table to be able to reference who completed the tax return but that’s another story).
Let’s say Employee #2 made a mistake and selected the wrong record. (S)he completed record #2 from the list but selected record #1 by mistake. So, record #1 was removed from the “To do” list and was transferred to the “Done” list. I need a way of reversing that mistake (i.e. removing it from the “Done” list, which updated the wrong record in the table, and putting it back in the “To do” list).
Let’s say on a given day, Employee #2 opens the database and sees 100 records listed in the “To do” list. In one sitting, (s)he completes 50 of them. The “To do” list should now be displaying 50 records and the “Done” list should be displaying 50 records (Actually, every time a record is selected, the “To do” and “Done” lists should be updated; not just when an entire batch has been completed). Let’s say (s)he shuts the database down and opens it the next day. (S)he should now be presented with a list of 50 records in the “To do” list and 50 records in the “Done” list just as (s)he left it the day before. The “To do” list and “Done” list is why I tried using 2 subforms in the database I attached. One to list the records to be completed and one to list the records that have been completed.
To sum up this novel, I’ll I’m trying to do is present a list of work that needs to be done (i.e. records) to the user. As the user completes the work, (s)he will “mark” it as done (and the source table will be updated with indicator that it has been completed) and create a list of “completed work” and the “work to be done” list will be updated (i.e. reduced) as work is completed.
Example:
Step 1: Employee opens the form and sees the following:
To Do list:
Record1
Record2
Record3
Done List:
Step 2: Employee indicates that (s)he has completed Record2 by selecting it from the “To do” list. Expected results right after the selection is made:
To Do list:
Record1
Record3
Done List:
Record2
From the beginning:
Employee #1 is the one who always creates the records that go into the source table. There is a field in the source table that records Employee #1’s ID so that you’re able to reference who CREATED the record.
Just for illustration, let’s say Employee #1 creates a list of clients’ tax returns that need to be completed and Employee #2 will be the one who actually complete them.
Let’s say Employee #1 enters 100 records into the source table (i.e. created a list of 100 clients’ tax returns that need to be completed).
Now it’s Employee #2’s turn.
All I’m trying to do is present a list of records (in this example the 100 clients whose tax returns need to be completed) to Employee #2. Employee #2 will make selections from the list, one by one, to indicate (or verify) that (s)he has completed so and so’s tax return. The selections do not need to be made sequentially (i.e. don’t need to work from the top of the list down). They can be made in any order. For example, Employee #2 may see that the first record listed is Mr. Johnson’s. Employee #2 from past experience knows Mr. Johnson’s tax return is complicated, so he’ll get to that one later and will work on the next one’s in the list first.
As Employee #2 completes the tax returns (which is a function completely outside of the database), (s)he will mark the ones completed by making selections (one at a time) from the “To do” list. Those selections will be removed from the “To do” list and transferred to the “Done” list.
As each record is marked by Employee #2 as having been completed (again, this is a one record at a time process), I need the source table’s record updated with a marker indicating that it has been completed (I will also be writing Employee #2’s ID to the table to be able to reference who completed the tax return but that’s another story).
Let’s say Employee #2 made a mistake and selected the wrong record. (S)he completed record #2 from the list but selected record #1 by mistake. So, record #1 was removed from the “To do” list and was transferred to the “Done” list. I need a way of reversing that mistake (i.e. removing it from the “Done” list, which updated the wrong record in the table, and putting it back in the “To do” list).
Let’s say on a given day, Employee #2 opens the database and sees 100 records listed in the “To do” list. In one sitting, (s)he completes 50 of them. The “To do” list should now be displaying 50 records and the “Done” list should be displaying 50 records (Actually, every time a record is selected, the “To do” and “Done” lists should be updated; not just when an entire batch has been completed). Let’s say (s)he shuts the database down and opens it the next day. (S)he should now be presented with a list of 50 records in the “To do” list and 50 records in the “Done” list just as (s)he left it the day before. The “To do” list and “Done” list is why I tried using 2 subforms in the database I attached. One to list the records to be completed and one to list the records that have been completed.
To sum up this novel, I’ll I’m trying to do is present a list of work that needs to be done (i.e. records) to the user. As the user completes the work, (s)he will “mark” it as done (and the source table will be updated with indicator that it has been completed) and create a list of “completed work” and the “work to be done” list will be updated (i.e. reduced) as work is completed.
Example:
Step 1: Employee opens the form and sees the following:
To Do list:
Record1
Record2
Record3
Done List:
Step 2: Employee indicates that (s)he has completed Record2 by selecting it from the “To do” list. Expected results right after the selection is made:
To Do list:
Record1
Record3
Done List:
Record2
ASKER
Just got a chance to try it. Thank you so much, Gustav Brock. That's EXACTLY the functionality I was trying to achieve.
You are welcome!
/gustav
/gustav
Apart from this, and talking as is, remove link fields from first subform.
Any way here is a modified version, and explain the requirement starting from the records displayed.
test-db-2.accdb