Dynamic Subforms - synchronize with mainform

[jedraw]

I am trying to use the information provided in Allen Brownes response see
Response.

I have a mainform, a tabctl with 5 pages , 5 subforms and 1 subfrom control. I placed the subformname in the page tag for each of the pages. (That should load the proper subform when the tabctl is clicked).

Now I'm trying to synchronize the subforms with the mainform and am having difficulty. The subform control is not bound.

The mainform contains company information. The link field is EstabNo.
Each subform also has an EstabNo. It is possible that no data will exist for some subforms. In those cases I'd like the tab for that page to be hidden.

Any info would be appreciated.

jedraw

[MSeda]

Have you been successful at altering the subform source object and they are displaying all records or do the subs not load?
Do you have both the Link master fields and Link Child fields for the sub control set to EstabNo?

[jedraw]

Thanks MSeda for replying.

I am getting all records.
I'm trying the approach Allen Browne suggested - 1 subform control and dynamically assigning the subform name to be opened.

I'm not sure where or how to link the child and master fields in code.
Is the link field associated with the subform form or the subform control?

I have 5 tabs, and depending on which tab is clicked, I want to link that subform's field with the Master. The subforms are loading - but I'm getting all records.

Also, some of the subforms for some records will have no data - In those cases I'd like to hide the Tab in the tab control.

The sources for my forms are queries.

jedraw

[MSeda]

Since all of you forms contain the field “EstabNo” you should be able to set the links in the Design view properties window . Select the subform container and view its properties. On the data tab just below the source object (which is blank) you should see Link Master Fields and Link Child Fields just enter “EstabNo” into both of these.

In order to set the visible property of each page of the tab control you are going to have to test for the data when the record on the main form changes.
In the main forms oncurrent event do something like

If isnull(dlookup(“sub1DataPK”, “sub1DataQuery”, “EstabNo = “ & me.EstabNo”)) then
me.page1.visible = false
else
me.page1.visible = true
End if

…Repeat for the other subforms

[jedraw]

Thanks again MSeda.

I now have it working. I just typed in the name for the Child fields / Master fieldsand it worked fine. Previously I clicked the ellipses and Access said I could not build a link between unbound forms.

Thanks for the advice.

Is there any way to improve the speed when reading Maiin form records. It works , but it is a little slow. I have tried the read when using the subform that has least amount of data -that's as fast as I can make it go.

jedraw

[MSeda]

I'm glad you got the sub forms working. The wizard for linked forms is very deceiving about the capabilities of linking fields they are really much more flexible than the wizard indicates. Forms or controls do not neccessarily have to be bound to be used with a link field.

Regarding the speed of records being accessed by the main form , there are a variety of factors that can affect a forms performance.
The first thing I would examine is the forms underlying query. Is the query itself sluggish to open? Is the record based on an unusually large dataset? if so, are their ways you can decrease the number of records returned by filtering or using limiting criteria. The use of domain aggregates can also slow down a query.

Another consideration is how the user navigates the records. If the user has can choose a specific record from a combo box before the query is run that may help since then only one record will be returned.

If you like post the sql from the main form and any of the sub forms as well as any other relevent data and we'll have a look.