Transfer values from Form to Reports

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I have created a form which allows the user to open a
report using various values merged into the
stLinkCriteria. This works great due to some help from
this newsgroup. I have also added the final line in the
code so that the criteria used can be displayed in a
textbox on the report that is being opened. I seem to
have an error in the syntax. Below is the last few lines
of the code used to open the report using the filter
criteria. The last line is the one that is creating
problems for me. Can you see what I have done wrong? I
have tried variations using "[ ]" and such but to no
avail.

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If

If Not IsNull(Me![RoutingNum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[RoutingID]=" & Me![RoutingNum]
End If

stLinkCriteria = vStr
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Reports.DetailReport.text99=Forms.CriteriaForm.RoutingNum
 
I have created a form which allows the user to open a
report using various values merged into the
stLinkCriteria. This works great due to some help from
this newsgroup. I have also added the final line in the
code so that the criteria used can be displayed in a
textbox on the report that is being opened. I seem to
have an error in the syntax. Below is the last few lines
of the code used to open the report using the filter
criteria. The last line is the one that is creating
problems for me. Can you see what I have done wrong? I
have tried variations using "[ ]" and such but to no
avail.

If Not IsNull(Me![PONumber1]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PoNum]=" & Me![PONumber1]
End If

If Not IsNull(Me![RoutingNum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[RoutingID]=" & Me![RoutingNum]
End If

stLinkCriteria = vStr
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Reports.DetailReport.text99=Forms.CriteriaForm.RoutingNum
Click to expand...

Do it in the report itself.
Set the Control source of Text99 to:

=Forms!CriteriaForm!RoutingNum

Notice that I'm using the bang (!) not the dot in the syntax.

You can add some text to it if you wish:
="This report is for Routing Number " & Forms!CriteriaForm!RoutingNum

The form must be open when the report is being run.
You can close the form, if you want, in the report's close event.
 
Back
Top