11-21-11, 09:48 #1Registered User
- Join Date
- Aug 2011
Unanswered: Run multiple select queries on-the-fly
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:
but I want "qryeng" , "qrygen" , "qrygrbx" to be select queries based on the combo box selection using this code:
WHERE tbleng.eqWMI='" & Me.cboeqWMI.Value &"'
ORDER BY tbleng.eqWMI;
I am having trouble on where to put the SELECT code.
11-21-11, 20:54 #2Registered User
- 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
11-22-11, 00:48 #3(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
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 queryshope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON