Issue with Adding & Updating New Records from other Form

[Gaztry80]

Hi all,

I have created a sub-form called "frmAssignmentDetails," which I use in the main form "frmAssignment," where I can add new cars based on the table "dbo_tblCar." I use some FAYT VBA code from MajP to find the car in a combo box. In case the car is not found, the user can double-click on the text box above the combo box to launch the form "frmAddcar" to add a new car to the database in the table "dbo_tblCar." If the user adds a new car, but the car is already in the database, no new car is created; instead, the related CarID is placed in the record source of frmAssignmentDetails. Additionally, each time the record is changed in frmAssignmentDetails, the StartDateTime value of frmAssignmentDetails is updated to Now().

I have two remaining issues that I’m struggling to resolve:

1.) If the user decides to insert a new record but starts with the double-click event, immediately adding a new car and finishing the records, it is no longer possible to add a new record afterward.

2.) When performing process (1), the StartDateTime is not inserted into dbo_tblAssignmentDetails.

I have attached my database. Does anyone have any advice or tips? Thank you !

 

[arnelgp]

issue 1. double-click where? (what control and form).
i added StartDateTime on the subform and Hid it.

 

[Gaztry80]

issue 1. double-click where? (what control and form).
i added StartDateTime on the subform and Hid it.

Hi, control "Text68" within form frmAssignmentDetails

 

[arnelgp]

here test it again.

 

[Gaztry80]

here test it again.

After testing, I can confirm that issue one is resolved. However, after inserting the record (using the method from issue one), issue 2 still exist for that record, StartDateTime is null/empty. Also when changing an existing car by using frmAddCar form (with text68) is not updating anymore the StartDateTime.

 

[arnelgp]

test it again, i add BeforeUpdate event on the subform instead of using Dirty event.

 

[Gaztry80]

test it again, i add BeforeUpdate event on the subform instead of using Dirty event.

The functionality for issues 1 and 2 has been resolved. However, the incorrect time is now being inserted/updated in the StartDateTime field. Currently, it records the time at which the record is saved, but I would like it to capture the time when the user begins the insertion or update process.

For example, if the user starts updating at 12:00:00 and finishes 30 seconds later, the date time inserted/updated should still be 12:00:00, rather than the completion time. This is the reason I was trying to get this functionality by using the Dirty event.

FYI: In the backend (sql server), a trigger records the insert/update datetime in another table. Both timestamps are then used in the backend to calculate the number of seconds spent on a record.

 

[arnelgp]

you can manually remove the code of BeforeUpdate event.
instead, add code to the subform's BeforeInsert and move the code there.

 

[Gaztry80]

you can manually remove the code of BeforeUpdate event.
instead, add code to the subform's BeforeInsert and move the code there.

I don't think that will work, because if the user edits another field like ProductType, it will take the "record finish datetime", instead of "records start edit/insert datetime"

 

[arnelgp]

oK, I remove the BeforeInsert event and reinstate Form_Dirty event , but this time i change it to Public Sub so we can call it from frmAddCar.

 

[Gaztry80]

oK, I remove the BeforeInsert event and reinstate Form_Dirty event , but this time i change it to Public Sub so we can call it from frmAddCar.

I think I got it to work, by adding first a check if the subform is already on Dirt in frmAddCar.
We want the timestamp the moment the record enters dirty mode and don't change it when a field is changed. Please see my update based on your last modification.