Results 1 to 5 of 5
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    confused and bewildered about adding record data to a report header

    I have a subreport with a source of 2 related tables (linked on the field [type] in a 1 to many relationship)

    Fields in the first table (the “1” table) should appear once in the report (in the report header)
    All of the related fields in the second table (the “many” table) appear in the detail

    All of the fields are in the data source and on the report as a control

    The controls related to the first table are in the header section
    The controls related to the second table are in the detail section


    As such, the code below throws the error “can’t find the field…."
    HOWEVER,
    If I move all of the controls… into the detail section, everything is possible, except that now the fields form the “1” table appear in front of every record of the “many” table, as I would expect them to but do not want them to….

    Code:
      Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
      With Me
    
          ‘show reference to notes at footer
    
          If .InstallationNotesAtFooterYN = True Then
              If Len(Nz(InstallationNotesAtFooterAbout, "")) > 0 Then
                  .txtInstallationNotesAtFooterDescribed_text = "See additional notes at the end of the schedule about " & _
                      [InstallationNotesAtFooterAbout] & "."
    
              Else
    
                  .txtInstallationNotesAtFooterDescribed_text = "See additional notes at the end of schedule."
    
              End If
    
              .txtInstallationNotesAtFooterDescribed_text.Visible = True
          Else
    
              .txtInstallationNotesAtFooterDescribed_text = ""
    
              .txtInstallationNotesAtFooterDescribed_text.Visible = False
          End If
       
          ' show reference to mounting detail
          If [InstallationSeeSketchYN] = True Then
              .txtInstallationSeeSketchYN_text.Visible = True
              .txtInstallationSeeSketchYN_text = "Refer to fixture mounting detail for additional information"
          Else
              .txtInstallationSeeSketchYN_text.Visible = False
          End If
       
          ' note that focusing is required
          If [FocusingReqdYN] = True Then
              .txtFocusingReqdYN_text.Visible = True
              .txtFocusingReqdYN_text = "Focusing is required"
              If [FocusingReqd_TDAsupervision] = True Then
    
                  .txtFocusingReqdYN_text = .txtFocusingReqdYN_text & "; TDA to supervise labor provided by others."
    
              End If
    
          Else
              .txtFocusingReqdYN_text.Visible = False
          End If
       
      End With
      End Sub
    Wandering aimlessly in new territory;
    any suggestions will be greatly appreciated with thanks in advance,
    Mark

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please expand on this for clarity.
    All of the fields are in the data source and on the report as a control
    What exactly is the RecordSource of your main report?

  3. #3
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    the record source is a query that includes all of the necessary fields from both of the tables

    what I've noticed is that if I'm concatenating, in VBA, multiple fields to form a a string of text (for each record in the report) that will be assigned to a textbox control in the report (typically in the detail section) , I have to have that field assigned to a (hidden) control in the detail section so that I can reference the value in the VBA code (if i don't do that, then the code does not know what value I'm adding to the string and tosses an error back).
    (see code sample below)

    so with that logic, I've made certain that all of the fields used in this report are assigned to a textbox on the report so that the VBA can determine what the values of the fields are.
    (this might be a circuitous route, but it was what I was able to figure out a long time ago, and it works -an easier way would be nice, but I haven't found that yet !)

    _______
    in this code, I'm looking at the values of the fields MockUpRequiredYN and MockUpDescription, and assigning a concatenated text strMockUp_Text to the report detail control txtMockUp
    Code:
            'add mock-up information
            strMockUp_Text = ""
            If ReportParms!vInclMockUpComment Then
                If [MockUpRequiredYN] Then
                    strMockUp_Text = "MOCK-UP REQUIRED"
                    If Len(Nz(MockUpDescription, "")) > 0 Then
                        strMockUp_Text= strMockUp_Text & ": " & "Include " & [MockUpDescription]
                    End If
                End If
                me.txtMockUp = strMockUp_Text
            End If

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I haven't followed all of your details, but the following might be options:
    You could have a separate hidden table with the control and data you need to reference.
    Or you could use TempVars, and reference the value(s) as needed.

  5. #5
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    and all it was is that I needed to subdivide the report by leaving data from the first table (the “1” table) in that report, and moving the data from the second table (the "many" table) into a nested subreport (that simple).

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

Similar Threads

  1. Loss of page header data in sub-report.
    By Thompyt in forum Reports
    Replies: 5
    Last Post: 10-02-2014, 09:54 AM
  2. Totally Bewildered
    By PeeODE in forum Access
    Replies: 2
    Last Post: 07-31-2014, 10:14 AM
  3. Replies: 1
    Last Post: 08-06-2013, 07:52 PM
  4. Export Data for Distinct Value from Report Header
    By ertweety in forum Programming
    Replies: 6
    Last Post: 06-04-2012, 05:09 PM
  5. Replies: 32
    Last Post: 06-20-2011, 07:30 PM

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