Report Help

[nayfeh]

Hi,

I have a report that gets data from a sql stored procedure.
There are two fields in the underlying table that I need updated when the report gets printed. One is "Printed" (int) the other "Duplicate" (int).

So, when this report gets printed for the first time, the field PRINTED changes from 0 to 1. The next time it's printed, the field duplicate changes from 0 to 1.
I need this because I need to show on the report "DUPLICATE" if this report has already been printed. Not sure how to do this.

Thanks in advance!

TN

 

[PaulBricker]

I don't know of any easy way to do this. Especially coming from a stored procedure (which I only have passing knowledge of). The problem is that
1. The Print event fires when you move to the last page of the report, whether it is actually printed or not.
2. The PrintCount property always reads 1 from what I've seen.
3. There is no way to determine whether a Report has been printed before (at least non that I'm aware of) so it's harder to figure how to set your Duplicates value to 1.
Now having said that, you could use an Insert Into TableName (ReportName,Printed) Values(Me.Name,1)
and then create a total query that grouped on Report Name and sumed the Printed field and then any report that had a value of greater than 1 in the Printed field would trigger your duplicate code.
Now, having said that, what are you trying to do? Do you want the duplicate label/textbox to be visible if the Duplicate flag is triggered? If that's it, then none of what I suggested may be of any value to you. There are no open args with Reports the way there are with forms. It's possible you could get there with a global function that got called from the Open Event, that looked at the value in the query, but some additional info from you will help smooth this out.

Paul

 

[Neng]

How is the report generated? If you use a command button to start the process, you could code it to first look up the value of your Printed field. If it is null or 0, your code could set the value to 1 and continue printing. If it is 1 (which would mean you've already printed it) you would leave the value as 1 and continue printing. Use the same sort of lookup function on your report to determine how the visible property of your Duplicate text box should be set. If dlookup returns 0 or null, it would be visible.false. If the value was 1, the property would be true and the text box would be printed
I haven't tried this, but it should work.

 

[nayfeh]

Thanks for the reply.

Yes, the report is generated through a command button. Is it possible to provide me the code to do this? Not sure how I can set the field "Printed" to 1 through the form.

Thanks,
TN

 

[Neng]

I am assuming the data for the report is coming from a single record and the form is displaying data from that record. If so, put the Printed field on the form (it can be invisible if you want). Then, put the code to assign the value behind the print button's On Click event.

Private Sub CmdPrint_Click()
'set up a variable here for the value of the Printed field
'set the value of the variable with a dlookup function
'then
If variable = False Then
Me.Printed = True
Else: Me.Printed = True
End If
'then write the code to print your report
End Sub

On the report, using the Detail section's On Print event, you'd need code establishing the same variable set by a lookup function. The code that would print "Duplicate" or not would also use an If/Then statement.

If variable = False Then
[labelDuplicate].visible = True
Else: [labelDuplicate].visible = False
End If

The word Duplicate would just be a label on your report saying "Duplicate" and positioned where you want.

I hope this works for you. I haven't tested it.

 

[nayfeh]

Hi Neng,
Again, thanks for the reply. The form is actually just an unbound form with 2 text fields for StartDate and EndDate and the Print command button. No data is displayed through the form, it's just used to generate the sql procedure and pass that information to the report.

Is it still possible to do it in the method you stated above? ie. by adding a bound control for the PRINTED field.

TN

 

[Neng]

Yes, it should work if the form is bound to the table. I forgot to mention that my approach uses the Printed field as Yes/No. If you want to track how many Duplicates are printed, you could go with an Integer field and set up a counting mechanism. Also, I am unsure when the data would be saved to the field using VBA. If it isn't saved until after the procedure is run, your report wouldn't know whether it had been printed or not based on the data in the table. It might be possible to have the dlookup function in the report look up the value on the form instead of the table, but I would cross that bridge when I came to it.