0

On my work desktop, I have

-Microsoft Office Professional Plus 2013 Access

I've been tasked to create a MS Access application with an Access DB.

I have an MS Access Report within my application

The MS Access Report's Record Source is associated with a MS Access Query Definition

, and furthermore, said MS Access Query Definition takes an argument which I've named as idArg ( of type Double ).

However, within the MS Access Report, I also have a ListBox which is associated with Another MS Access Query Definition which takes a different argument which I've named as idArg2 ( of type Double ).

The aforementioned MS Access Report will be ultimately used to generate a pdf version of itself.

I generate the pdf programmatically from a Form's VB code:

DoCmd.SetParameter "idArg", CInt(Me.IdLabel.Caption)
DoCmd.SetParameter "idArg2", CInt(Me.IdV2.Caption)
DoCmd.OpenReport "OrgFinancialInstReport", acViewPreview
 DoCmd.OutputTo acOutputReport, "OrgFinancialInstReport", acFormatPDF, GetConstructedPdfFileName(CInt(Me.IdLabel.Caption), Me.InvestorName.Caption), True
  DoCmd.Close acReport, "OrgFinancialInstReport"

However, the code shown above Only successfully applies the CInt(Me.IdLabel.Caption) to "idArg" correctly but fails to apply the CInt(Me.IdV2.Caption) to "idArg2"

therefore, when the above code is run, I see the popup box that requests a value for "idArg2"

What steps do I have to take in order to implement said MS Access Report so that I can assign the CInt(Me.IdV2.Caption) to "idArg2" properly?

braX
  • 11,506
  • 5
  • 20
  • 33
crazyTech
  • 1,379
  • 3
  • 32
  • 67

1 Answers1

1

Simply remove the query parms. Just base the report on the raw query.

Then, to filter the report - pass it a "where" clause.

So:

dim strWhere     as string
dim strReport    as string

strReport = "OrgFinancialInstReport"
strWhere = "Arg = " & CInt(me.IdLabel.Caption)
DoCmd.OpenReport strReport, acViewPreview,,strWhere

DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, 
     GetConstructedPdfFileName(CInt(Me.IdLabel.Caption), Me.InvestorName.Caption), True
DoCmd.Close acReport, strReport

And for the other report, we go:

dim strWhere     as string
dim strReport    as string

strReport = "OrgFinancialInstReport"
strWhere = "Arg2 = " & CInt(Me.IdV2.Caption)
DoCmd.OpenReport strReport, acViewPreview,,strWhere

DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, 
     GetConstructedPdfFileName(CInt(Me.IdLabel.Caption), Me.InvestorName.Caption), True
DoCmd.Close acReport, strReport

You could even break out the above code that creates the PDF as a separate routine - call it from any form - just pass the report name, and the where clause, and maybe the caption.

So, when you don't know the number of parameters ahead of time, or they change? Just use the where clause. Your query will thus have no parameters.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Thx, But I'm asking about a case where a Single MS Access Report needs multiple parameter arguments(in fact, to be more specific, my Single MS Access Report expects an argument for it's own QueryDef, & a listbox within said Single MS Access Report has another QueryDef associated with it which expects another argument parameter.) – crazyTech Nov 28 '19 at 14:17
  • 1
    Having two separate places for params is difficult. Remove all the parameters, and then just pass a simple where clause to the query. It is these all over the place parameters that is causing difficulty here. You should be able to filter the report, and not have to filter the original query. This way, then you are free to cook up any filter you want.However, because you have a query, and then a report,you might consider overwriting the original query raw sql. However, if possible, pass a where clause. – Albert D. Kallal Nov 28 '19 at 18:08