Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Expand Datasheet? (2002)

    I have a tab control: TabJobCost, a subform: 71200-frmChartOfAccounts-Mainform and a sub sub form: Query.99865-qryChartOfAccounts that gets it's records from Query.99865-qryChartOfAccounts.
    I need buttons on TabJobCost that expand and collaspes selected records. The Selected records are accounts in chart of accounts tquery hat gives details to the account, ie Chart of Accounts, Chart of Accounts Line Items and Chart of Accounts Items. Each is a subdatasheet.
    Frank Hoeffer

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Expand Datasheet? (2002)

    I don't understand. Why not just click on the + signs for the subdatasheet to expand it?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Expand Datasheet? (2002)

    I want a button that expands or collases ALL. or a specific record.
    The + signs are too small for my client.
    Frank

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How to Expand Datasheet? (2002)

    This assumes you have main form and datasheet subform with sub-subform. Note: It does not matter if subform is on tab control or not. The primary subform records are the ones to be expanded or collapsed to display related sub-subform records. Create 2 command buttons on main form and use following event procedures:

    <pre>Private Sub EXPAND_btn_Click()
    SubformSetFocus
    DoCmd.RunCommand acCmdSubdatasheetExpandAll
    End Sub

    Private Sub COLLAPSE_btn_Click()
    SubformSetFocus
    DoCmd.RunCommand acCmdSubdatasheetCollapseAll
    End Sub</pre>

    These two subs use SubformSetFocus sub:
    <pre>Private Sub SubformSetFocus()

    Me.frmOrdersSubform.SetFocus
    Me.frmOrdersSubform![OrderID].SetFocus

    End Sub</pre>

    This sub moves focus to the subform (frmOrdersSubform in this example) whose records are being expanded/collapsed. If focus not on subform, error will result when running Expand All or Collapse All commands. This was only tricky part, you have to set focus on subform, and then a control on subform, for this to work. Otherwise if sub-subform had focus, Subform.SetFocus by itself did nothing and runtime error occurred. Replace frmOrdersSubform and [OrderID] in example above with names used by your subform. I used separate sub to move focus to subform to avoid repetitive code in the 2 event procedures.

    NOTE: I don't know of any way to specify that only selected record on subform be expanded or collapsed; it's apparently all or none! Individual records would have to expanded/collapsed "manually".

    HTH

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    That did it!

    That did it-much thanks for your brillant help.
    Frank <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Well almost.

    If there are sub sub datasheets how do you transverse down to the next sub datasheet and expand ALL?
    Frank

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: SubSubDataSheets (ACC 2002)

    If you have a sub-sub-subform (3 levels of subforms) code has to be modified. I used 4 command buttons on main form: EXPAND ALL, EXPAND ONE, COLLAPSE ALL, COLLAPSE ONE. The Expand All button was simple to modify. (The Collapse All button did not need to be modified). Revised code:
    <pre>Private Sub ExpandAll_btn_Click()
    With Me
    .frmOrdersSubform.SetFocus
    .frmOrdersSubform![OrderID].SetFocus
    DoCmd.RunCommand acCmdSubdatasheetExpandAll
    .frmOrdersSubform![frmOrderDetailsSubform].SetFocus
    .frmOrdersSubform![frmOrderDetailsSubform]![OrderID].SetFocus
    DoCmd.RunCommand acCmdSubdatasheetExpandAll
    End With
    End Sub</pre>

    The Expand One and Collapse One buttons were a bit trickier. There is no good way I am aware of to determine if a given subdatasheet is in expanded or collapsed state. So code resorts to error-handling to expand/collapse one level up or down:
    <pre>Private Sub ExpandOne_btn_Click()
    On Error GoTo Err_Handler

    Dim strMsg As String
    With Me
    .frmOrdersSubform![frmOrderDetailsSubform].SetFocus
    .frmOrdersSubform![frmOrderDetailsSubform]![OrderID].SetFocus
    DoCmd.RunCommand acCmdSubdatasheetExpandAll
    End With
    Exit_Sub:
    Exit Sub
    Err_Handler:
    If Err = 2110 Then 'Can't move focus to control
    With Me
    .frmOrdersSubform.SetFocus
    .frmOrdersSubform![OrderID].SetFocus
    DoCmd.RunCommand acCmdSubdatasheetExpandAll
    End With
    Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "EXPAND ONE ERROR MSG"
    End If
    Resume Exit_Sub
    End Sub</pre>

    <pre> Private Sub CollapseOne_btn_Click()
    On Error GoTo Err_Handler

    Dim strMsg As String
    With Me
    'Next line generates error 2110 if not all expanded:
    .frmOrdersSubform![frmOrderDetailsSubform]![frmOrdersDetailsProductsSubform].SetFocus
    .frmOrdersSubform![frmOrderDetailsSubform].SetFocus
    .frmOrdersSubform![frmOrderDetailsSubform]![OrderID].SetFocus
    DoCmd.RunCommand acCmdSubdatasheetCollapseAll
    End With
    Exit_Sub:
    Exit Sub
    Err_Handler:
    Select Case Err
    Case 2110 'Can't move focus to control
    With Me
    .frmOrdersSubform.SetFocus
    .frmOrdersSubform![OrderID].SetFocus
    DoCmd.RunCommand acCmdSubdatasheetCollapseAll
    End With
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "COLLAPSE ONE ERROR MSG"
    Resume Exit_Sub
    End Select
    End Sub</pre>

    I tested this with forms based on NorthWind.mdb database. The attached zip file demonstrates how this technique works. It contains 4 forms (main form & 3 subforms) and 4 queries for forms. Tables are all linked to NorthWind.mdb located at default path (Access 2002): C:Program FilesMicrosoft OfficeOffice10Samples. If your copy of NorthWind.mdb is located elsewhere, will need to reset links using Linked Table Manager (Tools menu).

    NOTE: This technique will work ONLY with Access 2002 (aka Access XP)!! Apparently the Expand & Collapse All commands are not included in A2K and earlier; will get error msg in A2K!!

    HTH
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubSubDataSheets (ACC 2002)

    Thanks very much well try it shortly.
    Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •