Results 1 to 8 of 8
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    labels on report based on a cross-tab


    Hi to all,

    A report is based on a cross-tab query. The column headings are based on a table limited to only 8 records maximum. The user until now has only 5 records in the table. He can add another 3 fields.
    The 5 labels on the report have their caption manually written. My issue is that when the user adds another field to the table, I need to update the label caption manually in the design view of the report.

    Is it possible to put 8 labels on the report that the caption in each label use the field value from the original table or the cross-tab query ?

    Khalil

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    attached is a db with a report and class, when the report opens, it takes the fields in the query assigned (in report OPEN event)
    and renames the fields & labels with those in the query.

    I havent used this in MANY years, but you can review and alter to your needs.

    An alternate method is to make a 'report' table (tXtabReport) that has every possible field the Xtab will create.
    Then run a macro that has N append queries to add the 1 field to the 1 field in the tXtabReport tbl.
    then the report will sum all values of the tXtabReport
    Attached Files Attached Files

  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Khalil Handal View Post
    Is it possible to put 8 labels on the report that the caption in each label use the field value from the original table or the cross-tab query ?
    Hi Khalil,

    1. For fixed header,
    specify in the Column Headings property of the crosstab query the captions of the fields, separated by semicolon. For example “Field1”;”Field2”;”Field3”;…”Field8”.

    2. For a dynamic header,
    create a new crosstab query based on the original table (8 columns in one row), a new report based on this one-row crosstab query and use this tiny report as header in the main report.

    Good luck,
    John

  4. #4
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    Thank you both for the reply.

    I am not sure if was clear enough.
    I used the database in #2 and it shows the name of the fields names in the table.
    The table has two fields only primary key and a institName. I want to have the values of the fields (the values the user can add. The user can add up to 9 records).

    Khalil

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    So, you have to go with the solution 2. (For a dynamic header)

    Create a single-row crosstab query with the institName as Column Heading and Value.
    For example:
    Code:
    TRANSFORM First(institName) AS theValue 
    SELECT 1 AS theRow
    FROM YourTableName
    GROUP BY 1
    PIVOT institName;
    Create a report based on the query above and use its Detail section (the fields) as header in the Report/Page Header of the main report (report in report). Align the fields of the subform (the “header”) with the fields of the main form.

    I hope I’m clear now.

  6. #6
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    I tried the second method trying to have a subreport based on a crosstab query and i received the following error:

    you can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subquery

    What to do now?
    Is it possible to use a VBA to get the values form the table or crosstab query on the open event of the report ?

    Khalil



  7. #7
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Khalil Handal View Post

    Is it possible to use a VBA to get the values form the table or crosstab query on the open event of the report ?
    Yes, of course. You can do something like this:
    Code:
    Private Sub Report_Open(Cancel As Integer)
        Dim rs As DAO.Recordset
        
        Set rs = CurrentDb.OpenRecordset("SELECT ID, fName FROM tblNames", dbOpenForwardOnly)
        With rs
            While Not .EOF
                Me.Controls("Label" & !ID).Caption = !fName
                .MoveNext
            Wend
            .Close
        End With
        Set rs = Nothing
    End Sub
    Also, take a look in attachment that includes two ways: with VBA and with subreport.
    Attached Files Attached Files

  8. #8
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi to all,

    Thank you very much for the help.
    It is Working

    Khalil

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

Similar Threads

  1. Report based on a cross Tab Query
    By chavez_sea in forum Reports
    Replies: 43
    Last Post: 12-05-2018, 03:31 PM
  2. Labels/Report Based Upon Query
    By streub in forum Access
    Replies: 8
    Last Post: 11-28-2017, 10:45 AM
  3. Replies: 4
    Last Post: 08-15-2017, 08:44 AM
  4. Replies: 3
    Last Post: 02-25-2016, 09:11 AM
  5. Replies: 14
    Last Post: 07-19-2011, 04:00 PM

Tags for this Thread

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