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:
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
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
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
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
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
Set qdf1 = Nothing
Set qdf2 = Nothing
Set qdf3 = Nothing
Set qdf4 = Nothing
Set qdf5 = Nothing
Set db = Nothing
DoCmd.Close acForm, Me.Name
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.
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
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 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.