Results 1 to 9 of 9

automatically create queries

  1. #1
    GEORGIA Guest

    automatically create queries

    I am currently creating quries based on collector's name. for example:
    qrySam, qryJane, qryBob. These are exported out to excel spreadsheet in
    different tabs. Is there a way to somehow in code create queries based on
    name so that if there are new names added to the main table, I wouldn't have


    to create queries for each new name? any suggestions? I maybe be making it
    harder than it should.
    thank you!

  2. #2
    Alex Dybenko Guest

    Re: automatically create queries

    Hi,
    yes, you can use CreateQueryDef Method
    Look at Access help for more info and examples

    --
    Alex Dybenko (MVP)
    http://Alex.Dybenko.com
    http://www.PointLtd.com

  3. #3
    Nikos Yannacopoulos Guest

    Re: automatically create queries

    I maybe be making it harder than it should.
    Yes you are! You should only have *one* query, and use a parameter in it
    to select the collector's name at runtime. Here's an idea:

    * Create a table to hold collectors' names (unless it already exists);
    * Define a public valiable in a standard module, to temporarily hold the
    collector's name;
    * Create a simple function in the module, to return the collector's name
    currently stored in the public variable, so the query can "read" it as a
    parameter;
    * Change your query so the criterion under the collector name field
    reads the public variable through the function;
    * Use code to loop through the collectors' names and export the query
    results.

    Sample code:

    Option Compare Database

    Public vCollector As String
    Function F_Collector()
    F_Collector = vCollector
    End Function

    Sub Export_qryCollector()
    Dim db As DAO.databasee
    Dim rst As DAO.Recordset
    Dim MyExportFile As String
    Dim qry As String
    Dim tbl As String
    qry = "qryCollector"
    tbl = "tblCollectors"
    MyExportFile = "C:\My Documents\Collector Data.xls"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(tbl)
    rst.MoveFirst
    Do Until rst.EOF
    vCollector = rst.Fields("Collector_Name")
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, _
    qry, MyExportFile, True, vCollector
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    End Sub

    Assumptions:
    Query name: qryCollector
    Table name: tblCollectors, field: Collector_Name

    The criterion in the query should be:
    F_Collector()

    The code loads the collector names as a recordset, and loops through
    each, temporarily setting the variable vCollector to each name, and
    exporting the query results to a separate sheet named after the
    currently selected collector name.

    Note: To run this code, an appropriate DAO Object Library reference is
    required. While in the VB editor window, go to menu item Tools >
    References; check if a Microsoft DAO reference is present among the ones
    checked at the top of the list. If not, scroll down to find the
    appropriate Microsoft DAO X.X Object Library reference and check it. The
    appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

    The above approach assumes you will always want to export all
    collectors. If you want to be able to select manually which ones to
    export each time, then make a form with a multi-select listbox with the
    collectors' names, and modify the code to loop through the selections
    instead of opening tblCollectors as a recordset.

    HTH,
    Nikos

  4. #4
    GEORGIA Guest

    Re: automatically create queries

    I am sorry, i cannot find it. Can you suggest a good website for me learn
    this function?
    Thank you!

    Hi,
    yes, you can use CreateQueryDef Method
    Look at Access help for more info and examples

  5. #5
    GEORGIA Guest

    Re: automatically create queries

    Wow!
    Thank you for easy to follow instruction!
    this is my code:

    Function F_collector()
    F_collector = vcollector

    End Function

    Sub export_qrycollector()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim myexportfile As String
    Dim qry As String
    qry = "qrycollector"
    tbl = "tblcollectors"
    myexportfile = "d:\my documents\collector data.xls"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(tbl)
    rst.MoveFirst
    Do Until rst.EOF
    vcollector = rst.Fields("collector_name")
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, qry, myexportfile,
    True, vbcollector
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set db = Nothing

    End Sub
    and my query looks like this:

    SELECT CCC.[Collector Name], CCC.[Invoice #], CCC.[361+]
    FROM CCC
    WHERE (((CCC.[Collector Name])=F_collector()));

    do i simply run the query??
    sorry for my ignorance...
    i have the function and sub export_qrycollector() in one module.

    thank you!

  6. #6
    GEORGIA Guest

    Re: automatically create queries

    I have figured it out. However, there's a problem.
    There are 5 names in the tblcollectors and when exported, it is only
    exporting one name. (last one on the list). What am i doing wrong?

  7. #7
    GEORGIA Guest

    Re: automatically create queries

    ok..
    i don't know what i did but it is working PERFECTLY!!!
    THANK YOU SOOOOOOO MUCH!!!

  8. #8
    Nikos Yannacopoulos Guest

    Re: automatically create queries

    You are welcome! Glad that you could troubleshoot on your own, that's
    where the real value lies.

    Nikos

  9. #9
    Join Date
    Jan 2006
    Posts
    1

    Re: automatically create queries

    Hi,
    I have tried your method but was not able to read through the hole table where the collector names are stored.
    If I put F_COLLECTOR() in the criteria, It will only read the last record.

    Couldyou please advise.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to create a Password Form
    By heman85 in forum Forms
    Replies: 1
    Last Post: 07-07-2011, 11:49 AM
  2. Replies: 0
    Last Post: 11-10-2008, 12:32 PM
  3. how to create a macro or code to...
    By Eaglezinha in forum Access
    Replies: 1
    Last Post: 10-20-2008, 04:01 PM
  4. Have database open automatically
    By Norman Masson in forum Access
    Replies: 6
    Last Post: 01-08-2008, 07:41 AM
  5. Create the link
    By accessman2 in forum Access
    Replies: 0
    Last Post: 03-13-2006, 01:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums