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.

 

[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

 

[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...