Is there a way to open second report if field is null or... (1 Viewer)

[dtaylor89]

Is there a way if a record is missing a certain field(attachment) then open short report else open long report. Or is there a way that you can have one report with two pages and say if field(attachment) is null only open display page 1 else display page 2.

I have some code like this
If (Me.Print) = 0 Then 'Checkbox
Call MsgBox("Please make sure you select a metric to print!")
End If
If (Me.Metric_Display) = 0 Then 'attachment
DoCmd.OpenReport "rptMetrics", acViewPreview, "", "", acNormal

Else

DoCmd.OpenReport "rptMetricsLong", acViewPreview, "", "", acNormal
End If

My Error says: Object doesn't support this property or method.

 

[James Dudden]

My Error says: Object doesn't support this property or method.

What line causes the error?

 

[AccessMSSQL]

I think the error might be in the actual report. Can you try to open the reports from the navigation pane and see if you get this error? The code above seems okay to me.

 

[dtaylor89]

Yes I can view the report any other way. Only when i use this code I cant see the report. If I take out the if else statement and just open the report it works just fibe

 

[AccessMSSQL]

This line is the problem:

If (Me.Metric_Display) = 0 Then 'attachment

metric_display is an attachment type field so change it to this:

if (me.Metric_Display.AttachmentCount) = 0 then

 

[dtaylor89]

Ok that fixed my error however how do i account for someone selection a record with an attachment and one without an attachment at the same time. When I do this I only get the short report and i dont see the long report.

 

[AccessMSSQL]

How are you having users select multiple records to print? Are they printing from a datasheet and just highlighting the rows?

 

[dtaylor89]

I have a split form where they can check which records they want to print.

 

[AccessMSSQL]

So there is a checkbox that gets checked for each record that is being printed?

In that case you have to loop through each record selected and evaluate each one separately. Also, between each record you will have to close the report and re-open it for every record.

Something like this:

Dim rs as dao.recordset
set rs = me.Recordset  'OR if it's a subform set rs = Forms!MainForm!SubForm.Form.Recordset
Do While not rs.EOF
    if rs("Checked") = True then
         'do your report logic
    end if
    rs.movenext
Loop

make sure you change rs("Checked") to the actual name of the checkbox

 

[dtaylor89]

so something like this

Dim rs As dao.Recordset
Set rs = Me.Recordset
Do While Not rs.EOF
If rs("Print") = True Then
If (Me.Metric_Display.AttachmentCount) = 0 Then
DoCmd.OpenReport "rptMetrics", acViewPreview, "", "", acNormal
Else
DoCmd.OpenReport "rptMetricsLong", acViewPreview, "", "", acNormal
End If
End If
rs.MoveNext
Loop

 

[AccessMSSQL]

That's not going to work unless you are printing directly to the printer. If user is going to preview the report you won't be able to loop through and preview the next report becuase the user will have the report open in preview mode.

Also, if you do the loop and print directly to the printer you might have to put a wait time in between loops to give the report enough time to finish printing before you send the next one to the printer.

 

[dtaylor89]

ok how can i make this work just to loop through to directly print.
I will only have the user preview single reports

 

[AccessMSSQL]

Change acViewPreview to acViewNormal in your Docmd.OpenReport argument

 

[dtaylor89]

Hey do you see something that I'm doing wrong in my code because when i hit print it immediately prints the reports selected but the one with the attachment still only printed the first page.

Dim rs As dao.Recordset
Set rs = Me.Recordset
Do While Not rs.EOF
If rs("Print") = True Then
If (Me.Metric_Display.AttachmentCount) = 0 Then
DoCmd.OpenReport "rptMetrics", acViewNormal, "", "", acNormal
Else
DoCmd.OpenReport "rptMetricsLong", acViewNormal, "", "", acNormal
End If
End If
rs.MoveNext
Loop

 

[dtaylor89]

And I also moved the movenext between the two End If

 

[AccessMSSQL]

Not sure about that. How many pages is rptMetricsLong? Are these reports bound to a particular record or are they just blank reports? Not sure. How many records did you have selected? Have you tried putting a breakpoint in your code and stepping through it? To place a breakpoint - in the VBA window click in the LEFT gray bar next to the line of code you want to step through - you should see a big red circle. Then, save the window go back to your form and click your print button. The code should stop at the breakpoint and you can use F8 to step through each line.