Changing subform sourceobject

[assassin4213]

Hello All,

I have a subform that I would like to be able to change the sourceobject on with command buttons or even combobox. This way the users of the database can view prefabbed data sets without having to constantly close windows. I prefer to use the command buttons in order to keep things simple for the end user.

Let me know what your thoughts are.

Thanks,

Greg

 

[dcx693]

Are you asking if we think it's a good idea, or asking how to achieve what you suggest?

I think it's a fine idea. The less complicated the UI is, the easier it will be for more of the users.

The syntax for changing a subform recordsource is:
Me.subformName.Form.Recordsource=sql statement or querydef

 

[ghudson]

I do that but I change the subforms SourceObject by loading a different
subform within my main form using the AfterUpdate event of a combo box.
My example is using a combo box named cbData.

Private Sub cbData_AfterUpdate()
On Error GoTo Err_cbData_AfterUpdate
    
    Select Case cbData
    
    Case 1
        If cbData = 1 Then 'XYZ Data was selected
        SubForm.Visible = True
        lblSubForm.Caption = "XYZ Data"
        SubForm.SourceObject = "subXYZ"
        End If
    
    Case 2
        If cbData = 2 Then '123 Data was selected
        SubForm.Visible = True
        lblSubForm.Caption = "123 Data"
        SubForm.SourceObject = "Sub123"
        End If
    
    Case Else
        cbData.SetFocus
        SubForm.Visible = False
        lblSubForm.Caption = "Select an imported table for viewing!"
    
    End Select
    
Exit_cbData_AfterUpdate:
    Exit Sub
    
Err_cbData_AfterUpdate:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_cbData_AfterUpdate
    
End Sub

HTH

 

[assassin4213]

Thanks

It would have helped if I wrote the question (been watching too much Jeopardy). I was looking for a way to change the subform source for different queries to be viewed instead of having to make several different forms and program a ton of extra code to make them visible/invisible. Thank you for your suggestions. I will work with them. If you wish to expand on them, feel free.