Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2011
    Posts
    54

    Unanswered: Please help with an easy macro

    I'm new to the forum and VB code

    I need to make a macro script for something at work.

    I have six queries, queries A, B, C, D, E and F.

    When you open the query, it asks for to input the name of the owner of a particular project. Afterwards it will display the list of projects associated with the owner, which is the first column and the criteria.

    Each of the six queries are for separate purposes (i.e. risks, issues, milestones etc.) but what is shown is still determined by the project owner.

    I need to make a macro script which will run the six queries in ORDER (and also ask to input the name of the owner), and save the output of each query to ONE report.

    Thanks for any help I hope this will be easy.

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    It would be easier if you could put the SQL of your queries down.

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    I suggest to do this via VBA (in the Form).

  4. #4
    Join Date
    Jul 2011
    Posts
    54
    Quote Originally Posted by MStef-ZG View Post
    I suggest to do this via VBA (in the Form).
    How would that work exactly?

  5. #5
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Send a short example of your MDB, (access 2000 or 2002-2003).

  6. #6
    Join Date
    Jul 2011
    Posts
    54
    Quote Originally Posted by MStef-ZG View Post
    Send a short example of your MDB, (access 2000 or 2002-2003).
    Ah sh*te its confidential, I cant show any of it

    ummm its just a bunch queries where there is a field called "project owner" and it asks for the name to be inputted, and if you input a matching name, it shows all the associated records. one query for each table. I just need these queries (based on the input) to be outputted to one report.

    can you just give some general coding ideas?

  7. #7
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Put a command button on the form.
    On click this button put Sub with the code.
    For run a query it is:
    DoCmd.OpenQuery "NameOfQuery"

    If you have got more queries, put this code more tiems.
    Look in access help "OpenQuery" method.

  8. #8
    Join Date
    Jul 2011
    Posts
    54
    Quote Originally Posted by MStef-ZG View Post
    Put a command button on the form.
    On click this button put Sub with the code.
    For run a query it is:
    DoCmd.OpenQuery "NameOfQuery"

    If you have got more queries, put this code more tiems.
    Look in access help "OpenQuery" method.
    Cheers, how would you store the output of each query into one report?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what makes each query different, apart form the filter (where clause) bit

    if they use the same data columns then 'just' supply the relevanbrt filter as aprt of the call to the report. you can beautify the report by topping and tailing it by addign specific information eg a page header that reads' report type:-1' or 'report type:-x' whatever you need.

    if its different columns then alter the reprots rowsource when you open the report, assign the data to unbound columns as required OR have 6 individual reports
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jul 2011
    Posts
    54
    Quote Originally Posted by healdem View Post
    what makes each query different, apart form the filter (where clause) bit

    if they use the same data columns then 'just' supply the relevanbrt filter as aprt of the call to the report. you can beautify the report by topping and tailing it by addign specific information eg a page header that reads' report type:-1' or 'report type:-x' whatever you need.

    if its different columns then alter the reprots rowsource when you open the report, assign the data to unbound columns as required OR have 6 individual reports
    ok like there are six tables: projects, milestones, interdependencies, risks, issues and financials. Each one has a project owner, and project name.

    I have a query that shows results based on entered project owner.

    I just need the output of each query to store in one report in order, using 1 macro.

    Can you just please give me some coding help?

  11. #11
    Join Date
    Jul 2011
    Posts
    54
    UPDATE:

    Here's what I have.

    A bunch of Ouput to statements which create a PDF output file based on queries.

    How can I make them output to 1 pdf file?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what you could do is a create a single report that comprises 5 sub reports (one of each of the other tables)
    the top level is the project, the link fiedl between each of the sub reports is the project
    design you 5 queries to extract the information required form whatever columns you need, make certain you have a common link field between the top level report and the sub report in the query (project id seems a good candidate).
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Jul 2011
    Posts
    54
    Quote Originally Posted by healdem View Post
    so what you could do is a create a single report that comprises 5 sub reports (one of each of the other tables)
    the top level is the project, the link fiedl between each of the sub reports is the project
    design you 5 queries to extract the information required form whatever columns you need, make certain you have a common link field between the top level report and the sub report in the query (project id seems a good candidate).
    That's exactly what I did just before you posted and it worked thanks again

Posting Permissions

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