Fields to output selection! (1 Viewer)

[Wolf]

I have a report that is based on a query that has 30 columns,
Now of course it wont output all columns on 1 page, so I would like to have a field selection on the form that opens that report,
For ex. I have Name, address, city, zip, phone, SS, DOB, Destination, etc.

I would create unbound check-boxes for all of them or an unbound multiselect listbox, where the user can choose which columns to show on the report.

Is that a good idea?

What code do I put on the report open event?

Also, is it possible to move up the fields if the user dosn't select to show them, lets say if user selects name and DOB, it should only show name and DOB without the space for the non visible columns?

Obviously the can grow property is not an option because that grows vertically not horizontally.

 

[Pat Hartman]

Access is unsatisfactory when trying to construct variable output reports. You might find Excel a better tool or even Word since both have better rendering engines that allow you to size to fit the output. And, much as I hate working with Word, that might be the easiest solution. I would create a Word template with landscape orientation. I would then add some bookmarks to use to enter the report header and the date or the header could be hard-coded and the date could be a word field so it will fill automatically. The important bookmark is the one that will hold the "table" you are constructing in code.

Your code will create a string that contains the column headers separated by comas and ending with a crlf. It will then open a query that was created from the columns selected by the user and create each row with data separated by a coma and ending with a crlf. Once the string is created, place it in the bookmark and while the text is seill selected, convert it to a table and adjust the column widths to fit. The following code snippet takes a bookmark name and a string and inserts it into an open word doc.

Public Sub FinishTable(bkmk As String, strTable As String)
    
    On Error GoTo Proc_Err

   '''' WordApp.Visible = True  'uncomment for testing
    
    Call InsertTextAtBookMark(bkmk, strTable)
    Set objTable = WordApp.Selection.ConvertToTable(Separator:=vbTab)
    objTable.AutoFormat Format:=wdTableFormatProfessional, applyshading:=False, applyHeadingrows:=True, AutoFit:=True
    objTable.AutoFitBehavior (wdAutoFitWindow)
    WordApp.Selection.MoveRight unit:=wdCell
    WordApp.Selection.MoveRight unit:=wdCell
    WordApp.Selection.SelectRow
    WordApp.Selection.Font.Bold = wdToggle
    WordApp.Selection.Shading.Texture = wdTextureNone
    WordApp.Selection.Shading.ForegroundPatternColor = wdColorAutomatic
'    WordApp.Selection.Shading.BackgroundPatternColor = -603923969 'color # too low for W2003 - caused -2145263334 error and 462 error
    Set objTable = Nothing

    
Proc_Exit:
    Exit Sub
    
Proc_Err:
    Select Case Err.Number
        Case 4605 'this method or property is not available because the object is empty
            Resume Proc_Exit
        Case 5941 ' member does not exist
            Resume Proc_Exit
        Case 5825   'Object has been deleted
            Resume Proc_Exit
        Case 91     'object variable not set
            Resume Proc_Exit
        Case 4218   'type mismatch
            Resume Proc_Exit
        Case Else
            MsgBox Err.Number & " - " & Err.Description
            Resume Proc_Exit
    End Select
    Resume Proc_Exit
End Sub
Public Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)
    Dim BMRange As Word.Range
    
    On Error GoTo Proc_Err
    
    Set BMRange = WordDoc.Bookmarks(strBkmk).Range
    '---- changes here ----
    'WordDoc.Bookmarks(strBkmk).Select    ' subsequent processing refers to selection
    BMRange.Text = varText & ""
    WordDoc.Bookmarks.Add strBkmk, BMRange
    BMRange.Select
    
Proc_Exit:
    Exit Sub
Proc_Err:
    Select Case Err.Number
        Case 4605 'this method or property is not available because the object is empty
            Resume Proc_Exit
        Case 5941, 6028 ' member does not exist/the range cannot be deleted
'            MsgBox "Bookmark {" & strBkmk & "} There is a mapping error with this document.  Please contact your administrator.", vbOKOnly+vbInformation
            Resume Proc_Exit
        Case 91     'object variable not set
            Resume Next
        Case 4218   'type mismatch
            Resume Proc_Exit
        Case Else
            MsgBox Err.Number & " - " & Err.Description
            Resume Proc_Exit
    End Select
    Resume Proc_Exit
End Sub

 

[MarkK]

Check out the Word.ContentControl class, new in 2007. In some respects it's like having the concept of Excel's named range in a word document environment. And you can set it so the user can't move or delete--or edit the contents of--the control.
Anyway, I'm just working on an Access-based Word document handling utility now, and I'm pleasantly surprised. Here's sample code that enumerates the ContentControls collection of a Word.Document, and checks the .Title property of each control, and pushes in appropriate data for whatever it finds ...

[FONT="Lucida Console"][SIZE="1"]Private Sub UpdateFromEstimate()
    Dim cc   As Object [COLOR="Green"]' Word.ContentControl[/COLOR]
    Dim scn  As Object [COLOR="Green"]' Word.Section[/COLOR]
     
    With WD.ActiveDocument
        For Each cc In .ContentControls
            Select Case cc.title
                Case "Date"
                    cc.Range.Text = Date
                Case "CurrentUser_Fullname"
                    cc.Range.Text = User.FullName
                Case "Customer"
                    cc.Range.Text = Estimate.CustomerName
                Case "CustomerContact_Fullname"
                    cc.Range.Text = Estimate.Contact.FullName
                Case "CustomerContact_Email"
                    cc.Range.Text = GetCustomerContactData("Email")
                Case "CustomerContact_Fax"
                    cc.Range.Text = GetCustomerContactData("Fax")
                Case "CustomerContact_Phone"
                    cc.Range.Text = GetCustomerContactData("Phone")
                Case "JobName"
                    cc.Range.Text = Estimate.JobName & " - Millwork Quote"
                Case "Address"
                    cc.Range.Text = Estimate.Address.FullTextOneLine
                    cc.Range.Font.Color = vbBlack
                Case "DocumentTitle"
                    cc.Range.Text = "QUOTATION #" & Estimate.EstimateID
                Case Else
                    Debug.Print "cc named: " & cc.title & " was not assigned a value."
            End Select
        Next
          
        For Each cc In .Sections(1).Headers(wdHeaderFooterFirstPage).Range.ContentControls
            Select Case cc.title
                Case "FromEmail"
                    cc.Range.Text = User.Email
            End Select
        Next
    End With
    
End Sub[/SIZE][/FONT]

Here's a sample that consecutively inserts new content controls into a Word.Selection object ...

[FONT="Lucida Console"][SIZE="1"]    With WD.Selection
[COLOR="Green"]        'insert job section content controlled labels[/COLOR]
        Dim wcc As Object[COLOR="Green"]   ' Word.ContentControl[/COLOR]
        Dim cjs As cSection
        For Each cjs In job.Sections
            Set wcc = WD.ActiveDocument.ContentControls.Add(wdContentControlText)
            wcc.title = cjs.FullName
            wcc.Range.Text = "#" & job.JobNumber & "  " & cjs.FullName
            wcc.Range.Bold = True
            wcc.Range.Underline = True
            .Start = wcc.Range.End + 1
            .TypeParagraph
            .TypeParagraph
        Next
    
    End With[/SIZE][/FONT]

...and then there are still the Word.ContentControlListEntry class, and Word.ContentControlListEntries collection, which I haven't even looked at yet.

It seems an oversight to me that the Word.ContentControl class doesn't raise events that I can handle in code like an Access.Textbox. It seems like it would be extra powerful if you could cancel the user's updates, or automatically fill in other controls based on the user's current selections as they happen.

Cheers,