CODEWITHSUNDEEP
c#ms-access
R.Romero
R.Romero

Reputation: 161

Parameter value in Access report dialog

so i'm trying to automate some reporting in Access 2013. When I run a report I get a dialog asking for a parameter (Enter Plant:), something like this. Image

What I want is to run this code without asking the query for a plant name. The code works but if I run it, it pops a dialog asking for a Plant name, and if I type the Plant Name it runs and saves the pdf file just like I want to. The report in Access works by giving a different Plant name and it outputs a different report depending on the given Plant. My idea is to put this code on a loop and in each iteration pass a different plant name and save a different new file. But it always pop a dialog asks for a plant name to be added manually.

Microsoft.Office.Interop.Access.Application oAccess = null;

            // Start a new instance of Access for Automation:

            oAccess = new Microsoft.Office.Interop.Access.Application();


            // Open a database in exclusive mode:

            oAccess.OpenCurrentDatabase(
               "route DB", //filepath
               true //Exclusive
               );

            //This doesnt work

           // oAccess.DoCmd.SetParameter("[Enter Plant:]", "Arlington");


            oAccess.DoCmd.OpenReport(
                "06 - Security Report - Plants",
                AcView.acViewReport,
                "qry Security Report - Plant",

                //This doesnt work either, still asks me for a plant name
                "[Enter Plant:] ='Arlington'",
                AcWindowMode.acWindowNormal
            );

            //If I give the plant name to the dialog it works correctly en saves a pdf file wit the report

             oAccess.DoCmd.OutputTo(
                 AcOutputObjectType.acOutputReport,
                    System.Reflection.Missing.Value,
                  "PDF Format (*.pdf)",
                  "route to save file",
                  false,
                  System.Reflection.Missing.Value,
                  System.Reflection.Missing.Value,
                  AcExportQuality.acExportQualityPrint
               );

            oAccess.Quit();

I can access the query but unfortunately i cant modify it, also its pretty long thats why I will not be able to show it(looks like it was created by a the Access Wizard, so its preeety long) though here its an example where the asked parameter is being used:

AND ((Signers.Location)=[Enter Plant:])

This parameters is in the query like 40+ times.

Any ideas? Thanks in advance!

Upvotes: 0

Views: 694

Answers (1)

JarbingleMan
JarbingleMan

Reputation: 460

I have faced this many times at work, and the solution I used most often is to have a TextBox in a form somewhere that can be referenced directly.

AND ((Signers.Location)=[Forms]![frmMyForm]![txtMyPlantTextBox])

NOTE: You would need to have the form open and data entered into the TextBox before you run the query.

Upvotes: 0

Related Questions