Solved

Help fixing Access report code

Posted on 2013-02-01
20
419 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:joylene6
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now