Using OpenArgs in a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code on a form:

Dim strField As String
'Evaluate which option button was selected
'Set the comment box recordsource as appropriate
Select Case optCriteria
Case 1
strField = "Comments" 'name of field in underlying query
Case 2
strField = "CommentsShare"
Case 3
strField = "CommentsMgmt"
Case 4
strField = "CommentsConfid"
End Select
Me.Visible = False
DoCmd.OpenReport "rptMemoReport", acViewPreview, OpenArgs:=strField

In the Open event of the report, I have:

If Not IsNull(Me.OpenArgs) Then
Me.txtComments.ControlSource = Me.OpenArgs
End If

Works great as is. What I would like to add is the option to combine the
various comments on the report. For example, allow the user to have the
"comment" field on the report contain both general and confidential comments.
On the form, I've tried strField = "Comments" & "CommentsConfid" which
didn't work.

These fields are in a "master query" I use as support for several reports.
For this report, all I want to change is the comments that print.

Is there a way to concantenate the fields on the report?

Thanks!
 
Something like this in the form code should do it ...

strField = "=[Comments] & [CommentsConfid]"
 
Brendan, I tried that and received an error:

An expression you entered is the wrong data type for one of the arguments.

Any ideas?

Thanks.

Brendan Reynolds said:
Something like this in the form code should do it ...

strField = "=[Comments] & [CommentsConfid]"

--
Brendan Reynolds (MVP)

KittyS said:
I have the following code on a form:

Dim strField As String
'Evaluate which option button was selected
'Set the comment box recordsource as appropriate
Select Case optCriteria
Case 1
strField = "Comments" 'name of field in underlying query
Case 2
strField = "CommentsShare"
Case 3
strField = "CommentsMgmt"
Case 4
strField = "CommentsConfid"
End Select
Me.Visible = False
DoCmd.OpenReport "rptMemoReport", acViewPreview, OpenArgs:=strField

In the Open event of the report, I have:

If Not IsNull(Me.OpenArgs) Then
Me.txtComments.ControlSource = Me.OpenArgs
End If

Works great as is. What I would like to add is the option to combine the
various comments on the report. For example, allow the user to have the
"comment" field on the report contain both general and confidential
comments.
On the form, I've tried strField = "Comments" & "CommentsConfid" which
didn't work.

These fields are in a "master query" I use as support for several reports.
For this report, all I want to change is the comments that print.

Is there a way to concantenate the fields on the report?

Thanks!
Click to expand...
Click to expand...
 
Could you post the modified code?

--
Brendan Reynolds (MVP)


KittyS said:
Brendan, I tried that and received an error:

An expression you entered is the wrong data type for one of the arguments.

Any ideas?

Thanks.

Brendan Reynolds said:
Something like this in the form code should do it ...

strField = "=[Comments] & [CommentsConfid]"

--
Brendan Reynolds (MVP)

KittyS said:
I have the following code on a form:

Dim strField As String
'Evaluate which option button was selected
'Set the comment box recordsource as appropriate
Select Case optCriteria
Case 1
strField = "Comments" 'name of field in underlying query
Case 2
strField = "CommentsShare"
Case 3
strField = "CommentsMgmt"
Case 4
strField = "CommentsConfid"
End Select
Me.Visible = False
DoCmd.OpenReport "rptMemoReport", acViewPreview,
OpenArgs:=strField

In the Open event of the report, I have:

If Not IsNull(Me.OpenArgs) Then
Me.txtComments.ControlSource = Me.OpenArgs
End If

Works great as is. What I would like to add is the option to combine
the
various comments on the report. For example, allow the user to have
the
"comment" field on the report contain both general and confidential
comments.
On the form, I've tried strField = "Comments" & "CommentsConfid" which
didn't work.

These fields are in a "master query" I use as support for several
reports.
For this report, all I want to change is the comments that print.

Is there a way to concantenate the fields on the report?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Sure, here it is:

Select Case optCriteria
Case 1
strField = "Comments"
Case 2
strField = "CommentsShare"
Case 3
strField = "CommentsMgmt"
Case 4
strField = "=[Comments] & [CommentsConfid]"
End Select
Me.Visible = False
DoCmd.OpenReport "rptMemoReport", acViewPreview, OpenArgs:=strField

Thanks!

Kitty


Brendan Reynolds said:
Could you post the modified code?

--
Brendan Reynolds (MVP)


KittyS said:
Brendan, I tried that and received an error:

An expression you entered is the wrong data type for one of the arguments.

Any ideas?

Thanks.

Brendan Reynolds said:
Something like this in the form code should do it ...

strField = "=[Comments] & [CommentsConfid]"

--
Brendan Reynolds (MVP)

I have the following code on a form:

Dim strField As String
'Evaluate which option button was selected
'Set the comment box recordsource as appropriate
Select Case optCriteria
Case 1
strField = "Comments" 'name of field in underlying query
Case 2
strField = "CommentsShare"
Case 3
strField = "CommentsMgmt"
Case 4
strField = "CommentsConfid"
End Select
Me.Visible = False
DoCmd.OpenReport "rptMemoReport", acViewPreview,
OpenArgs:=strField

In the Open event of the report, I have:

If Not IsNull(Me.OpenArgs) Then
Me.txtComments.ControlSource = Me.OpenArgs
End If

Works great as is. What I would like to add is the option to combine
the
various comments on the report. For example, allow the user to have
the
"comment" field on the report contain both general and confidential
comments.
On the form, I've tried strField = "Comments" & "CommentsConfid" which
didn't work.

These fields are in a "master query" I use as support for several
reports.
For this report, all I want to change is the comments that print.

Is there a way to concantenate the fields on the report?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
This is a strange one. For some reason that I can not explain, any attempt
to pass a string beginning with an "=" sign in the OpenArgs property seems
to raise this error. The only way I could get it to work was by passing the
expression without the "=" sign, then modifying the code in the report's
Open event to add the "=" sign when necessary.

In the form ...

Select Case optCriteria
Case 1
strField = "Comments"
Case 2
strField = "CommentsShare"
Case 3
strField = "CommentsMgmt"
Case 4

'no "=" sign
strField = "[Comments] & [CommentsConfid]"

End Select
Me.Visible = False
DoCmd.OpenReport "rptMemoReport", acViewPreview, , , , strField

In the report ...

Private Sub Report_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
If Me.OpenArgs = "[Comments] & [CommentsConfid]" Then

'add the "=" sign here
Me.txtComments.ControlSource = "=[Comments] & [CommentsConfid]"

Else
Me.txtComments.ControlSource = Me.OpenArgs
End If
End If

End Sub

--
Brendan Reynolds (MVP)

KittyS said:
Sure, here it is:

Select Case optCriteria
Case 1
strField = "Comments"
Case 2
strField = "CommentsShare"
Case 3
strField = "CommentsMgmt"
Case 4
strField = "=[Comments] & [CommentsConfid]"
End Select
Me.Visible = False
DoCmd.OpenReport "rptMemoReport", acViewPreview, OpenArgs:=strField

Thanks!

Kitty


Brendan Reynolds said:
Could you post the modified code?

--
Brendan Reynolds (MVP)


KittyS said:
Brendan, I tried that and received an error:

An expression you entered is the wrong data type for one of the
arguments.

Any ideas?

Thanks.

:


Something like this in the form code should do it ...

strField = "=[Comments] & [CommentsConfid]"

--
Brendan Reynolds (MVP)

I have the following code on a form:

Dim strField As String
'Evaluate which option button was selected
'Set the comment box recordsource as appropriate
Select Case optCriteria
Case 1
strField = "Comments" 'name of field in underlying
query
Case 2
strField = "CommentsShare"
Case 3
strField = "CommentsMgmt"
Case 4
strField = "CommentsConfid"
End Select
Me.Visible = False
DoCmd.OpenReport "rptMemoReport", acViewPreview,
OpenArgs:=strField

In the Open event of the report, I have:

If Not IsNull(Me.OpenArgs) Then
Me.txtComments.ControlSource = Me.OpenArgs
End If

Works great as is. What I would like to add is the option to
combine
the
various comments on the report. For example, allow the user to have
the
"comment" field on the report contain both general and confidential
comments.
On the form, I've tried strField = "Comments" & "CommentsConfid"
which
didn't work.

These fields are in a "master query" I use as support for several
reports.
For this report, all I want to change is the comments that print.

Is there a way to concantenate the fields on the report?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top