Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concantenate Fields in Report OpenArgs (2003)

    A couple of weeks ago, Hans recommended the following code to pass selections from a form to change the source for a report text box control. Works great, except now they want to combine results. For example, if the user selects option 4, they want two kinds of comments to print.

    I tried:
    Case 4
    strField = "=[Comments] & [CommentsConfid]"

    That doesn't work. Is it possible to do a concatenation in OpenArgs??

    Thanks for any suggestions you may have.
    Private Sub cmdReport_Click()
    Dim strField As String
    Select Case grpOption
    Case 1
    strField = "memManagementComments"
    Case 2
    strField = "memConfidentialComments"
    ...
    End Select
    DoCmd.OpenReport ReportName:="rptAccounting", View:=acViewPreview, OpenArgs:=strField
    End Sub

    In the On Open event of the reoprt, set the Control Source of the text box that will display the comments:

    Private Sub Report_Open()
    If Not IsNull(Me.OpenArgs) Then
    Me.txtComment.ControlSource = Me.OpenArgs
    End If
    End Sub

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

    Re: Concantenate Fields in Report OpenArgs (2003)

    For some reason, I get an error message if I try to pass an expression as OpenArgs. As a workaround, you can use a public variable:
    - Create a standard module by clicking New in the Modules section of the database window, or by selecting Insert | Module in the Visual Basic Editor.
    - Enter the following line in the new module:

    Public gstrArgs As String

    - Change the On Click code for the command button as follows:

    ...

    ' Set public variable
    strArgs = strField
    ' Open report (no OpenArgs)
    DoCmd.OpenReport ReportName:="rptAccounting", View:=acViewPreview
    ' Clear public variable
    strArgs = ""
    End Sub

    - Change the On Open code for the report as follows:

    Private Sub Report_Open(Cancel As Integer)
    If Not strArgs = "" Then
    Me.txtComment.ControlSource = strArgs
    End If
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concantenate Fields in Report OpenArgs (2003)

    Perfect! You're helping me learn almost enough to be dangerous

Posting Permissions

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