passing params to a report

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

Guest

I have a report that prints all the records from a table.

I want to print this report on click of a button in a form.

I have the following code for OnClick event to print -

stLinkCriteria = "[CONTRACT_NO]=" & "'" & Me![CONTRACT_NO] & "'" & " and
[T_NO]= " & "'" & Me![T_NO] & "'" & " and [J_NO] = " & "'" & Me![J_NO] & "'"
& " and [PHASE_NO] = " & "'" & Me![PHASE_NO] & "'"
If IsNull([stLinkCriteria]) Then
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria
Else
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria
End If

(well, I will change the if condition thing later on ...)

When I click, I do get to see the report, but its shows all the pages, I
want to restrict printing to only the records I pass thru' stLinkCriteria
stated above.

I think I am not doing it correctly.

Do I have to write a query to print report with filtered records or this way
it will work.

Since I already have the report to print for all records, is it possible for
me to just specify the criteria instead of redoing the report?

Thanks for your time!
-Me
 
I have a report that prints all the records from a table.

I want to print this report on click of a button in a form.

I have the following code for OnClick event to print -

stLinkCriteria = "[CONTRACT_NO]=" & "'" & Me![CONTRACT_NO] & "'" & " and
[T_NO]= " & "'" & Me![T_NO] & "'" & " and [J_NO] = " & "'" & Me![J_NO] & "'"
& " and [PHASE_NO] = " & "'" & Me![PHASE_NO] & "'"
If IsNull([stLinkCriteria]) Then
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria
Else
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria
End If

(well, I will change the if condition thing later on ...)

When I click, I do get to see the report, but its shows all the pages, I
want to restrict printing to only the records I pass thru' stLinkCriteria
stated above.

I think I am not doing it correctly.

Do I have to write a query to print report with filtered records or this way
it will work.

Since I already have the report to print for all records, is it possible for
me to just specify the criteria instead of redoing the report?

Thanks for your time!
-Me
Click to expand...

The where clause argument of the OpenReport method is in the 4th
position. You have your stLinKCriteria in the 3rd position. You need
to add an extra comma (shown below).

Your stLinkCriteria can also be modified a bit. I've added an extra
space (for clarity) in the single and double quotes. Remove those
spaces when you write your code. I'm following your example, which
indicates all of the fields are Text datatype. If any, or all, of
those fields are Number datatypes you will need to modify the clause.

No need to check if stLinkCriteria Is Null. If it's null all the
records will display, otherwise only the filtered records will
display.

Dim stLinkCriteria as String
stLinkCriteria = "[CONTRACT_NO]= ' " & Me![CONTRACT_NO] & " ' and
[T_NO] = ' " & Me![T_NO] & " ' and [J_NO] = ' " & Me![J_NO] & " '
and [PHASE_NO] = ' " & Me![PHASE_NO] & " ' "

DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
 
you need another comma before stLinkCriteria

DoCmd.OpenReport stDocName, acViewPreview,, stLinkCriteria


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
 
stLinkCriteria = "[CONTRACT_NO]=" & "'" & Me![CONTRACT_NO] & "'" & "
and [T_NO]= " & "'" & Me![T_NO] & "'" & " and [J_NO] = " & "'" &
Me![J_NO] & "'" & " and [PHASE_NO] = " & "'" & Me![PHASE_NO] & "'"
If IsNull([stLinkCriteria]) Then
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria
Else
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria
End If
Click to expand...
Click to expand...

In addition to Crystal's help, I wanted to ask about the way you are
calling the report. Why are you checking for Null if you wind up calling
the exact same syntax anyway? Also, stLinkCriteria will *never* be null,
because you are explicitly assigning a value to it's string. Sure,
individual parts of it may not be filled in on your form, but you'll
always have the criteria portion.
 
Back
Top