Modify remote report using code 1

[evalesthy]

Using the following code I can open a report in a 2nd database.

thread702-583654

By changing the docmd.open to acDesignView I can now open th remote report in design view. Is there a way to add code that would alter a label caption in that report and then save the change?

 

[RickSpr]

Yes, there is. After the DoCmd.OpenReport, add code like the following:

        .Reports(strReport).Controls(&quot;<label name>&quot;).Caption = &quot;New caption:&quot;

However, you might not want to use that code exactly as it is. It makes the remote database visible to the user, and waits for the user to close the database. It sounds to me as if you might want to do this without the user watching what's going on. Do you understand how that code works? If not, why don't you describe what you want from a step further back (such as "I want to change the label on the report and then print it, without involving the user and without saving the change in the remote database", or "I want to change the label and save it without previewing or printing the report&quot.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[evalesthy]

Thanks, yes, your assumption is correct. I would like to implement this change (and maybe others) without the user seeing the database open/close. It would be OK if all users needed to be out.

In short, I want to implement a change behind the scenes. User will see it when they next use that section of database (report in this instance). What is the best way to go about that.

 

[RickSpr]

Replace all the code with the following function. I enhanced it to allow you to change any property of any control.

Function ChangeRemoteReport(DbPath As String, _
                            ReportName As String, _
                            ControlName As String, _
                            PropertyName As String, _
                            NewValue As Variant) _
                            As Long
' Purpose: Changes any property of any control on any report in any external database
'          (assuming the property is read/write).
' Returns: 0 if successful, else runtime error number
Dim appAccess As Access.Application
Dim rpt As Report

    On Error GoTo ErrorHandler

    'If Len(Dir(DbPath)) > 0 Then
    Set appAccess = New Access.Application
    With appAccess
        .Echo False
        .OpenCurrentDatabase DbPath
        .DoCmd.OpenReport ReportName, acDesign
        Set rpt = .Reports(ReportName)
        rpt.Controls(ControlName).Properties(PropertyName) = NewValue
        Set rpt = Nothing
        .DoCmd.Save
        .DoCmd.Close acReport, ReportName
    End With
    ChangeRemoteReport = 0
    'End If
ErrorExit:
    On Error Resume Next
    appAccess.Quit
    Set appAccess = Nothing
    Exit Function
ErrorHandler:
    ChangeRemoteReport = Err.Number
    Select Case Err.Number
        Case 7866: ' Database not found or is already exclusively opened
            MsgBox "The database you specified," & vbCrLf _
                & "'" & DbPath & "'," & vbCrLf _
                & "was not found or is currently open in exclusive mode.", _
                vbExclamation, "Could not open database"
        Case 2103: ' Report doesn't exist
            MsgBox "The report '" & ReportName & "' doesn't exist in the database " _
                & vbCrLf & "'" & DbPath & "'", _
                vbExclamation, "Report not found"
        Case 2465: ' Can't find the control
            MsgBox "The control '" & ControlName & "' doesn't exist in the report '" _
                & ReportName & "'", _
                vbExclamation, "Control not found"
        Case 2455: ' Invalid reference to property
            MsgBox "The control '" & ControlName & "' doesn't have a '" _
                & PropertyName & "' property", _
                vbExclamation, "Property not found"
        Case 2113: ' Invalid property value
            MsgBox "The value '" & NewValue & "' is not valid for the '" _
                & PropertyName & "' property", _
                vbExclamation, "Invalid property setting"
        Case Else:
            MsgBox "Error " & Err.Number & ":" & vbCrLf & Err.Description, _
                    vbCritical + vbOKOnly, "Runtime error"
    End Select
    Resume ErrorExit
End Function

Note that the taskbar will briefly show an Access task running. I don't know how to prevent that, but it's only briefly there.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[evalesthy]

Excellent. It worked perfectly. Thanks for your help.