Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Update Field on Report Close Event (2K2)

    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?
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field on Report Close Event (2K2)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Field on Report Close Event (2K2)

    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.
    Easy Access

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field on Report Close Event (2K2)

    Unlike a form, a report doesn't have a "current record". So what exactly do you mean?

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Field on Report Close Event (2K2)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field on Report Close Event (2K2)

    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).

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Field on Report Close Event (2K2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •