Help fixing Access report code

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
LVL 1
joylene6Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mbizupConnect With a Mentor Commented:
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
 
FlysterCommented:
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
 
mbizupCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
IrogSintaCommented:
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
 
joylene6Author Commented:
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
 
hnasrCommented:
Why is your code not working? It should work.
Reproduce the issue with a sample database and upload.
0
 
FlysterCommented:
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
 
mbizupCommented:
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
 
joylene6Author Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
IrogSintaCommented:
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
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
IrogSintaConnect With a Mentor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
joylene6Author Commented:
Sorry I was pull away from the office. Please excuse the delay. Attached is the database
DAtabase-TEST.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
IrogSintaCommented:
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
 
joylene6Author Commented:
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
 
mbizupCommented:
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
All Courses

From novice to tech pro — start learning today.