Sub table on datasheet form

[nancy54]

Hello,
I have a table with a relationship to another table which creates a sub table(in the way of a plus sign to the left of each row). When I created a datasheet form from that table, I lost the sub table (plus signs). How do I get them to appear on the datasheet form?

 

[June7]

You have to establish a subform in DesignView first.

I have NEVER used subdatasheet.

 

[nancy54]

I tried to create a sub form in design view, but the table I'm trying to link is an imported table from an external database and is not an option on the list of tables to choose from for the Record Source??

 

[June7]

Well, that's odd. The table is actually imported or just linked? In either case, should be able to build form with it.

 

[isladogs]

You can add subdataheets to forms but the method isn't the same as for tables where its done via the ribbon

Create the forms for each table (it doesn't matter whether these are local or linked)
Open the top level form in design view and drag the subdatasheet form onto the top level form. Repeat if you have further subdatasheet forms

I only use this method on one app where I create tables & forms based on imported JSON data and want to show the relationship between them.

Attached are 3 screenshots showing the subdatasheets :
1. collapsed
2. partly expanded
3. fully expanded

In general I would advise against using this approach as it can make viewing the data quite confusing for end users

 

[nancy54]

This is exactly what I want!... but all of the records are showing in expanded view, how can I get then all to collapse?

Nancy

 

[isladogs]

My form has a button cmdExpandAll which can toggle expand all/collpase all.

Private Sub cmdExpandAll_Click()

On Error GoTo Err_Handler

     Me.fsubTables.SetFocus[COLOR="SeaGreen"] 'this is the main top level subform [/COLOR]

    If Me.cmdExpandAll.Caption = "Expand All Subdatasheets" Then
        DoCmd.RunCommand acCmdSubdatasheetExpandAll
        Me.cmdExpandAll.Caption = "Collapse All Subdatasheets"
    Else
        DoCmd.RunCommand acCmdSubdatasheetCollapseAll
        Me.cmdExpandAll.Caption = "Expand All Subdatasheets"
    End If
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    If Err = 2046 Then Resume Next
    MsgBox "Error " & Err.Number & " in cmdExpandAll _Click procedure : " & Err.Description, vbOKOnly + vbCritical
    Resume Exit_Handler

End Sub

HTH

 

[nancy54]

To June7,
In the property Sheet, format, Subdata sheet Expanded- I had to change to No. All is good. Thanks so much!!

 

[isladogs]

Ah sorry, thought you were responding to me earlier.
You might still find it useful to add a button to expand all/collapse all records using code as in post #7.