How can I access reports recordset 1

[Knutjo]

Hi !

I'm working on/creating a invoice report.
When the invoice is printed I want to update the invoice record with info that tells that the invoice is printed and shall not be edited anymore.

What is the correct syntax to access the records in the report's recordset ?

... like "Reports![Invoice].Recordset("Printed&quot = "YES"


Knutjo

 

[Darrylles]

Hi,

You can't update a table from a report, a report simply displays data - it doesn't hold any.

Set the 'Printed' flag from the form that initiated the report in the first place.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[PaulBricker]

Well, yes and no. You can update the underlying record source for the Report using an update query but it will do it when the report is opened for printing or just for viewing. This code in the On Format Event for the Detail Section updates my WorkOrder table Printed Field. WONumber is my primary key. Each time the Report formats the detail section, it looks at the value in Me.WONumber, and updates the Printed Field for that WONumber in the Table.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "UPDATE [WorkOrders] SET [WorkOrders].Printed = Yes WHERE ((([WorkOrders].[WONumber])= " & Me.WONumber & &quot);"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

You'll want to be sure to wrap the DoCmd.RunSQL in
DoCmd.SetWarnings False
Docmd.SetWarnings True

or you will be clicking on OK for every record updated. If it's more that a couple, it's a pain. The SetWarnings method prevents you from having to do that.

Paul

 

[randysmid]

Hi Paul,
Why not use the OnClose event for the report? It sounds like Knutjo only wants to update the invoice record one time, since it appears that is the only item he is printing.
HTH,
Randy Smith
California Teachers Association

 

[PaulBricker]

Randy, the logic is that as the Report formats each record, the WONumber is available to use in the SQL string to return the correct record and then move on to the next record. If Knutjo only has the one record to deal with, then I suppose you could use the Close event. It's kind of six of one/half dozen of another at that point. If you don't wrap the RunSQL in the SetWarnings method then you will have to click a message box no matter where you put the code. I've never used the close event for anything like this so I would say you would have to experiment with it to see if it works.

Paul

 

[randysmid]

Hi,
Perhaps Knutjo can clarify this for us. It seems to me that he is only printing one invoice, and that is the only thing he wants updated in the database (printed=YES).

Now, if he were printing a whole series of invoices, then the detail section (or whatever is most appropriate) code example you provided would work perfectly. HTH,
Randy Smith
California Teachers Association

 

[PaulBricker]

I ran a quick test and the code works in the Close event with a single record but only updates one record if there are multiple records to run. I guess my preference would still be the Format event because it works no matter how many records you are updating. It covers all the bases without adding any overhead to the process.


Paul

 

[Knutjo]

Hello everyone !

Thanks for your help and hints.

I solved my problem with a modal form.
When I start the invoice report I use a modal form. In this form I can control what to do with some global variables. Actually it was pretty simple... In this form with a recordset all printed invoices was updated as I wanted.

Thanks folks !