Update previous records in table when pressing save button on form

[kevint57]

Hi all,

A bit of background first.
I have a table that I keep all the records for training that staff have undertaken.
Among other fields in the table, the main ones are - Surname, First_Name, Element, Date_of_Training, Entry_Date (ie data entry date), Current_Training (which is a yes/No field) and Revision_Date. The table is called tbl_Training.

I also have a form that I use to enter new records. This form has a Save button in it. The form is called frm_Training_Entry_Form.
I also have 2 more forms.
1 form is used to extract ALL the training an individual has undertaken including any refresher training.


The 2nd form is used to only extract the latest training for all staff in a certain Element.

What I want to happen is when I hit the save button on the frm_Training_Entry_Form it should update the Current_Training field automatically in the tbl_Training table to No for all previous records for that person, providing the Surname, First_Name and Element are the same as the new record. I don't know how to do this.
In other words, I only want the 2nd form to display the latest entries for each person for each Element.

If you could provide a solution to my nightmare I would much appreciate it.

Cheers,

Kevin

[ArviLaanemets]

Write a saved query to calculate latest Date_of_Training for every person. (Btw. create a separate table to register persons like tblPersons: PersonID, First_Name, Surname, and include PersonID into training records table and remove names from there. Otherwise you'll have a problem when you have 2 different people with identical names. And for solution you need a way to define person uniquely!) A possible query syntax:
qLastTraining: SELECT PersonID, MAX(Date_of_Training) AS [LastTraining] FROM tblTrainingRecords GROUP BY PersonID

Now in OnClick event of save button you call an update query of tblTrainingRecords for current person, where you set the value for Current_Training depending on Date_of_Training being equal to LastTraining in qLastTraining or not. The query string will be like
"UPDATE tblTrainingRecords tr JOIN qLastTraining lt on lt.PersonID = tr.PersonID
SET tr.Current_Training = Iif(lt.Date_of_Training = tr.LastTraining,"yes","no")
WHERE lt.PersonID = " & Me.txtPersonIDControl

P.S. When you change the Current_Training field type to Boolean (TRUE/FALSE), the update query will be simplier.
...
SET tr.Current_Training = (lt.Date_of_Training = tr.LastTraining)
...

[kevint57]

Thanks for the tips Arvi.
I am working through it and will let you know how it goes.
Cheers,
Kevin

[kevint57]

Hi Arvi,

Have done as you suggested. I changed the ID in the Employee table to PersonID and reflected this in the tbl_Training table, removing Surname and First_Name from that table. Both of these tables have a relationship through the PersonID. I also changed the Current_Training field to Boolean (True/False) as suggested.
How do I "call" an update query in the OnClick event of the save button? I have looked but can't work out how to do it.
TIA,
Kevin

[kevint57]

Also, the code comes up with a Syntax error when I paste it into a new query.
Kevin

[ArviLaanemets]

Here is a simple example (I used form events instead of button to trigger updates).

When form is opened, then all records are updated;
During session, trainings for current employee are updated whenever a record in trainings subform is added/edited or deleted.

[kevint57]

Hi Arvi,

Did post on Friday saying it was solved and saying thank you.
Post may not show until Monday US time or something.
Also hit the solved button in threads above.

Cheers.