Results 1 to 4 of 4
  1. #1
    MatP is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    7

    Report to hide/show colums as per source form

    Hi all,



    So I have a form that my users utilize to view data. The users are particular about the colums they want to see on the report. They typically move columns around, and hide/show some of them. Once the form looks the way they want, they then create a report that is subsequently printed or sent to pdf.

    I'm not too worried about the order of the columns (but insight is welcomed). My main interrogation is about the visible fields. Is there a way to have the report hide/show columns based on what is hidden/shown on the source form?

    Thanks everyone,

  2. #2
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    From the report’s side, in Detail_Format event procedure, you have to deal with the ColumnHidden and ColumnWidth properties of the controls of the source form.

    Try something like this:
    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        Dim cTxt As Control 'Pointer to the textbox in report's Detail
        Dim cCol As Control 'Pointer to the textbox in the subform of the source form
        Dim frm As Form     'Pointer to the source form
    
        On Error Resume Next
        Set frm = Forms("SourceFormName").SubFormName.Form
    
        If Not frm Is Nothing Then
            For Each cTxt In Me.Detail.Controls
                Set cCol = Nothing
                With cTxt
                    If TypeOf cTxt Is TextBox Then
                        'Only for controls with the same name.
                        Set cCol = frm.Controls(.Name)                  'Set a pointer to the forms's textbox.
                        If cCol Is Nothing Or cCol.ColumnHidden Then    'Is hidden in DS or doesn't exists in source form.
                            .Width = 0                                  '"Hide" this column.
                        Else
                            .Width = cCol.ColumnWidth                   'Adjust the Width of column.
                        End If
                    End If
                End With
            Next cTxt
        End If
        Set frm = Nothing
        On Error GoTo 0
    End Sub
    I hope it helps.
    John

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I have used the code below to save the user settings for datasheet forms between front-end updates, and I think it could work for you.

    Try to save the datasheet form that your users are manipulating as another form and use that as a subreport embedded in an empty report (with title,etc.).

    In the Load event of the new form add this line:
    Code:
    Call LoadUserColumnSetup(Forms!frmYourOriginalDatasheetForm)

    Put in new module:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Sub LoadUserColumnSetup(ByRef frm As Form)
        Dim Ctl As Control
        Dim strBlob As String
        Dim strColumns() As String
        Dim intColumns As Integer
        Dim intColumn As Integer
        Dim strValues() As String
        
        On Error Resume Next
        
        ' Only apply to forms in datasheet view.
        Const cDatasheetView As Long = 2
        If frm.CurrentView <> cDatasheetView Then Exit Sub
        
        strBlob = GetSetting("DB_Columns", "Settings", frm.Name, "")
    
    
        If strBlob <> "" Then
          Call GetOrderedColumns(strBlob, strColumns)
        
          intColumns = UBound(strColumns) + 1
          If intColumns <> 0 Then
            For intColumn = 0 To intColumns - 1
              If Trim(strColumns(intColumn)) <> "" Then
                 strValues = Split(strColumns(intColumn), ":")
                 Set Ctl = frm.Controls(strValues(0))
                 Ctl.ColumnOrder = CInt(strValues(1))
                 Ctl.ColumnHidden = CBool(strValues(2))
                 Ctl.ColumnWidth = CLng(strValues(3))
              End If
            Next
          End If
        End If
    
    
    End Sub
    
    
    Private Sub GetOrderedColumns(ByVal strData As String, _
                                  ByRef strColumns() As String)
        Dim strTemp() As String
        Dim intCols As Integer
        Dim intCol As Integer
        Dim intCurr As Integer
        Dim strValues() As String
        
        On Error Resume Next
    
    
        strTemp = Split(strData, vbCrLf)
        intCols = UBound(strTemp) - 1
        
        ReDim strColumns(intCols)
        For intCol = 0 To intCols
            For intCurr = 0 To intCols
                strValues = Split(strTemp(intCurr), ":")
                If CInt(strValues(1)) = intCol + 1 Then
                    strColumns(intCol) = strTemp(intCurr)
                    Exit For
                End If
            Next
        Next
        
    End Sub
    
    
    Public Sub SaveUserColumnSetup(ByRef frm As Form)
        Dim Ctl As Control
        Dim strBlob As String
        Dim strCtl As String
        
        On Error Resume Next
    
    
        ' Only apply to forms in datasheet view.
        Const cDatasheetView As Long = 2
        If frm.CurrentView <> cDatasheetView Then Exit Sub
        
        For Each Ctl In frm.Controls
          Select Case Ctl.ControlType
            Case acLabel, acLine, acSubform, acCommandButton
                ' do nothing for these controls.
            Case Else
              strCtl = Ctl.Name & ":" & _
                       Ctl.ColumnOrder & ":" & _
                       Ctl.ColumnHidden & ":" & _
                       Ctl.ColumnWidth & vbCrLf
              strBlob = strBlob & strCtl
          End Select
        Next
        
        SaveSetting "DB_Columns", "Settings", frm.Name, strBlob
    
    
    End Sub
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry I forgot to mention that you need to add Call SaveUserColumnSetup(frmYourOriginalDatasheetForm) in the Unload event of the original datasheet form and also in the OnClick event of the button opening the report.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-23-2015, 02:38 PM
  2. Hide/Show a row on a multiple items form
    By Historypaul in forum Programming
    Replies: 10
    Last Post: 04-20-2014, 10:34 PM
  3. Replies: 1
    Last Post: 09-27-2013, 12:49 PM
  4. List in Report Won't Show a Column in the Source Table
    By italianfinancier in forum Access
    Replies: 1
    Last Post: 05-28-2011, 02:23 AM
  5. Show/Hide Form
    By sparlaman in forum Forms
    Replies: 5
    Last Post: 05-16-2011, 11:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums