asked on

Formatting group totals in Access

Gentlemen:

Hope this will be easy points for someone.

I have attached a PDF of the first page of a "sanitized" report. I am getting a total for a group of accounts... That's working correctly; however, when there is only ONE account in a grouping code I just want to print the detail, and NOT the automatic sub-total. Also, a minor point.... Would like an underline at the appropriate location....

See the attached PDF for the first page of the report that shows what I need.

Any and all assistance is greatly appreciated.

Thanks,
Rick Norris+AccessReport.pdf

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rick Norris

ASKER

Pat:

Understand above verbiage; however used the below formula in the "on Format" event in the total section.... Must be doing something wrong.... totals still show, and the value of Const1 on the total line is ONE.

Comments please.

=IIf([Const1]=1,[2013BALANCE].[Visible]=False,"")

PatHartman

I don't believe you can affect the state of a control this way. You need to write VBA in the Format event for the section

If Me.Const1 = 1 Then
Me.2013Balance.Visible = False
Me.lbl2013Balance.Visible = False
Me.objLine.Visible = False
Else
Me.2013Balance.Visible = True
Me.lbl2013Balance.Visible = True
Me.objLine.Visible = True
End If

Rick Norris

ASKER

I think I may ALMOST be there.... I just tested with just the one total (2013Balance) to see if I could just turn off it's visible property. I put a stop at the below section; however, code never executed????

In the "on Format" event of the property sheet for GroupFooter2 it is now set to: [Event Procedure]. I'm very adept at C#, but this is really getting to me.... should be soooo simple... What am I missing????

Code I have now is:

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
If Me.Const1 = 1 Then
Me.AccessTotals2013BALANCE.Visible = False
Else
Me.AccessTotals2013BALANCE.Visible = True
End If
End Sub

SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rick Norris

ASKER

Helen:

Thanks for the post.... Const1 is a new field that I set to a default value of 1.. (Yes, I made sure each record had the 1 after I created the new field). I put this in the detail of the report, and summed... When there is only one line of detail for a group, then I just get the value of 1 in the footer of that group. It seems your code for "group" (last example) should work perfectly; however, when I implement your suggestion, nothing happens..... I even put a "break point" on the first line of code..... did not even break when I ran????

Here's code I used... though still not working.

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
If Me![AccessTotalsConst1].Value = 1 Then ' breakpoint this line, yet no break occured?
Me.Section(GroupFooter2).Visible = False
Else
Me.Section(GroupFooter2).Visible = True
End If
End Sub

This should not be THAT hard.... I'm obviously doing something wrong :-(

Thanks

Helen Feddema

Try giving the control bound to AccessTotalsConst1 the "txt" prefix, then reference the control in your code. sometimes there can be confusion when a control has the same name as its field, and in reports I have often found that you will only get the desired results by referencing a control rather than a field. In my second code sample, I first referenced MailingCity (the field), and that didn't work, but when I changed the reference to txtMailingCity.Value, it worked.

Also, you could use a Debug.Print statement to see the value of the control.

And finally, make sure the event procedure is listed in the properties sheet for that section footer. Sometimes this doesn't happen if you just create the code in the module.

ASKER CERTIFIED SOLUTION

Rick Norris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rick Norris

ASKER

Helen/Pat:

I really need a kick in the head.... everything is working correctly.... To show now "green" I happen to be with the access report writer.... I was just right clicking on the report and selecting "open". I thought that should "fire" the code.... I FINALLY noticed the print preview.... HUGE BUTTON.... How did I miss it???? After I actually did a REAL print preview I was able to see that the code was firing, AND most importantly working correctly.

Sorry for all the hassle after I had the solution!!

Both of you gave valuable insight, therefore I am splitting the points.

Thanks again!