Subform slow to update on combo change

[Dwight]

Hello,

The last couple of days I have been putting together a very large form. Basically it is a seriers of forms and then subforms spread out across different tabs. Anyway, one subform is based on a combo box selection i.e. the combo box is the Master part of the Master/Child relationship.

It works but the subform does not update automatically. I have to click on the form and then it is correct. I think I need to "Refresh" with VB. Right/Wrong?

Which event would I place the code under?

I think this is easy.

Thanks in advance, Dwight

 

[llkhoutx]

Your doing something wrong, because what you recite should work. No need to refresh. The Master/Child Links should handle that for you. I suspect bad Master/Child Link properties, that is, the Links not being named correctly, often inconsistently.

Having multiple subforms of a corresponding number of tab controls is a waste of resources because you only view one tab at a time.

A different simple design is to create a form with a blank subform (no controls), with Master/child links and then calculate which subform is actually needed (as you're doing), then set the dummy subform's sourceobject to that subform. The Master/child Links will cause the records to position correctly. No need to have Access all the subforms and Access requery (not with the requery statement) them every time the Master/Child Link values change. On tabs, your only seeing one subform at a time.

Of course, you may be referencing the controls on multiple subforms at a time or have multuiple subforms on one or more tab controls. In that event compute the subform which must be loaded at any time and modify the foregoing accordingly.

I do something similar to this with 79 different subforms being displayed on a form, the subform displayed is determined by the combination of results from 3 separate combo boxes and is set on the AfterUpdate event of the last of the combo boxes. It works really well and is very fast.

me!sfrmDetail!SourceObject="sfrmP01", when sfrmP01 is wanted.

me!sfrmDetail!SourceObject="sfrmDummy" on a combobox change and when the form first opens.

 

[Dwight]

Thanks for the advice. It doesn’t surprise me that I’ve botched something. The idea is to have a main form with 3 level hierarchy (Account > Portfolio > SubPortfolio). These are one to many relationships i.e. 1 account can have many portfolios and 1 portfolio can have many SubPortfolios.

Each of these levels has information pertaining specifically to it which I want to display on the tabs. Therefore I have 3 levels of tabs. When the user changes portfolios the account (above) does not change.

I don’t fully understand your method but I’ll study it and give it a try. It seems clever. I may hit you with a follow up post. Any chance that you have or know of an example database that I could study?

Kind regards, Dwight

 

[llkhoutx]

Your form is the exact situtation which I have, in that I have a Owner, Category (for the Owner), and SubCategory (for the Category). The combinatiojn of the three determines which subform I display.

The code is relatively long, but is lightning fast.

If my code wasn't priprietary, I'd send a copy.

In a nutshell, he's how you do it:

You have a form, "frmMain", which has your three combo boxes and a subform, "sfrmDetail" which has no recordsource and no controls but has Master/clhild Links set, a dummy subform. You also have numerous (real) subforms.

On the AfterUpdate event of your "last" combo box, you go through code which determines which subform you really want, and you set the

me!sfrmDetail.SourceObject = "Name of Form" (something like sfrmX)

With that the subform "Name of Form" is displayed on your form, the Master/Child Links work and the subform appropriately filters your data.

One caveat, code the resetting of your combo boxes with a Doubleclick event which sets the combo box to Null, as well as the combo boxes uner it, and also sets

me!sfrmDetail.SourceObject = "frmdummy", so you don't have the combo boxes showing one thing in part and the subform showing unrelated data.

 

[Dwight]

I’ve made myself dizzy with this so I thought I would lay it out from the beginning and see if someone can help. IIkhoutx’s advice has been good (and appreciated) but my VBA skills are currently too poor to implement his technique.

Here’s what I have:

tblAccountID
AccountID field, autonumber PK

tblPortID
PortID field, autonumber PK
AccountID field FK

tblSubportfolio
SubPortID field autonumber PK
PortID FK
AccountID FK
Discretionary field (Yes, No)

- (if a SubPortfolio is discretionary then it belongs to a portfolio if it is non-discretionary then it belongs to an account. That is why I have the account field here. It is not a true hierarchy.)

There are other tables with information specific to each of these groups. I would like 1 main form that lays out the information in the 3 tables and also has the associated information available. I thought they could be laid out on 3 levels of tabs.

The Subforms displayed will always be the same only their records will vary. (I think Ilkhoutx is changing the actual subforms displayed?) Would a cascading combo box allow me to ‘whittle’ down the records? Meaning when I switch accounts the correct selection of Portfolios would show up? Then I would need another one to whittle down only the correct SubPortfolios? I have a Mile-O-Phile tutorial on cascading combo boxes waiting to be studied so I think I could implement this.

The major issue is how I structure the Form/Subform and master/child links. If someone could look at my tables and propose an idea I would really appreciate it. I’ll do the legwork but if you could be specific, in the vain of create main form, link these fields, etc… it would really help.

I have two weeks to get this done.

Kind regards, Dwight

 

[Dwight]

I think I got this licked. I just used multiple nested subforms and made sure the linking names were proper as was suggested.

Sometimes you just have to start from scratch and draw it out on paper.

Thanks for the help.

Dwight

 

[llkhoutx]

Agreed.