Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Query via VBA-SQL code (Access 2000)

    How can I run a select query from VB code (SQL statement) attached to a command button?

    (I am trying to avoid saving hundreds of queries for different parameters- this could be done nicely with a some command buttons and the respective SQL statement saved within the button. RunSQL is only for action queries... Is there a way to use OpenQuery with an SQL statement rather than a saved query?)

    Thanks for the help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Open Query via VBA-SQL code (Access 2000)

    Welcome to Woody's Lounge!

    If your database is for end users, you should never let them use a query directly. Create a form or report instead, and use the WhereCondition argument of DoCmd.OpenForm or DoCmd.OpenReport.

    If the database is for your personal use only, you can use a discardable stored query and set its SQL to the generated SQL string, then open it.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Query via VBA-SQL code (Access 2000)

    Hans,

    Thanks for your help- I'll look into the OpenForm/OpenReport method, but what I actually have in mind is the following.

    I'd like a control form (perhaps with some tabs or other bells and whistles) that contains an array of control buttons (+500), each containing the definition of a specific query to be ran. When the user (not well versed in Access) clicks the button, the results are displayed.

    I'm not sure if the last part of your response applies to what I'm trying to do. Why can't I use something simple like RunSQL???

    Thanks again, Brandon.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Query via VBA-SQL code (Access 2000)

    This sounds crazy. 500 buttons on a form. How do you think the user will find his way in such an amount of buttons ?
    Can you explain a little more about what you want as query after those buttons ?
    Francois

  5. #5
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Query via VBA-SQL code (Access 2000)

    Francois,

    It is actually much simpler than it sounds. I have a large data set spanning a few years; I want to vary a few criteria on a monthly basis. Thus, the permutation for one family of queries would be something like: 3 years * 12 months * 5 options1st criteria * 2 options2nd criteria= 360
    There could be a number of families of these queries, which to a user, would be quite clear based upon the question being asked.

    So, ultimately, I just want to run each query from a command button by copying and pasting an SQL statment in its code builder (modifying the code slightly each time).

    Brandon.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Query via VBA-SQL code (Access 2000)

    This is not the way of using Access.
    If one of the conditions change over the time, you then would have to modify your code behind the buttons.
    You should use comboboxes for your different choices (I think a combobox isn't too difficult for your users).
    One for the years, one for the months, one for the 1st criteria and one for the 2nd.
    Then you should build a where string with vba and use the where string in an Docmd.OpenForm.
    If you need help to write this please post back
    Francois

Posting Permissions

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