Solved Create a NEW subform record for existing Record.

[Aryzona]

What else do you have in the form's module?

Not sure i understand exactly what you are asking (form modules) but i will try to reply

I have a main form that carries forward the pertinent info from the first record of the day - Emp Id, date. Then the detail records subform, where the individual interations occur, and then the FTsubform to record the Form start and Form End time.

Does that describe what you are asking?

 

[Aryzona]

Not sure i understand exactly what you are asking (form modules) but i will try to reply

I have a main form that carries forward the pertinent info from the first record of the day - Emp Id, date. Then the detail records subform, where the individual interations occur, and then the FTsubform to record the Form start and Form End time.

Does that describe what you are asking?

This particular tracking system is modeled after the current tracking system - which is an excel spreadsheet each worker fills out - I am trying to streamline the process by taking out the component of entering the data off the spreadsheet into the data base by yet again another worker AND to take some of the tracking load off the worker by automating the time spent so they don't need to keep track of the time themselves.

Having them enter directly into the database makes the daily reporting more timely as we can simply run a report at end of day instead of waiting till the next day for the entries to be entered into the current db to create a report for the day before.

 

[bastanu]

Please have a look at the sample attached.

Cheers,

 

[bastanu]

Sorry, forgot to set the DataEntry=Yes for the subform, but I'm sure you figured it out

 

[Aryzona]

Sorry, forgot to set the DataEntry=Yes for the subform, but I'm sure you figured it out

I think i see .. FTe is stamped as time now when the form initially opens then re-stamped before update (which occurs at form close) to get the final entry?

so I would not need to set the time start =time(now) because the dirty aspect updates it ? and then the before update - updates the End time ?

I really am a newbie at this .. alot of practical DB knowledge (referential integrity, One to many etc... by no means expert tho) i know what a db CAN do but i do not always know how to tell access what i want it to do

 

[bastanu]

Yes, that's how it (mostly) works. The FTs has a Default Value of Now() in the table, so it get its value soonest the new record gets created\saved by the Me.Dirty=False. Once the record gets saved it is no longer "dirty" so BeforeUpdate wouldn't fire when you close the main form and that is why I am (temporarily) setting the FTe to Now() in the Current event. The BeforeUpdate will update it with its final value when closing the form.

Cheers,

 

[arnelgp]

in the middle of data entry, a power outage took place?
what happens to FTe? using timer event guarantees that it always has a value.

 

[bastanu]

But also fires every second\five second\ten second using (valuable) resources and in some cases interferes with other running processes (try to work in a VBA project with a form that has a timer open)....

 

[Aryzona]

But also fires every second\five second\ten second using (valuable) resources and in some cases interferes with other running processes (try to work in a VBA project with a form that has a timer open)....

Agreed ... the timer is the easiest to do, but i will have 40 plus people simultaneously working this .. running the timer seems like it would create too much overhead, to do a simple task that i should be able to accomplish without overhead. On the flip side ... i did learn something new by the timer contribution, up to that point i had NO idea how the timer thing worked, so i learned a great deal just by the suggestion! Every contribution helps me grow, and I appreciate every word y'all take the time to scribe

 

[arnelgp]

40 plus people simultaneously working this

even if it is 100 users, the timer is negligeble if you Split your db.
the timer will fire on each Local copy (on separate computers), so there is no worry.

 

[Aryzona]

You need to collect the start date from the form's dirty event. Do NOT write the record at this point. In the form's AFTERupdate event, the record that was modified has been changed and you can now collect the end date. In this event you can write a single record that has both the start and end dates AND the recordID is also available for the record you are logging the time for.

There is NO reason to use a timer event. save the start time to a variable and then in the FORM's AfterUpdate event, you have everything you need. Running a timer does create overhead but if you need it you need it. But in this case, you do NOT need it.

A timer event is most commonly used for two things.
1. to detect idle time so you can force the database to close if the user has not pressed any keys in the specified amount of time.
2. The form is typically kept open for long periods and you want to force it to refresh.

Saving Start and Stop times when you have events that will capture these as I described are much simpler.

I have implemented your example and it is not writing either start or stop to the table. The start time is represented in the table in a line "new" but none of the other data is written and a new id# is not assigned. I have compared your example to mine and the only difference i see is your FTsubform calls the FT table directly as the source, and my FT subform calls a Query - is this why it is not functioning?

Attaching a copy of the database:

Rocket J Squirrel , Goofy Dawg, Bullwinkle DaMoose and Pluto Kaynyne are my testing names. Rocket has a record initiated for Today 3/6/21.

The form Un-Undelivered Mail Tracking is the one that creates the first record of the day. It works well, Creates the main record in the UN table, and records the Detail record in the UND, table and creates a record in the FT table with time start and end attached to the UNid. You can test it with any of the names above except Rocket as .. i already started a test record for him today.

The Form: Un Query1-find existing is the start of the 2nd round for the day. It allows Rocket J Squirrel to pull up his existing record for 3/6/21 and passes this to UN Query1-findexisting1 form so he can continue to edit the 1st subform detail records.

The detail records consist of the user entering a Case# (400, or 999 as you can see) and as soon as they tab from that field it writes the record start time, once they do the "thing" with the document - they then record the outcome, space bar or mouse click in the appropriate check box, (Uploaded, Problem, Unable to Locate (UTL)) - the record endtime is triggered of the Checkbox. Once they have completed what they are doing for the session, they click the close form button and the form closes.

The detail records write correctly in the UND file with a new UNDid for each entry and the UNid # matching Rocket J Squirrels UNid for the date. It lacks the fdate but I am unconcerned with that at the moment ... I know why that is happening.. lol

The FTsubform2 is the one that is NOT working and is part of the UN Query1-find existing1 form. It shows the line "new" with the start time, but it is not assigning the new record id and is not saving the end time when the form is closed.

I originally had the tab order set to be the LAST before button but moved it to Tab order 2 to match your example in the event that it needed to be initiated earlier ... this did not make a difference. it is still set at tab 2. In the end game, i want this FTsubform to be invisible to the worker so I had it at Tab 6 which is last before button.

I also started out with the VBA on current and before update with Fte=Now but tried changing it to Fte=time() just to see if that would make a difference, it did not, but it remains FTe=Time() in the VBA.

The reason i am jumping through hoops to find previous record and not simply doing the 2nd form as an "update" is .. Management does not want 40 employees cycling through their fellow employees records to find their own record to continue AND management does not wish me to implement a password system which would Isolate each employee from the others records.... fun fun. There may be a better approach to meet the above criteria than the path I am on .... but the path i am on is the only way i know

Thank you for all the help so far - and i am interested in your feed back regarding the system in general. I just noticed that the post i am replying to says AfterUpdate, but the example db you sent me has the event on BeforeUpdate.... this may also be why it is not functioning properly?

 

[Aryzona]

Thank you. I will study this.

I do have the current and before update code in the form event on the FTsubform. I do not have any code in the Host Form, just the macro on the button. This is not a USPS application. The app is being paid only in the essence that I am getting a paycheck whether i create this app or not. I will study your points above, as I am a complete newbie and make no pretense otherwise. I am learning.

We handle documents .. the undelivered mail is ONE of the types of documents we handle. All are tracked in a similar way but the undelivered mail is the most complex. The other tables in the database will address the tracking of the other document projects. I will have one worker, with many project tracking. I put the table names in there to help me keep straight which tracker sheet i was working with, i did not realize that was considered bad form. I intended to keep all form times in the FT table. The category in the FT table designates which body of work the record goes to. A worker rarely works 1 thing all day, rather starts a default thing and then switches to what ever is determined to be the "priority" then back to their default when the priority is under control - so .. they could have 2 or more tracking sheets for 2 or more different bodies of work every day. Each tracking sheet is a little different, so the detail data has it's own table. I am rambling some here ..but it helps me think ... and there is clearly much to think about!

 

[bastanu]

@Aryzona
I think you should definitely streamline how this system works. But for the problem at hand I think you just had the wrong subform loaded into the UN Query1-findexisting1 form. Please have a look at these two screen shots, it looks to me it is working properly like it was in my sample. I would recommend changing from Time() to Now() as it better to have the date part. And stick to the BeforeUpdate event that updates FTe on closing of the main form (you can see the pencil meaning the record is "dirty" and BeforeUpdate will trigger on close).
Cheers,
Vlad

 

[Aryzona]

Since the process starts at the main form, that is where you need to collect the times. To include time spent on the subform is more complicated and for that you will need to update the record created on the main form. In the mainform's AfterUpdate event where you create the log record, retrieve the ID of the record that was just created. This is easiest to do if you used DAO and .AddNew rather than an append query. copy the autonumber to an unbound control on the main form. Then in the subform, reference this field to get the id of the log record you need to update with a new end time. If the ID is empty, that means the user has left the form and come back to it at a different time and so you need to create a new record. Use the same logic you used for the main form. Am I understanding that correctly? You want one log record that includes all the time spent on the main form and all subforms at one time. If they come back, they might only work on a subform. But if they work on both, you would again use one log record.

Having a clear plan is the only way to ever begin a development process. You make fewer mistakes and logic errors in development if you can start something and complete it before moving on to something else. If you don't have a clear plan and an outline (if the task is large enough so you can't keep all the details in your head at once) then you are setting yourself up for failure. I'm pretty sure you started this project before knowing what you actually needed to do. Lay out the workflow start to finish. It would actually help if you walked in their shoes for a day or two.

I can tell you from more than 50 years of doing this, I am significantly better the first time through than I am with fixing my mistakes.

I agree with doing it correctly the first time is way better than fixing mistakes! I did think this out. I did implement it to the best of my ability. I am BARELY knowledgeable of VBA - complete Novice - and while i know theoretically what i should be able to do, i have no idea how to communicate that in VBA terms. Every little bridge i cross helps me learn a new tidbit of VBA.

I actually DO walk in their shoes, i do this tracking for my self processing these documents and believe it or not - my system is a huge improvement on the old system!

I did finally get all the pieces to fall together in the correct order, on the correct form and thanks to your guidance, with a new understanding of some helpful tidbits and at the very least a new understanding of some things i clearly did not understand at all ... such as the "DoCmd.Close acForm, Me.Name, acSavePrompt" saving the form and not the record, I did not understand that saving the form was different than saving the data in the form...much to study!

Despite my obvious limitations, I am now able to write the Time Table with a valid start and end time attached to the correct master record.

Thank you !!!! for all your help!

 

[bastanu]

Hi Aryzona,
Just curious if you ended up using the code I've sent you or did you write your own using Pat's suggestions?
Cheers,
Vlad

 

[Aryzona]

Hi Aryzona,
Just curious if you ended up using the code I've sent you or did you write your own using Pat's suggestions?
Cheers,
Vlad

I used your suggestions, and followed the example Pat sent me as a model to get all my ducks in a row

 

[bastanu]

I see... In post #25 (and in post #35 showed that it worked) I gave you a sample which seemed to work, which post was Pat's example helping you herd the ducks ?
Anyway, glad to see you got it working, good luck with it!

Cheers,
Vlad