Move record to a different form/table based on event?

Question

Here is the case I am struggling with: I need to find a way to move a record into a different table/form based on a given event. In my example, I have a Main Form (dashboard) which is used for reporting purposes; it contains ID, Agent, Date_Registered, etc. I have another form called Registration_Closed (with fields: ID, Registration_Closed, Date of Closure, Reason). Registrant with ID 2021, for example, exists in the Main form; The registration needs to be closed and I create a record for ID 2021 in the Registration_Closed form. How could this automatically remove the record for this ID from the Main Form?  And then, I would need to have another table to store the closed records, keeping all their info from the Main Form ...Any ideas would be greatly appreciated!!!

Answer 1

Unless the number of records is excessively large, I would keep them in the same table. Add the Registration_Closed, Date of Closure, Reason fields to the original table.

On the form, keep the controls bound to Date of Closure and Reason hidden unless Registration_Closed is True.

You can filter the form to display the 'active' records (Registration_Closed = False) or the 'closed' records (Registration_Closed = True)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

1. Copy the table that contains the records for the main form and re-name it something like 'ClosedRecords'
2. Open the new table 'ClosedRecords' and delete all records
3. Open the new table 'ClosedRecords' in design mode
4. Right-click the Primary Key symbol (if there is one) and remove the Primary Key symbol (copied from the original table) and change its Data Type to Number (changes it from a Primary Key to a Foreign Key)
5. Create a new Primary Key field in the ClosedRecords table. Name it ClosedID with a Data Type AutoNumber
6. Create an Append Action Query that appends ONLY THE ACTIVE RECORD on the main form to the new 'ClosedRecords' table. Name the query 'AppendClosedRecords' when you close the query after its been created
7. Create a command button on the main form. Type 'CloseRecord' in its Name property and type 'Close This Record' in its Caption Property
8. Create an Event Procedure in the buttons On Click event
9. When the VBA window opens with the buttons On Click Private Sub procedure, type the following in the Private Sub (which will Append the active record to the ClosedRecords table and delete it from the original table):

       DoCmd.OpenQuery "AppendClosedRecords", acViewNormal, acReadOnly

       DoCmd.RunCommand acCmdDeleteRecord

Next you will want to create some test records in the main form. Then select the test records one-by-one and click the 'Close This Record' button. Make sure the records were added to the ClosedRecords table and deleted from the main form.

Answer 3

Thank you for this Hans! Number of records is not large at all, but I would rather keep a separate table for the Closed Records; the reason is that there are other events for which I need to create similar functionality, like Records On Hold, Archived Records, etc. and the Main table would become very wide if I add them all! But I see the logic and might as well default to this if I am not able to find another solution.

Best regards!

Answer 4

Thank you Lawrence! This sounds to me like the solution I am looking for! I will go ahead and try to implement the steps - thank you for the detail which would really help! There is one Step, however, that I am not sure how to do as I have never done before: #6. Create an Append Action Query; I will try and look it up but if you could share how to specifically go about it, I would appreciate it!

In any case, I will start following the procedures one by one.

Best regards!

Answer 5

Here are the procedures for creating an Append query:

https://support.office.com/en-us/article/Add-records-to-a-table-by-using-an-append-query-98A5BD66-2190-4243-9638-8EF649CF3596

By the way, I agree with Hans that, unless you absolutely have no other choice, use a single table. You can easily add record status True or False fields that could be for Open, Closed, On Hold etc. You would just add an option button control on your form. You could add as many record status options as you wanted. And it would also prevent a single record having from two status indicators at the same time. Best of luck.

Answer 6

In general, it will be much better to have a single table, maybe called Registrations, with an integer field Status that has defined values 0=Open, 1=Closed, 2=Hold, 3=Archived.  You could also have a field StatusDate to hold the date/time the status of the record was changed.  If necessary to record the status history of a record, so as to know when the status was changed from open to hold, from hold to closed, from closed to archived, etc., then you would need a separate table with one record for each status change. It is better not to move data from table to table when the data itself doesn't change, only an attribute (such as status) changes.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 7

You should not move the record to another table.  As 'closed' is an attribute of the accounts entity type, this would be encoding data as table names.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

You simply need a Closed column of Boolean (Yes/No) data type in the form's table.  As the form's RecordSource use a query where the Closed column = FALSE.  The form will then show only the open accounts.  Close accounts can easily be retuned by means of a query where Closed = TRUE.  Addtional columns in the table would record the reason for closure etc, but not be shown in the 'open accounts' form.  A table level validation rule would allow these columns to have values only if Closed = TRUE.

You might like to take a look at DeleteDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

In the form to 'delete a single record' is an option to 'mark as deleted'.  In the 'deleted' is analogous to 'closed' in your case.   Another form shows records 'marked' as deleted, and includes a button to the restore the record.  This can also be done from the 'delete a single record' form, in which case multiple records can be restored simultaneously by means of a multi-select list box in a dialogue form.

---

Ken Sheridan, Stafford, England

Answer 8

..............but I would rather keep a separate table for the Closed Records; the reason is that there are other events for which I need to create similar functionality, like Records On Hold, Archived Records, etc. and the Main table would become very wide if I add them all! But I see the logic and might as well default to this if I am not able to find another solution.

In that scenario you should have related table along these lines:

AccountStatuses
….AccountID  (FK)
….StatusID  (FK)
….StatusDate
….Additional columns*

The current status of each account is represented by the row with the latest (MAX) StatusDate value per AccountID value.  The primary key is a composite of AccountID  , StatusID and StatusDate.  The StatusID column would reference the primary key of a Statuses table:

Statuses
….StatusID  (PK)
….Status

The AccountStatuses table models the many-to-many relationship type between accounts and statuses .  *Additional columns in this table would each represent attributes of the relationship type, e.g. the reason for the status.

With this model, you have a set of correctly normalized tables, and all data are represented by values at column positions in accordance with the Information Principle.

---

Ken Sheridan, Stafford, England