Solved

Help fixing Access report code

Posted on 2013-02-01
20
464 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
[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
  • 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
How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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