Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011

    Unanswered: Run multiple select queries on-the-fly

    Access 2007

    The database is an equipment database where all the equipment is broken down into its mechanical parts (ie engine, generator, electric motor, gearbox). The tables are seperated by mechanical parts, so all the engines (and their specifications like horsepower and size) for all the equipment are listed in one table. To relate all the parts back to the piece of equipment they belong to, I have a field in each table that is the equipment's ID number.

    I am trying to create a way to gather information from all the tables that relate to a specific equipment's ID number and display it on a report, but I want the user to select which equipment they want to view in the report, hence the need for an "on-the-fly" select query.

    Right now I have a dialog box that contains a combo box (to select from a table list of equipment ID numbers), an OK command button and a Cancel command button.

    I was planning to set the on-click event for the OK button to:

    DoCmd.OpenQuery "qryeng"
    DoCmd.OpenQuery "qrygen"
    DoCmd.OpenQuery "qrygrbx"

    but I want "qryeng" , "qrygen" , "qrygrbx" to be select queries based on the combo box selection using this code:

    SELECT tbleng.*
    FROM tbleng
    WHERE tbleng.eqWMI='" & Me.cboeqWMI.Value &"'
    ORDER BY tbleng.eqWMI;

    I am having trouble on where to put the SELECT code.

  2. #2
    Join Date
    Aug 2011
    I don't know if this code is efficient but it got the job done.

    Private Sub cmdOK_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf1 As DAO.QueryDef
    Dim strSQL1 As String
    Set qdf1 = db.QueryDefs("qryeng")
    strSQL1 = "SELECT tbleng.* " & _
             "FROM tbleng " & _
             "WHERE tbleng.[eqWMI]='" & Me.cboeqWMI.Value & " ';"
    qdf1.SQL = strSQL1
    DoCmd.OpenQuery "qryeng"
    Dim qdf2 As DAO.QueryDef
    Dim strSQL2 As String
    Set qdf2 = db.QueryDefs("qrygen")
    strSQL2 = "SELECT tblgen.* " & _
             "FROM tblgen " & _
             "WHERE tblgen.[eqWMI]='" & Me.cboeqWMI.Value & " ';"
    qdf2.SQL = strSQL2
    DoCmd.OpenQuery "qrygen"
    Dim qdf3 As DAO.QueryDef
    Dim strSQL3 As String
    Set qdf3 = db.QueryDefs("qryelecmtr")
    strSQL3 = "SELECT tblelecmtr.* " & _
             "FROM tblelecmtr " & _
             "WHERE tblelecmtr.[eqWMI]='" & Me.cboeqWMI.Value & " ';"
    qdf3.SQL = strSQL3
    DoCmd.OpenQuery "qryelecmtr"
    Dim qdf4 As DAO.QueryDef
    Dim strSQL4 As String
    Set qdf4 = db.QueryDefs("qrygrbx")
    strSQL4 = "SELECT tblgrbx.* " & _
             "FROM tblgrbx " & _
             "WHERE tblgrbx.[eqWMI]='" & Me.cboeqWMI.Value & " ';"
    qdf4.SQL = strSQL4
    DoCmd.OpenQuery "qrygrbx"
    Dim qdf5 As DAO.QueryDef
    Dim strSQL5 As String
    Set qdf5 = db.QueryDefs("qrycomp")
    strSQL5 = "SELECT tblcomp.* " & _
             "FROM tblcomp " & _
             "WHERE tblcomp.[eqWMI]='" & Me.cboeqWMI.Value & " ';"
    qdf5.SQL = strSQL5
    DoCmd.OpenQuery "qrycomp"
    Set qdf1 = Nothing
    Set qdf2 = Nothing
    Set qdf3 = Nothing
    Set qdf4 = Nothing
    Set qdf5 = Nothing
    Set db = Nothing
    DoCmd.Close acForm, Me.Name
    End Sub
    After I got that done, I thought about adding check boxes to the dialog box along with the "cboeqWMI" combobox to let the user choose which queries to run. I think I need IF statements but don't know where they go logically. There would be a checkbox for each QueryDef and if it is checked, the query is ran on the on click event, otherwise the query is not.

  3. #3
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10

    open the from that has the combo box


    open "qryeng" , "qrygen" , "qrygrbx"

    and point the Criteria of each query to the Form/combobox

    then create a bottom to show those querys
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

Posting Permissions

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