[AccessD] Change Reports Caption From Code

Stuart,

    Sub-routine named P_QueryFriendlyCaption() given below, should be able to get the desired effect. Essentially, it involves making a temporary copy of the query, under the specified descriptive name. 

    Whenever, a fresh query needs to be displayed, previously existing temp query (if any) gets deleted, so that at any given stage, there is no more than one temp query. Finally when the form is closed, any temp query still around, gets deleted, so that the status of queries collection remains undisturbed.

    Complete code for form's module is given below. Query to be displayed is selected via combo box named CboQuery. Desired friendly caption is entered in text box named TxtCaption. On clicking the command button (CmdShow), the query gets displayed with appropriate caption (If TxtCaption is left blank, the query gets displayed under its original name).

Best wishes,
A.D.Tejpal
--------------

Form's Code Module
===================================
' General Declarations section
Private TempQuery As String   ' Global variable
------------------------------------------------------------

Private Sub P_QueryFriendlyCaption(ByVal RealName _
                        As String, ByVal DisplayName As String)
    On Error Resume Next
    
    ' Delete TempQuery if any and also query named
    ' DisplayName if any
    If Len(TempQuery) > 0 And _
                        TempQuery <> RealName Then
        DoCmd.DeleteObject acQuery, TempQuery
        ' Reset the value for global variable
        ' (Useful in clearing temp query in form's Close event)
        TempQuery = ""
    End If
    If DisplayName <> RealName Then
        DoCmd.DeleteObject acQuery, DisplayName
    End If
    
    ' Copy query named RealName as a temp query
    ' named DisplayName
    If DisplayName <> RealName Then
        ' Set global variable - for deletion of temp query
        ' in next round
        TempQuery = DisplayName
        DoCmd.CopyObject , DisplayName, _
                                            acQuery, RealName
    End If
    
    ' Display the newly copied query
    DoCmd.OpenQuery DisplayName
    DoCmd.Maximize
    
    On Error GoTo 0
End Sub

Private Sub CmdShow_Click()
    If Len(CboQuery) > 0 Then
        P_QueryFriendlyCaption CboQuery, _
                            Nz(TxtCaption, CboQuery)
    End If
End Sub

Private Sub Form_Activate()
    DoCmd.Restore
End Sub

Private Sub Form_Close()
    ' Delete temp query if any
    If Len(TempQuery) > 0 Then
        DoCmd.DeleteObject acQuery, TempQuery
    End If
End Sub
===================================

  ----- Original Message ----- 
  From: Stuart McLachlan 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, July 13, 2005 04:36
  Subject: Re: [AccessD] Change Reports Caption From Code


  On 12 Jul 2005 at 17:52, Josh McFarlane wrote:

  > 
  > DoCmd.OpenReport ReportTest, acPreview,,,,"Report Caption"
  > 
  > Then in the on open handler:
  > Caption = OpenArgs
  > 
  > or something similar to fit your needs.
  > -- 

  In a similar vein, has any looked into changing the Caption in a query datasheet or print preview.   I often shortcut in simple applications and display a query to the user rather than going to the effort of producing a report based on the query. It would be nice to customise the header on a printout of the query.

  -- 
  Stuart