How to save previous record in one table and the current record in another table

[Kiranpendy]

I have two tables(main table and history table) and a form for the Main table. The Main form consists of multiple drop downs. I want to track the history of the selections in those drop downs. So when a user makes several changes in the Main form, the previous selections need to go to the history table and the main table should have the fresh selections. I tried using the following code but this duplicates the history table due to the select command. is there a better way?

Code:
CurrentDb.Execute "INSERT INTO History ( Dropdown1,Dropdown2,Dropdown3..,Dropdown40)SELECT Main.dropdown1,Main.dropdown2,.....,Main.dropdown40]FROM Main"

 

[theDBguy]

Hi. On the surface, what you're describing seems suspicious. For example, if you have a series of fields with sequential numbers in their names, it indicates a non-normalized structure. Also, since you say they are dropdowns, it could mean you're using lookup fields at the table level, which is not really recommended. And lastly, duplicating a record between active and historical tables is a topic for another discussion. However, if you're asking how to create an audit trail table, then there's plenty of demos available to do just that.

 

[Kiranpendy]

The form that I have is a checklist with the drop downs and i couldn't post the field names as they are confidential. That is the reason I recreated the code, all the fields are a set of questions in the checklist that have three options "yes,no,NA". I'm pretty new to the Access world.

 

[theDBguy]

The form that I have is a checklist with the drop downs and i couldn't post the field names as they are confidential. That is the reason I recreated the code, all the fields are a set of questions in the checklist that have three options "yes,no,NA". I'm pretty new to the Access world.

Hi. Are you saying the names of your fields are complete sentences?

 

[Kiranpendy]

Yes! It tracks what the user has entered for that sentence.

 

[theDBguy]

Yes! It tracks what the user has entered for that sentence.

Unfortunately, that's a bigger problem for us. Not sure we can provide you with a solution to your question without dealing with your table structure first. If you're creating a survey or exam database, there are some sample demos available that we might be able to recommend. What we don't like to happen is when the questions change in the future, you shouldn't have to modify the names of your fields, which could also mean you'll have to modify your form design.

 

[Pat Hartman]

The actual questions should be text fields and each question should appear as a separate row in the question table.
tblQuestions:
QuestionID
QuestionText

So this schema allows you to have an infinite number of questions rather than the 225 that Access limits you to for table fields. Also a properly normalized questionnaire schema can be used for an infinite number of questionnaires. So, I agree with theDBGuy, it is time to stop and think about what you are doing because your approach is wrong.

 

[Kiranpendy]

Oh okay.I'm gonna try and explain the design, So I totally have six checklists forms and tables and a master table called the project table that consists of the projectid(Autonumber) as a primary key that tracks all the projects. The checklists have their own primary keys(checklistIDs[Autonumbers]) and the projectID acts as a foregin key in all the six checklist tables. So for every project there are six checklists that need to be filled out. The project table has a 1 to many relationship with the six checklist table. My initial intent is to capture the changes done by the user. There are be several changes done to one project in one checklist. The checklist tables capture the current entry because it is a bound form. What i really want to capture is the change from a "no" to a "yes" or vice versa for a particular checklist record

 

[Uncle Gizmo]

What i really want to capture is the change from a "no" to a "yes" or vice versa for a particular checklist record

I'm wondering if what you want is what is referred to as Audit Trail? If that's the case, have a look at this thread:- https://www.access-programmers.co.uk/forums/showthread.php?t=294923 Basically you would do it at the form level for each individual form. If you have a main form, then when something is changed on the main form the name of the form; the control changed; the Old value and new Value are saved in a table. You would use the same code in the subforms and every time a value is changed in a control on a subform again the name of the form, the name of the control and the old and new values would be recorded...

 

[Uncle Gizmo]

I thought it prudent to mention I've done quite a few examples on Checklists.

This short video demonstrates the Check List Problem:- "Checklist Required - Nifty Access"

This blog run through the process of adding a single CheckList to your database:- How to add a Check-List to MS Access

This second blog assumes you might want to create more than one checklists and expands on the single check-list blog into demonstrating how to create multiple checklists:- "add-a-multiple-check-list-to-your-ms-access-database"


This Video shows how to pop up a form to record the users answers in various formats. "Checklist With Multiple Choice"

That video is one of the videos in my blog here:- Check List Extras

The first video:- "Update the Checklist Automatically" Demonstrates how to set up the after update event to fill the subform with data.

The second video:- "Checklist - How to Check ALL" Demonstrates how to check and uncheck all of the check-boxes in one go..

The Third Video:- "Check-list - Serial Number Collector" demonstrates how you can change the checkbox into a text box and collect scanned in bar-codes

 

[Pat Hartman]

Here's another audit trail example:
https://access-programmers.co.uk/forums/showthread.php?t=305482&highlight=audit+trail

But, you really need to rethink your design.you have six tables and forms where you should have only one (two actually because you need one table to define the question and a second to hold the answers) because you are using data to name columns.

 

[theDBguy]

Oh okay.I'm gonna try and explain the design, So I totally have six checklists forms and tables and a master table called the project table that consists of the projectid(Autonumber) as a primary key that tracks all the projects. The checklists have their own primary keys(checklistIDs[Autonumbers]) and the projectID acts as a foregin key in all the six checklist tables. So for every project there are six checklists that need to be filled out. The project table has a 1 to many relationship with the six checklist table. My initial intent is to capture the changes done by the user. There are be several changes done to one project in one checklist. The checklist tables capture the current entry because it is a bound form. What i really want to capture is the change from a "no" to a "yes" or vice versa for a particular checklist record

Hi. Based on this explanation, it may even be possible we don't really want an audit log but more of a historical log. For example, let's say I have a checklist to check blood sugar level or daily weight log. All I would need is the FK to refer to the person being checked, a date for transaction, and the result value. In your case, it could be the ProjectID, the name of the person making the update, the date and time (maybe), and the value (yes/no/na). You can then query this table, group by ProjectID and sorted by the date of transaction to know the latest status of the project. Just a thought...