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

[Aryzona]

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

I did .. i tried both before and after and i am getting the exact same behavior

 

[bastanu]

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

 

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

I have implemented your example and it is not writing either start or stop to the table.

I told you what events to use. You have NO code in ANY form level event. You have code in your button. That won't do it. I can't even figure out what the app is supposed to do. Something about undelivered mail so it sounds like this is the USPS and I'm paying for this app. I'd really like to help but you could not have made the design more cryptic if you tried. It is completely undecipherable to someone who doesn't know what the abbreviations mean. I have no point of reference. It must hurt even your head to remember what letter you used for each column name. There has to be some rational happy medium between a single letter and 32 letters with special characters and embedded spaces which is what we usually see.

Time() stores only the time. You really need to use Now() if you care about the date and you should.

DoCmd.Close acForm, Me.Name, acSavePrompt

This command does not save a record. It saves a form. Do you really allow users to change the design of a form? It is generally poor practice to save design changes made by users. Close the forms without saving.

Suggestions,
1. Do NOT prefix column names with the table. This is unnecessary and as you can see by the DS subforms, obfuscates the name since all you can see are the first few characters and they are all the same. You can fix this by opening the form in design view and changing the label caption so something more meaningful.
2. Standard abbreviations are OK but single letter abbreviations are meaningless. It is quite possible that I wouldn't know what the columns and tables are supposed to represent even if you spelled out their names but as it is, I have no clue and I've been doing this for a very long time so I'm pretty good at "interpreting" questions and database schemas.
3. When you make a relationship, enforce Referential Integrity. That is what relationships are for.
4. Relationships are easier to "see" if you give the FK the same name as the PK it relates to. So UNI in UN should be LocID and so should TDI in TD and RCI in RC, etc.. FTcat in FT should be FTCID. Loc is the only table with a decipherable name but the data contained doesn't relate to anything I would consider a location so Loc must not be location.
5. If you are going to sort the name comb on last name, reorder the columns to Last, First. rather than First Last. That will allow type and filter to work correctly.
6. You also seem to be storing name mushed as well as separated. This is not necessary and can lead to data anomalies if you are not careful with your name change process. And I can't be sure how it happens but it looks like you have copied the full name to multiple tables rather than relying on a join to bring the tables together.
7. You cannot rely on a control getting the focus to populate it because unless the user actually places focus in the field, your code in the GotFocus event will not ever run. If you want to populate fields with data from some other place, do it all in the Form's BeforeInsert event. That way as soon as someone starts typing in ANY field, the data can be copied but You should NOT be copying data fields unnecessarily. I see Me.UNDfd = Me.Parent.Text34 -- there are several things wrong with this starting with controls should always be given proper names so Text34 is unacceptable. But more importantly, if the value for all rows in the subform will be the same, then the data belongs in the parent table. FolderDT is unbound in the parent record? why is it there? Why are you copying it to the subform record? If you want to use a default for the subform, you need to find a better way to populate it or put some validation in the code to ensure there is a value.

I've given you a lot of advice but I don't think I've actually helped you. I can't follow the logic you are using for creating the time records and so I'm pretty sure that the mis-understanding is preventing you from properly applying my suggestions because they don't make sense to you. If you can rebuild the tables and forms with meaningful names we might be able to get someplace. I and the others helping you will have firmer ground on which to stand. Plus, you'd be doing yourself and anyone who has to maintain this app after you a huge favor.

This won't be as onerous as it could be. This is one case where I'm going to suggest that you turn the Name Auto Correct feature on. Experts refer to it as Name Auto Corrupt because it is actually quite dangerous. But this is a case where it will help you. But, be very careful to follow my directions or you'll end up cursing my name.

Start by making a backup and zipping it. It is safer that way since you can't accidentally change it. Then turn on the feature if you have it off. Make yourself a note to go back and turn it off IMMEDIATELY when you are done changing the names.

Then table by table rename the columns and save the table. Then rename the table name. If you have the patience, open every form, report, and query, the changes will be applied as you make them. You don't have to do this though, you can let them stack up just as long as you don't double up and change something twice before you propagated the first change. The "corrupt" part gets people because they don't understand when Access propagates the changes. The answer to that is - not until you next open an affected object. So if you make a change but fail to open an affected object, you may make a different change and Access gets lost. So just be patient and work through one table at a time. This "feature" does not modify code so you will then have to go into each form and compile it to find the compile errors generated by the name changes. I think what happens in forms is if the name of the bound field = the name of the control, NAC will change the name of the bound field and leave the name of the control alone. This means that your code will continue to work because Access will assume that you are referring to the control and in newer versions of Access, you can do this. It is also helpful to turn on the LOG feature since that will enable you to see the actual propagation as the objects are changed.

There are utilities out there that will do mass changes but they can also be dangerous although they do change more things for you all at once. If you get a utility that does mass changes, do ONE change at a time and be very careful. Don't take shortcuts. Spell out the entire name. If you change "UN" to "UNC" that will change undo to uncdo so, take care. Make a change. Then compile to make sure you didn't break anything. Save early. Save often because 10 changes in, you could break something that you can't fix. Access' NAC does the changes very differently and so doesn't have this particular problem. It has others. I will attach two documents you can read if you want to actually understand how NAC works. One is a document created by MS and the other is a ppt that I created when I gave a presentation to an Access User's group on the topic. Since it is a PPT it doesn't have a lot of words but it does summarize the actions if the doc is too dense. I also included a database to practice on.

 

[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

 

[Pat Hartman]

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.

 

[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