Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    38

    Unanswered: Passing a paremeter to a query via a report

    Hi All,

    I wrote a database to run my company a couple of years ago in access 2007. It works great and I continually add extra functionality.

    A year ago I added the facility that creates invoices. The invoices show on a form and when all the details are correct on the form, the user selects the Generate Invoice button which runs a report.

    The report runs a query and the report is then shown on screen which can then be printed.

    The query collects all the relevant data for the report to show the invoice and is based on the invoice ID, (INV_ID).

    My problem is this:

    When I select print invoice, the report runs, which calls the query, and then a box appears asking for the invoice number. Since the form already has the invoice number displayed, how can I get this number into the criteria of the query?

    Finally, we send all our invoices as PDF, is there a way that when I select generate invoice button that the report automatically creates the PDF rather than having to right clcik, select print and so on.

    Thanks for any help.
    Last edited by nigelc; 02-20-12 at 05:30.

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    In your query simply add the following to the criteria field under Invoice ID

    =forms!formname!formcontrolthatisboundtoinvoiceid

    As for creating PDF have a look at this link ReportToPDF
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Jul 2009
    Posts
    38
    Dave,

    Thanks for that.

    I will try it our ASAP

    Nige

  4. #4
    Join Date
    Jul 2009
    Posts
    38
    Dave,

    I entered the criteria in the format you detailed, which is where I had been putting my efforts previuosly, but it still asks the user for input.

    I entered into the criteria =forms!frmInvoices!tbInvoiceID but as soon as I click out of the criteria field, what I entered changes to =[forms]![frmInvoices]![tbInvoiceID]. I still saved and closed the query.

    I then ran the report from my form and got the box pop up asking for [forms]![frmInvoices]![tbInvoiceID]

    I went back to the query and the '=' had also be removed leaving just [forms]![frmInvoices]![tbInvoiceID]

    Any ideas what I am doing wrong?

    Thanks

    Nige

  5. #5
    Join Date
    Jan 2012
    Posts
    97
    Assuming when you run the report the form is open?

    If the form is open and this is still happening, try recreating the report and see if your still get the parameter box pop up.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  6. #6
    Join Date
    Jul 2009
    Posts
    38
    Dave,

    The form is open, as the button to run the report is actually on the form.

    However, I have just added another button to the form, that runs the query and I get the parameter box up still. Therefore, I conclude that the problem is not with the report but with the query.

    I then ran the query directly and still get the parameter box up. If the query understood that is should get the criteria value from a control on a form, then I would expect the query to crash or come up with an error when I run the query directly without the form being open!

    This is really winding me up now.

    Nige

  7. #7
    Join Date
    Jul 2009
    Posts
    38

    Fixed

    Dave,

    Thanks for your help.

    I just fixed the problem.

    Although the form was open, it was embedded in another form, therefore I needed to write: [Forms]![frmInvoiceProjectDetails]![frmInvoices].[Form]![tbID]

    Don't fully understand the .[Form]![tbID] but it works! The tbID is obviuos but why it is suffixed with [form] I do not know.

    I have downloaded the pdf files you mentioned. Looks good but I will need to fully understand it before I put it in my database.

    Nige

  8. #8
    Join Date
    Jan 2012
    Posts
    97
    If your referencing a control on a sub form in Access you will need to reference it through the main form. Access tends to do some strange things occasionally.

    Your welcome.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

Posting Permissions

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