Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    Tunbridge Wells, Kent. UK
    Posts
    24

    Unanswered: Passing a value to a VBA expression

    I need to output a report, that draws its data from a query. If I have a parameter query it prompts me for the parm value during execution. However I want to have a form with a button, when pushed, it outputs the report with a specific value already known, without the need to be prompted.

    Below is the VBA expression. I need to pass a it a value so it only produces asub set of the records. How?

    PLease help!! Thanks



    DoCmd.OutputTo acReport, "Fixtures", "HTML(*.html)", "c:\fixtures.html", False, "", 0


    This report 'Fixtures' uses a query to get its records

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    Aquick fix: create another copy of Fixtures report that works with that specific value and output that new copy.
    ghozy.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    recommendation: abandon parameter queries entirely!

    parameters confine you to A's ugly automatic prompt, and in the case of multiple parameters, the risk of the user screwing up one or the other of the inputs is very high. worse, the user cannot review his parameters so he does not know which parameter was wrong if there is an error.

    if you can get your "known value" (or values) into a textbox or combo or list

    open your query in design view. click once in the grid on the criteria box that you are trying to set a parameter for. click the magic-wand thingie in the tool bar. in the left pane of the ensuing dialog navigate through Forms, All Forms, to the form that holds your textbox (combo etc), and double-click on the name of the textbox (combo etc) in the middle pane.

    et voila! your query now takes it's parameter from the form.

    note also that you can do the same with variables and constants as parameters, but only indirectly: you need to set up a function that returns the value of the var/const and use that function as criteria.

    caveats:
    the form needs to be open when you run the report.
    the parameter value needs to be in the bound column if you are using combo/list.
    the form holding the const/var & function needs to be open if they live on a form (otherwise global module is fine too)

    check out the query in SQL view after you have done this so you can type the SQL directly next time... it's usually faster.

    it is only a very small step from here to a truly general report screen which allows the user to define (or not define) as many "parameters" for the report as he wants, including Like "*blah*" parameters" if you wish.

    izy
    Last edited by izyrider; 10-17-04 at 06:11.
    currently using SS 2008R2

  4. #4
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    or parse the required parameters to a SQl subroutine which creates te desired SQL line

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •