Can't Find Where To Put Code

[micks55]

I have frmMain with frmSub which performs complex calcs and recommendations when values in the sub are changed. This works fine in the AfterUpdate event of all the relevant fields but I have a separate frmGlobal so that the user can make the same change to all of the records in the subform.

The problem is that after a global change, I need to visit every record in the sub and recalculate all of the recommendations but I can't see where to do this and am struggling because I can't grasp the strategy that I need.

I have tried this…
When frmGlobal!btnCommit is clicked,
Forms!frmMain!frmSub!DoRecalc = True
Forms!frmMain!frmSub.SetFocus
DoCmd.Close acForm, "Global"
then, in frmSub I have tried this in OnGotFocus and OnActivate but the message never appears.
If Me!DoRecalc = True Then
MsgBox "its true"
Me!DoRecalc = False
End If
I have also tried the OnGotFocus of the first control in frmSub.

I understand that Refresh and Requery are the normal way to go but I can see no way that I could manage the complexity of the recommendations within the query of the subforms recordset. The calc's also include looking up data in a settings table.

I can put a button on the frmSub to process all of the calcs whenever the user wants to, but it should be automatic in case the user forgets. Can someone please point me in the right direction even if it's what phrase to search for. Thanks for any suggestions. Mike.

 

[suepowell]

HI,

I'll try to make a suggestion, but I'm not sure I really understand what you want so forgive me if I'm heading in the wrong direction.

I think you have code that runs after an update in a field (several fields I know but lets start with one), and if this update is made in frmglobal you want this to run in all records of your subform at once.

I think I would do this by opening a recordset in the module of frmglobal which contains the fields in your subform, then stepping through this recordset and running the calculation for each record. This means you are changing the underlying data, not trying to manipulate the subform.

You could also consider taking the code you have written for the after update event of the field and putting it in a module as a public sub. You could then call it from your subform, or from frmglobal and be sure you were running exactly the same code from both places.

Others may have opinions as to how efficient this is to run, but I have found it OK with The databases I am using.

You would of course duplicate these ideas for each field you want to be able to update.

I hope this has given you some new ideas, and if I've got completely the wrong end of the stick I'm sorry!

Sue

 

[micks55]

Sue, Thank you so much, sometimes your so close you can't see. There's a glimmer of hope here so I will certainly be giving it a try and I'll need to find a way to Refresh the subform when frmGlobal closes but that's another day.
Thank again, Mike