• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

MS access 2010

I have report in MS access where I  wrote script to show me only negative records and do not show me the positive records. I wrote the statement below.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.AmountDetail < 0 Then
    Me.Text9.Visible = True
    Me.Date.Visible = True
    Me.Text9.Visible = False
    Me.Date.Visible = False
End If
End Sub

It does work and only show negative records but it shows blank lines for positive records. I do want to shrink the report and do not want empty spaces showing on my report. I already choose the "Can grow" and Can shrink properties to yes but it is not working. Any idea to get rid of the blank lines to now show on the report.
1 Solution

Have you tried the Canshrink Property


snhandleAuthor Commented:
Yes I did but it is not working.
NorieVBA ExpertCommented:
Can't you exclude the negative records in the report's underlying query?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

snhandleAuthor Commented:
I know but here I am using running sum field in the report.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
1. Make sure your running that code in the OnFormat event

2. Make sure you have can shrink set for the controls and for the section.

3. Make sure there is nothing stopping the section from shrinking; other controls, lines, etc. all way across the page.

If that gets you no where, post a screen shot of the report design.

In your source query, eliminate these "empty" or null records.  For instance, if the DATE field is expected, simply add  WHERE DATE IS NOT NULL and then sum your records.  PS.  Blank rows are legitimate rows and "shrinking" won't work.  You must eliminate them from the source.

Scott C
Dale FyeCommented:
How about simply setting the reports Filter Property to:

[AmountDetail]< 0

You could do this using the WHERE argument of the OpenReport method, or by simply changing the query that is the RecordSource for your report.
snhandleAuthor Commented:
It is not working, actually the "amount detial" is running sum amount so we do not have that column in the query.
Dale FyeCommented:
You could loop through the controls in the details section, in the Format event, and change the height of all the controls in that section.  With the sections, "Can Shrink" set to Yes, if the controls are 0 height, the section should shrink as well.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim dblHeight As Double
    Dim ctrl As Control
    dblHeight = IIf(Me.txtDetailAmount < 0, 0, 0.2083) * 1400
    For Each ctrl In Me.Section(acDetail).Controls

        ctrl.Height = dblHeight

    Me.Section(acDetail).Height = dblHeight + (dblHeight * 0.125)
End Sub

Open in new window

In the attached sample, I use txtDetailAmount Mod 4 = 0, since I don't have any negative numbers, as you can see the rows for 7 and 8 are missing from the output.  But you will probably have to do this in PrintPreview mode, as the Detail Format event will not fire in Report View.
snhandleAuthor Commented:
Is there any VBA statement where I can do something about it?
Dale FyeCommented:

"Is there any VBA statement where I can do something about it? "

Who are you replying to, and which message.  If you want to have an effective thread, you need to indicate who you are replying to (and ideally which post, by #) when you reply like this.

Did you take a look at the sample database I provided?
snhandleAuthor Commented:
it was a good hint.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now