Solved

Help fixing Access report code

Posted on 2013-02-01
20
448 Views
Last Modified: 2013-02-07
I have previously asked a question here on EE and did receive answer. I thought it was working.... but now it not...

I need an object to show up  on my access report ONLY if my RUNSUM box = 1, 2 or 3

Right now, the code is showing the line one lines 2,3,and 4...... can you help me fix it???

I attached several screenshots......

Here is the code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.CLSHOW.Visible = (Me.RUNSUM <= 3)
End Sub
RUNSUM.doc
0
Comment
Question by:joylene6
  • 5
  • 4
  • 4
  • +3
20 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 38845869
See if this works for you:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.RUNSUM <4 Then
        Me.CLSHOW.Visible = True
    Else
        Me.CLSHOW.Visible = False
    End If
End Sub 

Open in new window

Flyster
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38845872
Your code should be fine if RunSum is simply pulled from a field in your underlying recordsource.

However if runsum is a textbox or other control based on a calculation, you may need to use the calculation rather than the textbox or control name.  For example:

    Me.CLSHOW.Visible = ((x + y + z) <= 3)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38845941
What is RunSum? Is it a textbox with the ControlSource set to "=1" without the quotes and it's RunningSum property set to OverGroup?  If so, this should work fine.  Can you upload a database with just the report object?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 1

Author Comment

by:joylene6
ID: 38845983
Flyster - the code did not work.... same results as before.


mbizup- I dont understand what to put in the calulation...... what would I calulate? Are you talking about something in the code?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 0 total points
ID: 38845990
What is runsum?  A field in your table/query?  Or a control on your report?

If it is a textbox or other control, what is its controlsource property?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38846000
Why is your code not working? It should work.
Reproduce the issue with a sample database and upload.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 38846031
Sorry, forgot one part:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.RUNSUM.Value <4 Then
        Me.CLSHOW.Visible = True
    Else
        Me.CLSHOW.Visible = False
    End If
End Sub 

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38846051
For what it is worth,  the code you posted works perfectly for me with no modifications at all. That is with Runsum being a field in the table that your report is based on.

If this describes your setup then there is no reason that your code shouldn't work. That is why we are asking what runsum is.

A possible problem with using < 4 when you really want <= 3 is that it does not account for 3.5 etc if your  values are not all integers (floating point numbers between 3 and 4 will be included).
0
 
LVL 1

Author Comment

by:joylene6
ID: 38852598
RUNSUM is a  text box field I created in the report to add numbers to the report. I figured that I could show the CLSHOW box ( the lines) when the RUNSUM is <4.

I show the properties of the RUNSUM box in my attachment.


Flyster: I tried your updated code, but its still skipping that first line (#1) sometimes.

I will upload my database
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38853521
FWIW, ...
This basic sample works fine for me using:
1. A standard "Counting" running sum control with a controlsource of: =1
2. Flyster's basic code syntax (Points to Flyster)
3. The presumption that an "entry" is a record with a value in the Notes Field


Note that it is not inherently clear what the visibility of the line should be if there are *more* than 3 entries...?
...Line under the first 3 entries only?
...No line under any entries?
(toggle between 3 and 4 Notes entries and view the second report for an example of this)
If DCount("Notes", "YourTable1") <= 3 Then
    If Me.txtRunSum <= 3 Then
        Me.YourLine.Visible = True
    Else
        Me.YourLine.Visible = False
    End If
Else
    Me.YourLine.Visible = False
End If

JeffCoachman
Database15.mdb
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38853529
Your original code should work as it is.  Obviously there something else going on with your report.  Still waiting for you to upload a copy of your database.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 38853554
Ron, it may be in the way the OP derives the "RunSum" value.
As we all know, Reports that have a "Page x of y" control, will force Access to format the report in two passes.
So if the OP is using some sort of code Accumulator/Loop, ..to get the RunSum value (and not resetting it for each Pass), ..this may affect the RunSum Value and thus the visibility of the line...
Things like Groupings may also affect this as well..

But you and hnasr are correct in that a sample is always best...
;-)

Jeff
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 250 total points
ID: 38853570
Agreed Jeff.  Just pointing out that there is nothing wrong with the code as it is written.
  Me.CLSHOW.Visible = (Me.RUNSUM <= 3)
The problem lies somewhere else so there is no need for the OP to change this line.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38853585
Yeah,
I could not see anything wrong with the initial code either.

I just posted the sample to show that a basic If/Then/Else could work as well.
..To that end I am not even wanting any points, ...being that all of you previous experts stated this as well. (that the initial code should work)

Just curious to see what the issue was, so I too am a waiting the OP's sample...
;-)

Jeff
0
 
LVL 1

Author Comment

by:joylene6
ID: 38861548
Sorry I was pull away from the office. Please excuse the delay. Attached is the database
DAtabase-TEST.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861663
Did you evaluate the sample I posted...?

You are trying to make this happen using a "simulated" line, in a textbox....

Just use a "Line" (control) and this will work fine...

,,,as we all stated and is illustrated in my sample
http://filedb.experts-exchange.com/incoming/2013/02_w06/633031/Database15.mdb
(make sure you test the two reports with 4 records)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861679
I mean your simulated  line seems to be working there.

Just note that the default View for the report is "Report View"
VBA Report Format Code *Will not* run in Report View.

You must set the Default View of the report to: Print Preview

...Or open the Report explicitly in "Print Preview"
Docmd.OpenReport "YourReport", acViewPreview
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38862839
In your report you have your textboxes in the section named GroupHeader1 so your code should be in the format event for that section and not in the Detail section.

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
    Me.CLSHOW.Visible = (Me.RUNSUM <= 3)
End Sub

Also, as Jeff said, you don't need to use a textbox for your line.  Just use a Line control.
0
 
LVL 1

Author Comment

by:joylene6
ID: 38866787
you guys were right. The code was fine but I had the group header wrong in the code.
I tried to see the line controls, but that might be a lesson for me for another day. Thanks for your patience.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38866817
Curious... were you  trying to distribute the points evenly?  I'm asking because my post got assigned zero points.

I'm not arguing that, but I do believe you've uncovered a bug in EEs interface.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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