Fields to output selection!

[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.

 

[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,