Changing which form loads in a subform

[GregoryWest]

I have a form, sort of a menu, with a couple controls to set some parameters in a little database. On this form I currently have a sub-form control. What I need to do, is depending on which of several control buttons on the main form is pressed, the actual sub-form changes to be what the user is asking for.


Think of it as a bank account. The name screen has your name, address etc... And three buttons, "Deposits" "Withdrawals" "Other", The screen shows all their personal information name address phone etc. and below is a listing of all their transactions in a window. If they press "Deposits" The window changes to just showing all their deposits, same for Withdrawals, Other shows back changes etc.


Linking the main form and the sub-form data is optional as I can get the data I need be a couple methods. The problem is when I use ;Me.MainWindow.SourceObject = "Roll-ByLaws"' The form shows up OK, but there is no data in any of the sub-form fields. If I use docmd.openform I get the form in full screen mode covering up the rest of the data on the screen


Anyone done anything like this before?


:banghead:

 

[bastanu]

What are the record sources for the subforms? Are you limiting them by using a control on the main form? Are the references correct for the subforms vs forms (same form opened as form vs stand-alone form).

I do this kind of design from time to time and never had problems.

Cheers,
Vlad

 

[Frothingslosh]

All the time. Have the subform default to no SourceObject and invisible. When you determine which version needs to be loaded, update the subform's SourceObject property, do anything else you need to do to or in the subform, then make it visible.

If you start with a default, skip the invisibility games.

An example from my standard Admin screen:

Private Sub lstSelect_Click()

    Me!sfmSubform.SourceObject = Me.lstSelect.Column(FORM_NAME_COLUMN)
    Me.lblSubform.Caption = Me.lstSelect.Column(FORM_CAPTION_COLUMN)
    Me.sfmSubform.SetFocus
    
End Sub

The subform has no source when the screen opens, but the Load event runs a routine that locates User Maintenance in the list, selects it, and sets the subform to the User Maintenance subform. Every time they click a different option in the list, the indicated form gets loaded into the subform.

 

[Minty]

If you change the subform source make sure you update the relevant Master /Child properties if they also change - if you don't you'll get a blank form.

 

[GregoryWest]

Looks like it would be the Master/Child linking that is the problem. The field in the Master/Child is the same across all the sub-forms, BUT, the queries on the sub-forms is unique to each sub-form.
The code I am using right now is: Me.MainWindow.SourceObject = "Roll-Lists"

I take it I need more parameters to specify the Master/Child relation.

 

[bastanu]

Have you tried to remove the linking fields and limit the records returned by the subform's recordsource instead (using parameters from the main form)?

 

[Frothingslosh]

Yep, what I typically do in this situation is bypass the Master/Child Field properties entirely and base the subforms on parameterized queries.

 

[GregoryWest]

I tried to remove the linked fields and just use the sub-forms query to select the records. This still gave me no records. I then deleted the subform control off the main form, and added a new sub-form with no linking. Again, no data.

 

[bastanu]

can you remove any sensitive data and upload your db?

 

[GregoryWest]

From the main form, the sub-forms properties Source Object is equal to one of the sub-forms. Should this be set to something else?

 

[bastanu]

should be empty if you set it in code

 

[GregoryWest]

Here is the file.

 

[bastanu]

Hi Gregory,
Can you please try the attached file and see if it works as you intended? I have decompiled the one you uploaded (I commented out some of the code in modules, you might be able to put it back as might not impact the functionality) and after a few tries I think the issue was with the linking fields. I added some hidden controls to both the main form and the subforms (txtRollLinkID) and used those in the linking and seems to work for me. Found a problem in the control table (which is a restricted word and shouldn't be used)for the RollLinkID field - had the field size 255 but Format "15" - I think you typed the field size in the format row instead...

Cheers,
Vlad

 

[Deleted member 147267]

Wondering why no one suggested a tab control for each subform as an option?

 

[Frothingslosh]

They can get out of hand if you have enough subforms, plus they all have to load, which can slow everything down badly if you have too many or they're too complicated.

 

[Deleted member 147267]

plus they all have to load

You don't have to load them until you click on the tab, which means no altering of child/parent links, no altering of sources. Loading on tab click shouldn't look any different than swapping forms into a subform control, I would think. Since OP never said how many there were, I figured it was at least an option.

 

[bastanu]

Loading them was the problem, and as you said we would have hit the same wall as swapping forms. It took me a while to make it work, anyone willing to look at it and share their findings?

Here is my code for dynamic loading of subforms:

Private Sub tabMultipleInfoSub_Change()

Application.Echo False
    Call vcHideTabSubforms(Me.Name, "tabMultipleInfoSub")
    Call vcShowTabSubforms(Me.Name, "tabMultipleInfoSub", Me.tabMultipleInfoSub.Value)
    
Application.Echo True


End Sub

Public Functions:

Public Sub vcShowTabSubforms(strForm As String, strTabControl As String, lngPage As Long)
On Error GoTo vcShowTabSubforms_Error

Dim ctrl As Control
Application.Echo False
For Each ctrl In Forms(strForm).Form.Controls(strTabControl).Pages.Item(lngPage).Controls
    If ctrl.ControlType = acSubform Then
        ctrl.SourceObject = DLookup("[SourceObject]", "[tmpRecordSourceLookup]", "[FormName]= '" & strForm & "' AND [SubformName]= '" & ctrl.Name & "'")
    End If
Next
Application.Echo True

vcShowTabSubforms_Exit:
Exit Sub

vcShowTabSubforms_Error:
Application.Echo True

End Sub

Public Sub vcHideTabSubforms(strForm As String, strTabControl As String)
On Error GoTo vcHideTabSubforms_Error

Dim ctrl As Control, pg As Page
Application.Echo False

For Each pg In Forms(strForm).Form.Controls(strTabControl).Pages
    For Each ctrl In Forms(strForm).Form.Controls(strTabControl).Pages(pg.Name).Controls
        If ctrl.ControlType = acSubform Then
            ctrl.SourceObject = ""
        End If
    Next
Next pg

Application.Echo True
vcHideTabSubforms_Exit:
Exit Sub
vcHideTabSubforms_Error:
Application.Echo True

End Sub

And of course I have a table (tmpRecordSourceLookup) to hold the required info:
FormName(Text)/SubFormName(text)/SourceObject(text).

Cheers,
Vlad

 

[Deleted member 147267]

Intriguing that you're using a module level procedure for this rather than the form module itself. Why not just load the form on tab control click event? Why not just Me.TabControl(name or index)? Also, I wonder why loop through a controls collection when you seem to know the form, the control name and the page name or index. But that seems to be the lesser point.

 

[bastanu]

Because I use the same module for multiple forms, and on each tab could be multiple subforms.

 

[GregoryWest]

I thought about tab controls, but opted for this method simply because the user is far more used to this format.

Wondering why no one suggested a tab control for each subform as an option?