Detail Print...intermitent problem....I'm Baffled

[BNoble]

I searched the forums before posting this, and found some posts, and answers that came close but did not quite do it.

With that in mind, here's the problem I have.

I have a report that has a text field [Rank] in it with a value =1. It is set up as a running sum, ultimately I use it to filter for top 50 clients.

I have code on the print event that filters it all the way to print preview. Upon going to print, or page setup, it clears the page of all data.

I have moved this code to on_format with no luck....as a matter of fact at on_format it clears it out completely.

Here's the code.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Rank >= 50 Then
Detail.Visible = False
End If

Anybody have any idea what's going on? At one point this code worked just fine. I was brought in to fix it. I also cannot fathom what might have changed that would make it behave differently.

Thanks,

 

[BNoble]

Still working on it

As I continue to search etc...

I figured I would add some more information.

It's not really clearing the data so to speak just setting all the detail to not visible. This is as opposed to when rendered in print preview where the report is making only those detail records >= 50 not visible.

The grand totals etc....are intact.

 

[BNoble]

Update

Ok,

Here's where I'm at on this.

I got with one of our developers, and described the problem.

He told me the logic and code sounds good.

So that leads me to believe that this is an Access Tweaky Weird thingee.....

With that in mind I performed a compact/ Repair ...again....

The DB is wieghing in at a healthy 1.2 gig....

I remember back in the day that 1 gig...and access would become unstable....
however, this one seems to be ok...atleast appearance wise....

Would I be served any by going ahead and splitting the tables out to seperate mdb's and linking thereby getting them all down to something considerably less than 1 gig?

Could that have anything to do with the above problem?

 

[BNoble]

Hmmm...no help yet...well here's what I got

I found a fix around this...and it's gonna be a major pain, unless I can find something else.

I stuck a label on the report in the detail field that keeps track of what has been passed and what hasn't that way when I go to print from print preview it doesn't pass again and screw everything up.

This still does not explain why it stopped working in the first place....which I am not happy about at all.

Here's the fix:


(I defaulted the Mark caption = "No" that way I don't get null errors)

If Me.Mark.Caption = "Yes" Then GoTo Endit

Me.Mark.Caption = "Yes"

If Null2Zero(Rank) >= 30 Then

Me.Detail.Visible = False

End If

Endit:


The next step for me is to paramatize this with an input box. hopefully I can plug and play this into the other 20 or so reports with little incident.

Again, if anyone has a better way or can explain why the first bit is not working, I would really appreciate it.

 

[BNoble]

Come on folks ..... anyone ...

Ok well, I'm getting ready to go through all these reports and slip the fix that I have posted earlier in this thread.

I know that there has to be a better way....I just know it.

Is there anyone out there that is doing what I am doing with a better approach?

 

[mdemarte]

I have a report that has a text field [Rank] in it with a value =1. It is set up as a running sum, ultimately I use it to filter for top 50 clients.

Um, did you really mean text field? I don't think you can sum a text field, so a character '1' may not be <= a numeric 50

If that does not work, try a new post in the VBA section, just include a brief explanation that you may have started this post in the wrong section.

 

[BNoble]

Correction....It's a Text Box

Ooops.

Yup it's not a text field, it's a text box... So I have a text box on the report that is populated with a 1....then it is set as a running sum.

Oh and I did find a solution....I'm still not happy with it but here it is.

Here's the code:

Private Sub Report_Open(Cancel As Integer)

Message = "Enter Rank Parameter" ' Set prompt.
Title = "Ranking" ' Set title.
Default = "X" ' Set default.

' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

End Sub

Then with MyValue, it will test the detail line. I changed the event from On_print to On_format. It slows things downconsiderably, but it works.

The logic of this code will not work backwards...in other words if I have the detail visible, and then change the if statement to make those lines >= it will not work right.

I think the way I have it set up below is bypassing a bug, because if I go the other direction of setting visible to false at the if statement it doesn't work. (We're all pretty convinced this is some sort of access bug introduced during a service pack update, because this code the reverse did work just fine until recently)

Here's the code: (MyValue is an input box value....I was lazy and never renamed it from the example I ripped out of MSDN, Rank is the Text box with the running sum)


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.Detail.Visible = False ' Sets all detail invisible

If Rank <= CInt(MyValue) Then ' inspects the rank against the number in the input box

Me.Detail.Visible = True 'sets those that pass to visible

End If

End Sub

 

[WayneRyan]

BNoble,

Wow! Boy have you suffered.

I got lost reading this thread (an example could have really helped) and
had a thought. Why make the "non top 50" invisible.

Why not just use a "Select Top 50" type of query and make your life a
whole lot easier?

Wayne

 

[BNoble]

I came onto this report well after it was built

Unfortunately, I wasn't around when this database and reporting structure was orignally designed.

So I kinda had to work with what was there.

There are approximately 35 reports with this same structure...so I had to come up with something that would easily translate to all of them.


Turns out, it's worked ok...I've been able to copy and paste this code in with relatively little incident.