Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    6

    Unanswered: Subreport Control with VBA not showing in Report Header

    Control on main report that refers to a control on my subreport is blank when using VBA, it works if no VBA is used.

    If I use IIF instead it works great but here are too many variables so IIF will not work, hence my need to use VBA. The data needs to go into the main report header it works in the main report footer just fine.

    I am taking the total of the subreport from the footer and it shows in the main report footer just fine. I can't get the total from the footer into
    the main report header, I get #Name?. I made sure the text box on the subreport is not a calculated box, it simply looks at another box that has the subtotals summed. Other box totals transfer fine, just the boxes that depend on VBA don't work. URgggg.

    Similar issue with Access 2003 (but I am using Access XP):
    BUG: A control on a form or on a report that refers to a control on a subform or on a subreport is blank in Access 2003 (Q883867)
    The fixes for the issue in 2003 don't work with mine however.

    Subreport works fine and shows data on main report just fine, however the total from the subreport does not show in the main report header. Other data shows fine, the text controls that have VBA done on them blow up.

    I've tried creating a macro that calls a function and things get even worse, I can't seem to load it from onpage, onopen, onactivate and get it to work either.

    Right now the vba looks like this:

    '**********
    Option Compare Database
    Option Explicit
    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

    Dim DomeSize1 As String
    Dim DomeSize2 As String
    Dim DomeSize3 As String


    DomeSize1 = [txtAD1]
    DomeSize2 = [txtAD2]
    DomeSize3 = [txtAD3]

    Select Case DomeSize1
    Case "093Bronze"
    txtAdome1 = 0.81
    Case "093Clear"
    txtAdome1 = 0.73
    Case "093Grey"
    txtAdome1 = 0.81

    '***** etc, etc.... 72 variations of this

    The code looks at the value of txtAD1,txtAD2,txtAD3 and assigns a cost into txtAdome1,txtAdome2 and txtAdome3 of which some are going to have 0 values.

    Thanks for reading and helping if you can!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Can you do it in a separate query? The only way I know of to get totals in the header is to create a separate query that does the total. Then add that total to each of the detail records. When you reference the field in the header everything works fine.

    You might have to write your function in a module and reference the function in your query if and IIF is still not manageable.

Posting Permissions

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