Parameter Query

[jovii]

Hi all,

I'm having a problem passing values to a parameter query. The
parameter query requires the following values:
Start date, End date (from frm!reportsmenu) and customer number (from
frm!frominvoicestoemail)

The parameter query: qryInvoices_BillsEmail is used in report:
rptInvoices_BillsEmail

The where clause in the query is :
detail.tradedate Between [forms]![reportsmenu].[startdate] And [forms]!
[reportsmenu].[enddate AND
detail.cusno = [forms]![frminvoicestoemail].[cusno]

The report is output to rtf.

I use the following code which works fine:

Set db = CurrentDb()

On Error GoTo err_deal

Set q = db.QueryDefs("qryInvoices_BillsEmail")

For Each prm In q.Parameters
prm.Value = Eval(prm.Name)
Next prm

DoCmd.OutputTo acOutputReport, "rptInvoices_BillsEmail",
acFormatRTF, "c:\petrochemicals\invoices\" & cusno & ".rtf"

My problem is the customer number can also come from another form (frm!
frominvoicestoemail), so the above code will not work. I change the
parameter query to the following:

detail.tradedate) Between [enter startdate] And [enter enddate] AND
detail.cusno = [Enter Customer Number]

And used the following code:

Set db = CurrentDb()

Set q = db.QueryDefs("qryInvoices_BillsEmail")


q.Parameters("Enter StartDate") = Forms!ReportsMenu!StartDate
q.Parameters("Enter EndDate") = Forms!ReportsMenu!EndDate
q.Parameters("Enter cusno") = Forms!frminvoicestoemail!cusno


DoCmd.OutputTo acOutputReport, "rptInvoices_BillsEmail",
acFormatRTF, "c:\petrochemicals\invoices\" & cusno & ".rtf"

When the code tries to outputthe report to rtf I have to re-enter the
start date, end date and cusno.

Is there anyway to pass the customer number from either form into the
parameter query and the export the report to rft without having to re-
enter the start date, end date and cusno?

TIA

Joe V

 

[Dirk Goldgar]

in message

Hi all,

I'm having a problem passing values to a parameter query. The
parameter query requires the following values:
Start date, End date (from frm!reportsmenu) and customer number (from
frm!frominvoicestoemail)

The parameter query: qryInvoices_BillsEmail is used in report:
rptInvoices_BillsEmail

The where clause in the query is :
detail.tradedate Between [forms]![reportsmenu].[startdate] And [forms]!
[reportsmenu].[enddate AND
detail.cusno = [forms]![frminvoicestoemail].[cusno]

The report is output to rtf.

I use the following code which works fine:

Set db = CurrentDb()

On Error GoTo err_deal

Set q = db.QueryDefs("qryInvoices_BillsEmail")

For Each prm In q.Parameters
prm.Value = Eval(prm.Name)
Next prm

DoCmd.OutputTo acOutputReport, "rptInvoices_BillsEmail",
acFormatRTF, "c:\petrochemicals\invoices\" & cusno & ".rtf"

My problem is the customer number can also come from another form (frm!
frominvoicestoemail), so the above code will not work. I change the
parameter query to the following:

detail.tradedate) Between [enter startdate] And [enter enddate] AND
detail.cusno = [Enter Customer Number]

And used the following code:

Set db = CurrentDb()

Set q = db.QueryDefs("qryInvoices_BillsEmail")


q.Parameters("Enter StartDate") = Forms!ReportsMenu!StartDate
q.Parameters("Enter EndDate") = Forms!ReportsMenu!EndDate
q.Parameters("Enter cusno") = Forms!frminvoicestoemail!cusno


DoCmd.OutputTo acOutputReport, "rptInvoices_BillsEmail",
acFormatRTF, "c:\petrochemicals\invoices\" & cusno & ".rtf"

When the code tries to outputthe report to rtf I have to re-enter the
start date, end date and cusno.

Is there anyway to pass the customer number from either form into the
parameter query and the export the report to rft without having to re-
enter the start date, end date and cusno?

I don't think any of what you are doing with the querydef makes a bit of
difference to the report. If the report's RecordSource is
"qryInvoices_BillsEmail", Access will run that query afresh, evaluating the
parameters, when you open the report.

If you want to vary the source of the parameters for different openings of
the report, I suggest that you take the following steps:

1. Change the querydef so that the only parameters it uses are those that
will not change from run to run.

2. Put code in the report's Open event that determines (somehow) which form
it should use as the source of the cusno parameter (or any other parameters
that are subject to variation). Then have that code rewrite the report's
RecordSource, like this for example:

'----- start of example code -----
Private Sub Report_Open(Cancel As Integer)

' Is the form "frmInvoicesToEmail" open?
If CurrentProject.AllForms("frmInvoicesToEmail").IsLoaded Then
Me.RecordSource = _
"SELECT * FROM qryInvoices_BillsEmail " & _
"WHERE cusno = [Forms]![frmInvoicesToEmail]![CusNo]"
Else
Me.RecordSource = _
"SELECT * FROM qryInvoices_BillsEmail " & _
"WHERE cusno = [Forms]![SomeOtherForm]![CusNo]"
End If

End Sub
'----- end of example code -----

To make this work, you see, you need to have some way that the report can
determine which criterion to apply. If you can do that, it should work.
You might use the report's OpenArgs property to do it, but then you'd have
to modify your code that opens the report to open it and pass the argument,
then use OutputTo to write it out.

 

[jovii]

You're right Dirk. The qdef code is meaningless. I don't know why it
was in there. I thought it was needed to run the report.

It looks like I'll have to use the report's open event an change the
record source.

Thanks for your reply

Joe V

in message

Hi all,

  I'm having a problem passing values to a parameter query. The
parameter query requires the following values:
Start date, End date (from frm!reportsmenu) and customer number (from
frm!frominvoicestoemail)

The parameter query: qryInvoices_BillsEmail is used in report:
rptInvoices_BillsEmail

The where clause in the query is :
detail.tradedate Between [forms]![reportsmenu].[startdate] And [forms]!
[reportsmenu].[enddate AND
detail.cusno = [forms]![frminvoicestoemail].[cusno]

The report is output to rtf.

I use the following code which works fine:

Set db = CurrentDb()

On Error GoTo err_deal

Set q = db.QueryDefs("qryInvoices_BillsEmail")

   For Each prm In q.Parameters
      prm.Value = Eval(prm.Name)
   Next prm

        DoCmd.OutputTo acOutputReport, "rptInvoices_BillsEmail",
acFormatRTF, "c:\petrochemicals\invoices\" & cusno & ".rtf"

My problem is the customer number can also come from another form (frm!
frominvoicestoemail), so the above code will not work. I change the
parameter query to the following:

detail.tradedate) Between [enter startdate] And [enter enddate] AND
detail.cusno = [Enter Customer Number]

And used the following code:

Set db = CurrentDb()

Set q = db.QueryDefs("qryInvoices_BillsEmail")

q.Parameters("Enter StartDate") = Forms!ReportsMenu!StartDate
q.Parameters("Enter EndDate") = Forms!ReportsMenu!EndDate
q.Parameters("Enter cusno") = Forms!frminvoicestoemail!cusno

        DoCmd.OutputTo acOutputReport, "rptInvoices_BillsEmail",
acFormatRTF, "c:\petrochemicals\invoices\" & cusno & ".rtf"

When the code tries to outputthe report to rtf I have to re-enter the
start date, end date and cusno.

Is there anyway to pass the customer number from either form into the
parameter query and the export the report to rft without having to re-
enter the start date, end date and cusno?

I don't think any of what you are doing with the querydef makes a bit of
difference to the report.  If the report's RecordSource is
"qryInvoices_BillsEmail", Access will run that query afresh, evaluating the
parameters, when you open the report.

If you want to vary the source of the parameters for different openings of
the report, I suggest that you take the following steps:

1. Change the querydef so that the only parameters it uses are those that
will not change from run to run.

2. Put code in the report's Open event that determines (somehow) which form
it should use as the source of the cusno parameter (or any other parameters
that are subject to variation).  Then have that code rewrite the report's
RecordSource, like this for example:

'----- start of example code -----
Private Sub Report_Open(Cancel As Integer)

    ' Is the form "frmInvoicesToEmail" open?
    If CurrentProject.AllForms("frmInvoicesToEmail").IsLoaded Then
        Me.RecordSource = _
            "SELECT * FROM qryInvoices_BillsEmail  " & _
            "WHERE cusno = [Forms]![frmInvoicesToEmail]![CusNo]"
    Else
        Me.RecordSource = _
            "SELECT * FROM qryInvoices_BillsEmail  " & _
            "WHERE cusno = [Forms]![SomeOtherForm]![CusNo]"
    End If

End Sub
'----- end of example code -----

To make this work, you see, you need to have some way that the report can
determine which criterion to apply.  If you can do that, it should work..
You might use the report's OpenArgs property to do it, but then you'd have
to modify your code that opens the report to open it and pass the argument,
then use OutputTo to write it out.