Empty reports (1 Viewer)

[Ole]

Hi

I've made this code for printing out 4 different reports. The reports are as following: Invoice (Faktura), Specifikations1 (FakturaSpec), problem is that every time I open these repports, I only need "Faktura" and one of the 3 specifications. The remaning 2 are empty. But they will be generatet anyway.
Here is the code:

Private Sub Fakturanr_DblClick(Cancel As Integer)
Dim stLinkCriteria As String
Dim File1 As String
Dim File2 As String
Dim File3 As String
Dim File4 As String
File1 = "Faktura"
File2 = "FakturaSpec"
File3 = "FakturaSpecPakker"
File4 = "FakturaSpecTeknik"
stLinkCriteria = "Fakturanr=" & Me!Fakturanr
DoCmd.OpenReport File1, acViewPreview, , stLinkCriteria, , acNormal
On Error GoTo Faktura_Err
stLinkCriteria = "Fakturanr=" & Me!Fakturanr
DoCmd.OpenReport File2, acViewPreview, , stLinkCriteria, , acNormal
On Error GoTo Faktura_Err
stLinkCriteria = "Fakturanr=" & Me!Fakturanr
DoCmd.OpenReport File3, acViewPreview, , stLinkCriteria, , acNormal

stLinkCriteria = "Fakturanr=" & Me!Fakturanr
DoCmd.OpenReport File4, acViewPreview, , stLinkCriteria, , acNormal
On Error GoTo Faktura_Err
Faktura_Exit:
Exit Sub
Faktura_Err:
MsgBox Error$
Resume Faktura_Exit

End Sub

Is there anyway I can avoid having these 2 empty reports?

Best regards

Ole

 

[vbaInet]

So you want to avoid opening the report if it has no data? You can use the On No Data event of the report.

 

[Ole]

Yes thats right. But I can't find out how to implement the code in the exicisting code.

 

[vbaInet]

So if you look in the event, you will see a Cancel parameter, what you need to do is set Cancel to True, i.e. Cancel = True. Then in the event where you have your OpenReport code, trap the error number 2501, because it will occur when you interrupt the form's opening sequence.

Let us know how it goes.

 

[Ole]

I think that I maybe wasn't clear enough when I explaned my problem.
It's not 1 report I need to print, but I need to print all the reports with data.
It can be 2,3 or 4 reports. But always the report called "File1"
And I wan't to do it in one rutine.

I have tried to use On No_Data, but if File2 dosn't contain data, the hole rutine stops, and File3 and File4 is canceled even though there is data to be "reported".

I'm kinda stocked here.

 

[vbaInet]

Yes I know it's not one report you want to print. You will attempt to open all reports and if any of them is empty you will close it (i.e. setting Cancel = True) and continue to the next report by using the On Error Resume Next error handler. So based on what I've just said, see how you get on and show me what you've done and I will tell you how to move forward.

 

[Ole]

This is where I stand now (I only test it with File2):

Private Sub Fakturanr_DblClick(Cancel As Integer)

Dim stLinkCriteria As String
Dim File1 As String
Dim File2 As String
Dim File3 As String
Dim File4 As String

File1 = "Faktura"
File2 = "FakturaSpec"
File3 = "FakturaSpecPakker"
File4 = "FakturaSpecTeknik"

stLinkCriteria = "Fakturanr=" & Me!Fakturanr
DoCmd.OpenReport File1, acViewPreview, , stLinkCriteria, , acNormal

On Error GoTo Faktura_Err

stLinkCriteria = "Fakturanr=" & Me!Fakturanr
DoCmd.OpenReport File2, acViewPreview, , stLinkCriteria, , acNormal

On NoData GoTo No_Data
On Error GoTo Faktura_Err

stLinkCriteria = "Fakturanr=" & Me!Fakturanr
DoCmd.OpenReport File3, acViewPreview, , stLinkCriteria, , acNormal

On Error GoTo Faktura_Err

stLinkCriteria = "Fakturanr=" & Me!Fakturanr
DoCmd.OpenReport File4, acViewPreview, , stLinkCriteria, , acNormal

On Error GoTo Faktura_Err

Exit Sub

Faktura_Exit:
Exit Sub

Faktura_Err:
MsgBox Error$
Resume Faktura_Exit

No_Data:
Clear = True
Resume Next

End Sub

Can it be that the report isn't totally empty? I have some fields like "Turpris" witch always contain data. It's a field witch contain price information but it dosn't show.

All the reports are connected with the field "Turnr". This is the main field. If this field is empty the report is (seems) "empty". Turnr is a Autonumber field in the main Table. All the other tables are related to this filed.

I don't know if this is making any sence...

 

[vbaInet]

In my last post you will notice that I specifically bolded "On Error Resume Next" but you didn't use it in your code or ask me about it. Here's your code amended:

Private Sub Fakturanr_DblClick(Cancel As Integer)
    
    Dim stLinkCriteria As String
    Const Faktura As String = "Faktura"
    Dim File1 As String
    Dim File2 As String
    Dim File3 As String
    Dim File4 As String
    
    File1 = Faktura
    File2 = Faktura & "Spec"
    File3 = Faktura & "SpecPakker"
    File4 = Faktura & "SpecTeknik"
    
    On Error Resume Next
    
    stLinkCriteria = "Fakturanr = " & Me.Fakturanr
    DoCmd.OpenReport File1, acViewPreview, , stLinkCriteria
    DoCmd.OpenReport File2, acViewPreview, , stLinkCriteria
    DoCmd.OpenReport File3, acViewPreview, , stLinkCriteria
    DoCmd.OpenReport File4, acViewPreview, , stLinkCriteria
    
    On Error GoTo Faktura_Err
    
    Exit Sub
    
Faktura_Exit:
    Exit Sub
    
Faktura_Err:
    MsgBox Error$
    Resume Faktura_Exit

End Sub

What I would like to see now is what you have written in the On No Data event based on my previous explanations. This event is an event in your report, not a form. Remember an event is in the Property Shee so your code line of "On No Data Go To..." doesn't mean anything.

Have a look at this link:

http://www.fmsinc.com/free/newtips/access/accesstip45.asp