Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: Variable report fields

    Variable report fields

    I am looking for an example on how to construct a report whose visible fields change depending on what fields are specified in a query.

    What I am trying to do is construct one report that will display specific test results depending on what our customers require. For example I have one table that lists what tests each customer requires reporting (typically 1-5 tests). I have another table that has all of the test results (about 50 tests). So I want to construct a report which will show only those tests a customer requires. I could construct individual reports for each customer specifying the fields, but that would mean many reports. Doing it this way would allow me to have one report that changes as I need it.

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    I am not sure how much help this example can provide, but take a look at it. Perhaps it can give you ideas to approach your problem:

    LEARN MS-ACCESS TIPS AND TRICKS - Dynamic Report
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am not exactly sure what you are wanting. But if I understand it enough, you can use the Tag property (for text boxes and labels) to show or hide the different information. I have the following code in a report. It works a little differently than you are asking for. I use the OpenArgs parameter in the DoCmd.OpenReport method. And set it to the type of report I want. The values are between 1 and 5. In the Tag property of the controls you put all of the numbers that you want it to appear on. For example if TextBox1 should show on reports 2, 3 and 5 you would put 235 in the Tag property. All of the variable controls are hidden. The code below will unhide the correct controls:

    Code:
    Private Sub Report_Open(Cancel As Integer)
    
    
        Dim intLetterType As Integer
        
        intLetterType = Me.OpenArgs
        SetLetterType intLetterType
        
    End Sub
    
    Public Sub SetLetterType(intLetterType As Integer)
    
        Dim strOpenArgs As String
        Dim varControl As Variant
        
        intLetterType = IIf(intLetterType = 0, 2, intLetterType)
            
        'Now that the letter type has been decided, activate any hidden controls
        For Each varControl In Me.Controls
        
            If varControl.ControlType = acTextBox Or varControl.ControlType = acLabel Then
                
                If InStr(1, varControl.Tag, CStr(intLetterType)) Then
                    
                    varControl.Visible = True
                    
                End If
                
            End If
                
        Next varControl
        
    End Sub
    Keep in mind this code only works for Textboxes and Labels. If you need to work with other controls then you will have to modify the code.

  4. #4
    Join Date
    Nov 2010
    Posts
    4

    Thank you, I will review, but for now here is more information

    Thank for your responses, I have not tried them yet, but I will explain my situation further.

    Our customers require us to report some test data for different batches of material they order. Each customer requires different test data. For one batch of material we have 20 tests we routinely conduct, but a customer may only require data from three tests. I have one table of all batches of material that we produce and the test data we have collected for each batch. So the table is laid out with “batch” and “test 1, test 2 etc” as fields and the data would be the batch number along with the test results for each batch. If I were to query the table to obtain which batches of material a customer orders I would get the correct batch numbers and the corresponding data. Now if all customers wanted the all of the same tests reported, I would put into my report as fields “batch, test 1 test 2, etc” and the report would be fine. But customer A only wants test 1, test 4, and test 5 reported and customer B want test 2, test 6 and test 7 reported. So I to easily accomplish what I need, I could build separate reports for customer A with the fields required, than another report for customer B with another set of required fields and so on for many individual reports. What I am hoping got accomplish is this:

    I have another table indicating which test each customer requires. What I want is to query the required tests for the customer, and have the report adapt the displayed fields to show those listed in the query. This way I have one report for all customers.

    Report for customer A would look like this

    Batch test 1 test 4 test 5
    123 1.2 67 17
    235 1.4 65` 20
    Customer B:
    Batch Test 2 Test 6 Test 7
    123 100 12 1.8
    567 120 13 1.9

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sounds like you need a customer profile
    that allows you to define what test(s) should be reported for what customer(s)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2010
    Posts
    4

    Customer profile?

    Can you please elaborate?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    customer A
    reports the following tests
    1,2,5 & 10
    customer B
    reports the following tests
    2,3,6, 10, 11,12,15

    include all your tests in the report design, but rearange the report on the fly depending on what tests the customer wants reporting
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2010
    Posts
    4

    How is that done?

    Do you mean manually? I am hoping to automatically do that based on a query (or someother method) of a table listing which test a customer wants.

Posting Permissions

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