Solved

MS access 2010

Posted on 2013-01-20
12
284 Views
Last Modified: 2013-02-23
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
Else
    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.
0
Comment
Question by:snhandle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 38799991
Hi,

Have you tried the Canshrink Property

http://msdn.microsoft.com/en-us/library/office/aa224008(v=office.11).aspx

Regards
0
 

Author Comment

by:snhandle
ID: 38799993
Yes I did but it is not working.
0
 
LVL 34

Expert Comment

by:Norie
ID: 38800002
Can't you exclude the negative records in the report's underlying query?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:snhandle
ID: 38800008
I know but here I am using running sum field in the report.
0
 
LVL 58
ID: 38800044
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.

 Jim.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 38800636
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
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38800949
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.
0
 

Author Comment

by:snhandle
ID: 38801957
It is not working, actually the "amount detial" is running sum amount so we do not have that column in the query.
0
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38803328
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

    Next
    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.
DetailAmount.accdb
0
 

Author Comment

by:snhandle
ID: 38803444
Is there any VBA statement where I can do something about it?
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38803499
@snhandle

"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?
0
 

Author Closing Comment

by:snhandle
ID: 38922546
it was a good hint.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question