Return to current nested subform record after recalc (1 Viewer)

DataBass · Jun 25, 2018

I have a main form, frmItemDetail. This form has a subform frmStageSub in datasheetview. Nested within this form is another subform called frmCategorySub in Continuous Forms view. See attached pic.

On the main form, there are 6 calculated controls, Dsums, Dlookups etc.

frmStageSub and frmCategorySub both have calculated controls.

When I update either subform, I have the whole form recalc after update. I used recalc because supposedly it doesn't requery the underlying tables and shouldn't cause the current record to lose focus. However, I have found that this is not the case. When I update either subform, it takes me back to the first record in frmStageSub. For the record, I have tried Refresh and Requery as well. The results seem to be the same for all three.

Ideally I wish I could simply requery, recalc or refresh individual controls instead of the entire form, but this doesn't seem to work. I know controls support requery, but even when requerying an individual control it seems to requery the whole form.

I managed to get it to return to the same record when updating frmStageSub using this code that I have adapted from other discussions on the forum:

Code:

  Me.Painting = False
    Dim StgID As Long
    StgID = Me.StageID
        Me.Parent.Recalc
    With Me.RecordsetClone
        .FindFirst "StageID = " & StgID
        Me.Bookmark = .Bookmark
    End With
   Me.Painting = True

However, no matter what I do, I can't get it to return to the current record when I update the nested subform frmCategorySub.

As a side note, when the recalc runs, it appears to repaint or redraw the entire form 3 times. It looks like it recalcs the main form, then the first sub from top to bottom and then the nested sub from top to bottom. I think this may be a contributing factor in why I am having difficulty. This is why I included the Me.Painting lines.

Does anyone have any ideas for how I can correct this?

Thanks

JHB · Jun 25, 2018

Post your database with some sample data, zip it.

isladogs · Jun 25, 2018

Ah.....I think I remember this from a previous thread.
Sorry to quibble but for the benefit of others reading this thread it isn't a form, subform & 'nested subform (or sub-subform).
It is a form and subform where the subform contains a subdatasheet.

It may well be that the solution is to to remove the subdatasheet and replace with a standard 'sub-subform' that updates when a subform record is selected

DataBass · Jun 26, 2018

Here is the form in design view.

How is this not a main form, with a subform and a nested subform within that?

There are clearly 3 forms. What am I missing here?

DataBass · Jun 26, 2018

JHB,

I'll clean it up and get it 'postable' as soon as possible. It's a bit of a mess right now.

I was hoping someone might be able to point me in a direction, but I realize you probably need to actually see what's going on in the background to help with a solution.

Thanks.

MarkK · Jun 26, 2018

My impression of the form in the image is that it is probably designed around the structure of the data, not designed around the structure of the workflow. Ask yourself, "What is the user of this form going to spend 95% of their time doing?" Then, design the form for that task, and that task only.

The user of this form does not need to manage the Customer entity or the Stage entity. Each of those objects should have their own specific form, and in that case, this form can be vastly simplified. There is, as a result, no need to nest three deep.

So, overall, if you approach user-interface design as driven by the tasks your users need to perform, you won't find yourself nesting forms this deep.

hth
Mark

DataBass · Jun 26, 2018

Mark,
I can understand how it could appear this way.

However, this is exactly what the user wants. My initial version was more like you suggested but the user actually wants to see all of this data just like this. It gives him an overview of everything he wants to see at once and he can see how everything is related, and I can understand why he wants this. It does actually make sense.

It would be much easier to go a different route, but as it is, the user is happy, aside from this update behavior I am trying to correct.

I am going to get a version posted and hopefully someone will know how to get it to do what I want.

Thanks for the input

MarkK · Jun 26, 2018

DataBass said:
...but the user actually wants to see all of this data just like this.

Do you mean SEE the data, or do you mean EDIT the data? Fine, show them the data, then give them a button to open the Edit Wizard for that entity type. Then you can avoid this depth of nesting, avoid the bugs that come with this complexity, and still SHOW the user all the data, and make it editable, and confine specific workflow to smaller, more manageable interface elements.
Anyway, those are my observations if you or others can find them useful,
Cheers,

Mark

isladogs · Jun 26, 2018

In post #2, perhaps I should have said

It is effectively a form and subform where the subform contains a subdatasheet.

My point is that subdatasheets in forms are confusing and overall clarity is lost.
Much better to select a record in the subform and then view the other details in a separate form or separate subform but for that record only

However, MarkK has since explained the point I was making far more clearly than I did

JHB · Jun 26, 2018

DataBass said:
JHB,

I'll clean it up and get it 'postable' as soon as possible. It's a bit of a mess right now.

I will not judge your database, so if it's messy or not, does not matter.

DataBass said:
... but I realize you probably need to actually see what's going on in the background to help with a solution.

Yes getting the hands on the database is the A and O else it is too much guessing how you've set it up and could lead you on the wrong way.
To get a cut down version of your database, create a new database and copy all necessary forms and tables/queries into it, it takes lesser as 2 minutes, I think.

DataBass · Jun 27, 2018

MarkK and Ridders,

I certainly understand your points. They are definitely valid and there probably is a better way to present this. Both of you obviously have far more experience with this than I do. No argument there. For the time being, I just want to get this one to function smoothly then I can experiment with other designs. Thanks for the input.

JHB, and anyone else willing to take a look,
I have attached copy of the database. Once open, the form to look at is frmProjectsNew. There are four complete records in there. Click the 'Expand/Collapse' button to show all of the subform records.

The problem occurs when you update a record in the innermost subform (frmExpensesSubCF). After the update, the focus goes to the first record in the first subform(frmIncomeSub). I would like the focus to either stay on the same record on the inner subform or go to a the next record in that subform as it normally would if you were tabbing through the fields. As it is now it is so disruptive when it goes to the top of the first subform.

You can see all the code I have tried in the After Update event. It is all commented out and there is just a simple recalc there at the moment.

Let me know if you need me to clarify anything.

Thanks for looking and any help would be greatly appreciated.

JHB · Jun 27, 2018

I've made some other solution as recalc the whole form, try it.

DataBass · Jun 27, 2018

JHB, Thanks for looking at the database.

I have just edited this post. Please disregard what I said previously!

I see the funtion CalTheTotal() that you added. I also notice that you are storing the total in the underlying table. This works very well. I am going to see if I can apply this same idea to recalc/requery some of the controls on the main form without breaking it.

Thanks for your help with this!

Here we go

DataBass · Jun 27, 2018

While I'm working on this, could anyone tell me if it is bad practice to use domain aggregate fields as I have on the main form?

I always thought you shouldn't store values if you can calculate them but maybe I'm wrong on this

JHB · Jun 27, 2018

DataBass said:
...
I always thought you shouldn't store values if you can calculate them but maybe I'm wrong on this

You're correct, but in this case it is the only way, as I see it, IF you want to show the correct value in the Total Expenses in the parent form!
Using domain aggregate fields won't show the correct value until the current record is saved.
And you know, no rules without exceptions!

Also remember to add the code in the event "OnCurrent" in the main form.

DataBass · Jun 27, 2018

JHB,
What you did makes perfect sense. I would never have thought of that. I just noticed that you changed the On Current event of the main form. Again, this makes sense.

I've been unsuccessfully experimenting with your code to see if I can get txtProjectExpenses and txtProjectNet (on main form) to update also. Following your logic, I am assuming I would need to get rid of the Dsum functions and instead write these values to the main table, tblProjectList

----Edit----

I think I'm getting close, but still not there yet...

JHB · Jun 28, 2018

Just be careful, if the domain aggregate fields works correct, then don't change them, but you're the only person who can decide what is the best solution for you.

DataBass · Jun 28, 2018

JHB said:
if the domain aggregate fields works correct, then don't change them,

Thanks JHB. I agree, the domain aggregate functions work, so why not keep them?

I finally got it to work.

Here is the new After Update code for frmExpensesSubCF:

Code:

Dim AccID As Long
Dim ExpID As Long

   With Forms!frmProjectsNew!frmIncomeSub.Form
      AccID = .AccrualID.Value
      ExpID = !frmExpensesSubCF!ExpenseID.Value
      .Recalc
      .RecordsetClone.FindFirst "AccrualID=" & AccID
      If .RecordsetClone.NoMatch = False Then _
      .Bookmark = .RecordsetClone.Bookmark
      !frmExpensesSubCF.SetFocus
      !frmExpensesSubCF!cboCategory.SetFocus
         With Forms!frmProjectsNew!frmIncomeSub!frmExpensesSubCF.Form
         .RecordsetClone.FindFirst "ExpenseID=" & ExpID
         If .RecordsetClone.NoMatch = False Then _
         .Bookmark = .RecordsetClone.Bookmark
         End With
   End With

This actually works. I can't believe it.

Thanks for the help.

Return to current nested subform record after recalc (1 Viewer)

Registered User.

Attachments

Have been here a while

MVP / VIP

Registered User.

Attachments

Registered User.

bit cruncher

Registered User.

bit cruncher

MVP / VIP

Have been here a while

Registered User.

Attachments

Have been here a while

Attachments

Registered User.

Registered User.

Have been here a while

Registered User.

Have been here a while

Registered User.

Similar threads

Users who are viewing this thread