Solved Create a NEW subform record for existing Record. (1 Viewer)

[Aryzona]

I have a form where we track the work the employee is doing. The main form works well and updates well - however if they close the form and work on something else for a bit then come back and restart working this i need to retain the Main record id and the date (easy enough, a query drives this form to retrieve that info) but the catch here is that i have a 2nd table attached to record form time : Start time = time() and End time triggers on the "save button" = time() This table has an auto increment field and the query returns the initial record created the FIRST time the form was used. How can i tell the Time portion to set a new FTid (record ID) automatically?

Right now i have that table as a subform - and it updates appropriately - but one must tab thru the existing entry to GET to the "NEW" - how can i bypass the existing entry and simply start with "NEW".

This particular aspect of the form is not meant to be seen, or interacted with - but merely to collect the time the form was open and the time the form was closed and the difference between these 2 times are calculated to show "Total Time" on a report. The sub form as it is, requires interaction to be effective....

 

[Ranman256]

IF you set the subform property DATA ENTRY = true, then it will always start with a new record when it opens.

 

[Aryzona]

IF you set the subform property DATA ENTRY = true, then it will always start with a new record when it opens.

I was hoping there would be a simple solution ... i will try that !!!! Thank you !

 

[Aryzona]

I was hoping there would be a simple solution ... i will try that !!!! Thank you !

That works perfectly !!!!!!!!!!!!!!!! I didn't realize it was an option ! I was focused on the main form with the subform in it. When I would choose the subform in properties, it did not give me the option to toggle data entry. After your suggestion, I started to think out side the box and pulled just the subform up in design view and in properties and found that THERE I did have the ability to toggle it to Data Entry - Thank you so much!!!! Not only did i find a solution but i learned a HUGE lesson in dealing with subforms!!!!!

 

[Aryzona]

Ok .. so .. setting it to data entry worked on one hand but i am struggling with the rest of the story!

This particular form i am working on is a continuation of previous entry. I use a query to isolate the worker name and date and ID# then run the form off the query so the worker can continue entry for the same day after previously closing the initial entry form. I can't use the "update" form mode because "management" does not want other employees scrolling thru eachother's records to find theirs, and management also does not want to install passwords to isolate employees.

This particular subform records the time stamps ... when the form opens it places time() in the Time start field , updates the parentID field, and the category field automatically.

I am struggling with how to trigger it to record the Time End since the close form button is on the parent form - the form is intended to be completely automatic and unseen once debugging is complete i was hoping to trigger the Time End stamp as part of the Click event for the form close button. I have used the form exit button in a previous form to catch the time End stamp for that form and I am finding that with this form it doesn't seem to understand that the field to update is in the subform.

AND

the record Id is remaining as "new" and not creating the new record - apparently because there is no "hands on " entry ? How do i get it to see the Time start , parentId and category as "entry" and assign the recordID number?

I am sure there is a way in VBA to make all this happen in the background but i am not good enough with VBA to script it (Yet .. gimme time and guidance ) ...

I am looking for suggestions on how to approach this! Attached a screen shot of the form - it isn't pretty... pretty comes after functional!

Thank you for all your anticipated wisdom and advice!

 

[bastanu]

In the Current event of the subform populate the date and time fields. I would recommend using one field for TimeStart and one field for TimeEnd as it is much easier to work with than trying to split in two (a date field and a time field) as you've done. A Date\Time field in Access will always have both the date and time portion so you might as well take advantage of that.
In the BeforeUpdate for the subform update the TimeEnd:

Private Sub Form_Current()
Me.TimeStart=Now()
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.TimeEnd=Now()
End Sub

Cheers,

 

[Aryzona]

In the Current event of the subform populate the date and time fields. I would recommend using one field for TimeStart and one field for TimeEnd as it is much easier to work with than trying to split in two (a date field and a time field) as you've done. A Date\Time field in Access will always have both the date and time portion so you might as well take advantage of that.
In the BeforeUpdate for the subform update the TimeEnd:

Private Sub Form_Current()
Me.TimeStart=Now()
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.TimeEnd=Now()
End Sub

Cheers,

sorry i wasn't terribly clear ... there are actually 2 subforms here. the subform with all the data in it works flawlessly for it's purpose and the time start there is for the time they started the Record ... 400# and the time end there is when they completed the processing OF that record (check mark in appropriate status box).

The sub form 2 is the one at the top right corner ... this is designed to record when they OPEN the form - Start time =Time() and when they close the form End Time = Time() ...

The person can only have 1 master record for the day ... but may use the form multiple times a day ... so i need to be able to track the Time per Form.

The main form is a simple data entry form and records the intial information perfectly - but if they close that form after 2 hours work ... and come back to work again, then i need the 2nd round of data to record the new start and end time for this "session".

I use a query to provide the previously entered records to THIS form ... so the id# for the individual is already established, and the record id# for the subform 1 entries is already established and simply appends the next round of detail data to that record.

I have a table with an autoincrement id# , Category number, Time Start, Time End , master record ID# to record Form Time.

What i need to see happen is the FTID to change from "new" to 8 (initial form for id#55 goofy dawg on 2/24/21 was #7). This will be a mulit-user system so i can't just automatically add 1 to the previous because it may already have been assigned to someone else AND it is an autoincrement as well. And to have a way to get the END time to record when the form closes, or the button is pushed.

The date in the detail record on subform1 is merely for show... for the worker to see.... it is auto-populated not keyed

 

[bastanu]

So on the Current event of the subform add:
If Me.NewRecord Then Me.FTs=Time():Me.Dirty=False 'this will force to create new record
In the BeforeUpdate event of the subform add:
Me.FTe=Time()
Cheers,

 

[arnelgp]

does the Upper subform (call it subA) "always" have only 1 record?
can you add code to subA's Timer event?

Timer Interval: 1000

Private Sub Form_Timer()
    Me.TimerInterval = 0
    With Me.RecordsetClone
        If (.BOF And .EOF) Then
            .AddNew
        Else
            .MoveLast
            .Edit
        End If
        !FTe = Time
        .Update
    End With
    Me.TimerInterval = 1000
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Call Form_Timer
End Sub

 

[Aryzona]

Yes the subform A would only have a single session start & stop time. But an employee could conceivably open and close the form multiple times a day .. so there could be 4 or more Form Session Time Stamps for the employee for that date. Setting a timer i am unsure of, i don't know enough about the timer function(?) to decide. I see your example has 1000 ... what does that actually mean? An employee might open the first primary form and work for an hour, close it. Then come back to work and use THIS form (the form that appends new records to previous) and finish the day in this form .. so .. 6 hours? How would I account for that ? vs the employee who hits the form 3 x thru out the day for 2 hours at a time?

 

[Aryzona]

So on the Current event of the subform add:
If Me.NewRecord Then Me.FTs=Time():Me.Dirty=False 'this will force to create new record
In the BeforeUpdate event of the subform add:
Me.FTe=Time()
Cheers,

me.NewRecord ---- i will try this ! that solves one problem for sure if it forces it to give me the next available id#

BeforeUpdate --- since the entire subform is autopopulated will it Trigger any event? and i am not GREAT yet with events and their scope ...would before update set the end time as the actual time the form closed? or is the form closing what triggers the update in the first place there fore BeforeUpdate would capture the actual time the form closed?

I will be reading up on this before bed tonight ... exciting reading ahead of me !!!

 

[arnelgp]

Timer Interval is in ms (millisec)
so 1000 ms = 1 second.

here is a sample.
close and re-open the form.

to find the Total hours, just create a Total query
against Table1.

 

[Aryzona]

Timer Interval is in ms (millisec)
so 1000 ms = 1 second.

here is a sample.
close and re-open the form.

to find the Total hours, just create a Total query
against Table1.

so in essence the timer stops when the form closes and that value is written to the FTE field ? If i understand this - this is perfect!!!

 

[bastanu]

But why run a timer that consumes resources when you can just use the event trigger to do it.
In the Current event just try If Me.NewRecord Then Me.FTs=Time()
Because you edited the record the BeforeUpdate should fire when closing the subform.
Cheers,

 

[Pat Hartman]

I don't understand why the time is in a separate table.

What exactly are you tracking? Do you want to know how long it took a user to create a record initially?
Do you want to know how long a user spends updating a record each time he edits it?

Some useful events:
If you want to track time spent editing, then use the Dirty event to set the start date/time. Then use the Form's BeforeUpdate event to set the end date/time. Make this the last line of the event so it is the last command prior to the record actually being saved.

If you want to log the time in a separate table, Capture the times as I described above but write the log record in the form's AfterUpdate event. That will allow you to create a row for each edit. You should also include the user ID so you know who did the edit.

If you care about what got changed, there are logging suggestions posted and you can search for them.

 

[Aryzona]

I don't understand why the time is in a separate table.

What exactly are you tracking? Do you want to know how long it took a user to create a record initially?
Do you want to know how long a user spends updating a record each time he edits it?

Some useful events:
If you want to track time spent editing, then use the Dirty event to set the start date/time. Then use the Form's BeforeUpdate event to set the end date/time. Make this the last line of the event so it is the last command prior to the record actually being saved.

If you want to log the time in a separate table, Capture the times as I described above but write the log record in the form's AfterUpdate event. That will allow you to create a row for each edit. You should also include the user ID so you know who did the edit.

If you care about what got changed, there are logging suggestions posted and you can search for them.

I need to track both - the length of time spent doing the task (for the day) - as well as the time it takes to do each individual entry in the task.

I have a form where the user begins the task. This form works beautifully to record the time they start the task, and each individual iteration of the task entries, and puts a lovely end time stamp in when the form is closed.

The issue is - that same worker may work this task for 2 hours (create the initial record) then close the form and come back 2 hours later and continue doing this task. I need to be able to track total time for the DAY as one continuous record, but account for the time spent on each session OF the day. I have a form that goes out and finds the original record so the master record ID# stays the same, but the time record for the session needs to be new (time start / time end are tracked in a separate table) so that i can pull in all the session times for the day for that master record ID# and come up with a total time spent. So in essence, the relationship is ONE task record to many time records.

One the original form they start their day with, i trigger the stop time stamp off the "close Form" button. It is the 2nd form that i am having difficulty finding a way to trigger the stop time.

 

[Pat Hartman]

If you need multiple entries for a given record, then you need a separate table. Read what I suggested regarding events that will help you to do this.

 

[Aryzona]

If you need multiple entries for a given record, then you need a separate table. Read what I suggested regarding events that will help you to do this.

I am looking at your suggestions and trying to implement. I do tag the master record ID, it is not visible in the screen shot above but is there off screen to the right hand side. Everything in the Time Subform is meant to be automatic and (end result) not seen by the worker at all. When this form works and the data is updating properly - i will make this form invisible.

My current tripping point is that while the subform time record id says New , it is not writing to the next available auto increment number. and the end time does not write.

There are several suggestions put forth here and i am working to see which one works for my current situation. I am a beginner and it takes me some time and trial and error to implement new concepts and sometimes to fully understand and grasp said new concepts. I greatly appreciate each and every response i get as it helps me learn

 

[Aryzona]

I am looking at your suggestions and trying to implement. I do tag the master record ID, it is not visible in the screen shot above but is there off screen to the right hand side. Everything in the Time Subform is meant to be automatic and (end result) not seen by the worker at all. When this form works and the data is updating properly - i will make this form invisible.

My current tripping point is that while the subform time record id says New , it is not writing to the next available auto increment number. and the end time does not write.

There are several suggestions put forth here and i am working to see which one works for my current situation. I am a beginner and it takes me some time and trial and error to implement new concepts and sometimes to fully understand and grasp said new concepts. I greatly appreciate each and every response i get as it helps me learn

I am sort of getting what i need. I am clearly not implementing this correctly ...

What i am seeing is the new record id ! YAY... it does create the id # , i am seeing the time start and it is writing to the FT table. if i open the FT subform in data sheet mode - i can see the stop time, but it is not recorded on the record id line but below it, so in essence a "new" record in the FTs (start time column) . See example below of the form in data sheet view:

What i am NOT seeing is the end time being written to the FT table ... See example below:

The control src for the field FTe is ... FTe

This attempting to use the after update event?
//CODE

CODE//

 

[bastanu]

Did you try my suggestion to use the BeforeUpdate event? The AfterUpdate is too late...