Update Field on Report Close Event (2K2)

Topic

New Reply

I am running the following code in the On Close event of a report. When the user selects “Yes”, I get the following error message:
“Run-time error ‘-2147352567 (80020009)’;
You can’t assign a value to this object.”

Private Sub Report_Close()
‘Completes clinic for reconciliation and _
printing of claims.

If Me.ClinicReconciled = -1 Then
DoCmd.Close
ElseIf Me.ClinicReconciled = 0 Then
If MsgBox(“Is this clinic reconciled?” & Chr(13) & Chr(10) & _
“Are claims ready to print?”, vbYesNo, “Print Claims”) = vbNo Then
DoCmd.Close
Else
Me.ClinicReconciled = -1 <—-This is the line that is highlighted when I select "Debug".
End If
End If

End Sub

How can I enable the control "ClinicReconciled" to be updated when the user selects the Yes option of the message box?

Reply | Quote

Replies

The data in a report are read-only, you cannot assign values to fields from the record source.
It’d be better to do this from the form from which you open the report (if you do so).
For which record(s) do you want to set the value of ClinicReconciled to True (-1)? All records displayed in the report, or …?

BTW, there is no need to use DoCmd.Close in the On Close event of a report, the report is being closed anyway, otherwise the event wouldn’t have occurred.

Reply | Quote

Since, I open this report from a switchboard how can I update the field’s value. I only want to update the value of ClinicReconciled of the current record.

Reply | Quote

Unlike a form, a report doesn’t have a “current record”. So what exactly do you mean?

Reply | Quote

the recordset of my report has multiple tables with the field ClinicReconciled displayed in the header of the unique ID of the clinic.

Reply | Quote

I’m not sure it’ll work, but it’s worth a try:

Private Sub Report_Close()
‘Completes clinic for reconciliation and printing of claims.
Dim strSQL As String
If Me.ClinicReconciled = False Then
If MsgBox(“Is this clinic reconciled?” & vbCrLf & _
“Are claims ready to print?”, vbYesNo, “Print Claims”) = vbYes Then
strSQL = “UPDATE [tblSomething] SET [ClinicReconciled] = True ” & _
“WHERE [ClinicID] = ” & Me.[ClinicID]
CurrentDb.Execute strSQL, dbFailOnError
End If
End If
End Sub

Replace tblSomething with the name of the table in which you want to set the ClinicReconciled field to True, and replace ClinicID with the name of the unique ID field (I have assumed that it is a number field).

Reply | Quote

Your solution worked like a charm. As always your help is invaluable…many thanks

Reply | Quote