From a form: How do I plug a value into the name of a query, execute the query and populate a drop down with the result set.

Julie Kurpa
Julie Kurpa used Ask the Experts™
on
The data is stored in this way:  Year, Quarter (1st, 2nd, 3rd, 4th), DatabaseName, tablespace_name, Size(MB)


I have an access database that allows the user to choose the database name (from a drop down),
then (based on the database chosen) choose the "from" date, then the "to" date.  This gives the growth of the data during the selected time period.
It works OK but I'd like to revamp the database.

I've created a table for each database to hold the data for each database. See samples below for databases "FINA" and "PAYR".
 

TABLE:  tbl-FINA_Data

YEAR  QUARTER  APPLICATION  TABLESPACE  SIZE(KB)
2017  1ST      FINA         FINA_DATA   29383
2017  2ND      FINA         FINA_DATA   33228
2018  1ST      FINA         FINA_DATA   38222


TABLE:   tbl-PAYR_Data

YEAR  QUARTER  APPLICATION  TABLESPACE  SIZE(KB)
2017  1ST     PAYR          PAYR_DATA   3892983
2017  2ND     PAYR          PAYR_DATA   4493903
2018  1ST     PAYR          PAYR_DATA   4899393
2018  4TH     PAYR          PAYR_DATA   5300203


I also have a table of the database names:

TABLE: tbl-DB_List

DBCODE  DBDESC
FINA    FINANCIAL
PAYR    PAYROLL

I made a form that populates a drop down box of the Databases from tbl-DB_LIST

When the user selects the Database name form the list, I want to do the following:

1. Obtain the DBCODE (this I can do)
2. Plug the DBCODE into a variable that is used to construct the name of the query.  This query selects distinct Year & Quarter from the appropriate table.

   Example:  If the user selected "PAYR", I want to plug in "PAYR" to end of "qry-YearQuarter_" to have it call query "qry-YearQuarter_PAYR".

3. This distinct query, will populate the "FROM" drop down with all the YEARS & QUARTER available for that DB.
4. When the user selects the "FROM" year/quarter, it will select again from available years/quarters to grab those that follow the "FROM" and populate the "TO" drop down.  


Once those 3 items are obtained, a report be shown.  


BTW:  If the way I'm approaching this is stupid, I accept constructive criticism for alternate approaches.   :)

MY QUESTION IS THIS:  
How do I plug the DBCODE value into a query name, execute that query, and use the result set to populate a drop down list?

I've googled and read what appear to be suggestions for doing this but I just don't understand the information.

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Application Developer
Commented:
You've got a lot of questions there, but let me see if I can answer the first one:  "How do I create and name a query?"  

It appears from your question that you can compose the SQL statement for your query, and create the name you want to give it.  (If that's not right, let me know and I'll explain those processes.)

Below is a Sub I wrote a number of years which accepts a SQL statement, a name for the new query, and Boolean ("True" or "False") values that indicate if a query is to return records (a Select query) or manipulate data (Update, Delete, or Insert), and if the query is to be executed immediately.  

Give this a try, and let us know if/when you hit a glitch.  After you have this piece of the puzzle working, we can look at how to use the query results as the Row Source for a combo box.  :)  

Paul



Public Sub MakePassThruQuery(strSQL As Variant, strQueryNa As String, ReturnRecords As Boolean, Optional DisplayIn As Boolean, Optional ConnectionType As String)
'sample call: against linked SQL database:  MakePassThruQuery("select * from LookupTb", "NewQuery", True, False, "ODBC" )
'              against native Access table:   MakePassThruQuery("select * from LookupTb", "NewQuery", True, False)
'---------------------------------------------------------------------------------------
' Procedure : MakePassThruQuery
' DateTime  : 2/13/2007 09:30
' Author    : PCG
' Purpose   : Accepts SQL string, creates passthru query, optional query opening
' History   : Built on code snagged from MS KnowledgeBase.
'               12/28/08:  replaced syntax ("Set QD = db.CreateQueryDef("qryMatchInvestigatorComments", strSQL")
'               which would not accept Case statement.
'               1/13/09:  created global variables in PublicDeclaration module, built public sub
'               10/12/2017:  changed strSQL to variant to handle long queries
'               12/12/2018:  tightened code, added opportunity to review data type mismatch results
'---------------------------------------------------------------------------------------
Dim strConnectString As String, lngSelectStart As Long, db As Database, qd As QueryDef
On Error GoTo HandleError
Set db = CurrentDb
'set query
Set qd = db.CreateQueryDef(strQueryNa)


'If ConnectionType = "ODBC" Then qd.Connect = DLookup("Column2", "accLookupTb", "DataTypeDe = 'Database' and RowTypeDe = 'Data'  and  Column1 = 'qryDef.Connect'")


'populate query's SQL
qd.SQL = strSQL
'set query properties
qd.ReturnsRecords = ReturnRecords
qd.ODBCTimeout = 0

'if DisplayIn = Yes, then open query:
If DisplayIn Then
   DoCmd.SetWarnings False
   DoCmd.OpenQuery strQueryNa
   DoCmd.SetWarnings True
End If


ExitSub:
DoCmd.Hourglass False
Exit Sub
HandleError:
MsgBox "PassThru Module MakePassThruQuery Error " & Err.Number & " (" & Err.Description & ");  Line " & Erl & vbCrLf & "SQL as passed to function:  " & strSQL
Resume ExitSub
EndSub:    End Sub

Open in new window

Paul Cook-GilesSenior Application Developer

Commented:
On re-reading your question, I have a few of my own.  :)

Is your Access database the front end for/connected to multiple other databases?  If so, what kind of databases are connected?  

Is "DBCODE" the same element as "Application"?
Why have you created multiple tables to hold the data for each database?  It seems that you have the same elements for each (Year, Quarter, Application, Tablespace),
so why not have a single table that holds the data for all the databases?  Like this...

Table:  DatabaseSizeTb
YEAR  QUARTER  APPLICATION  TABLESPACE  SIZE(KB)
2017  1ST      FINA         FINA_DATA   29383
2017  2ND      FINA         FINA_DATA   33228
2018  1ST      FINA         FINA_DATA   38222
2017  1ST     PAYR          PAYR_DATA   3892983
2017  2ND     PAYR          PAYR_DATA   4493903
2018  1ST     PAYR          PAYR_DATA   4899393
2018  4TH     PAYR          PAYR_DATA   5300203

Do you need a table that tells you that FINA = Financial, and PAYR = Payroll?  If you just need a list of the applications, you can get it with
Select distinct (Application) from DatabaseSizeTb...
Julie KurpaSr. Systems Programmer

Author

Commented:
Thanks Paul.

I'm pecking at this.  

In the VBA code for "AfterUpdate" of my drop down (called DBCODE),  I have put together the SQL and assigned it to a variable "strSQL".
I've also assigned a value to strQueryNa.  

Now how do I get it to run the code you supplied?  I'm pasting what I have so far below:

Private Sub DBCODE_AfterUpdate()

strSQL = "SELECT  DISTINCT [tbl-" & Me.DBCODE.Column(1) & "_Data].Year, [tbl-" & Me.DBCODE.Column(1) & "_Data].Quarter FROM [tbl-" & Me.DBCODE.Column(1) & "_Data]ORDER BY [tbl-" & Me.DBCODE.Column(1) & "_Data].Year, [tbl-" & Me.DBCODE.Column(1) & "_Data].Quarter;"

strQueryNa = "qry-YearQuarter_" & Me.DBCODE.Column(1)

End Sub
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Julie KurpaSr. Systems Programmer

Author

Commented:
To try and answer your questions:


This Access database is not a front end for anything.  All data is stored in the Access Database.  I import fresh tablespace sizes from csv files created every quarter by my application databases.


Yup DBCODE is the same as Application.  I try to be consistent with things but you can see I don't succeed.  :)

I actually have this database already made with all data in one table.  It was something I did a few years ago when I was first learning MS Access and I just don't like the way I put it all together and made the reports.  Am trying to make it better.  But I could be making it worse. :(
Paul Cook-GilesSenior Application Developer

Commented:
Julie, we're all learning, so don't stress over the process of rebuilding/changing/improving.  :) I think my advice would be to take the code you have in the OnExit event of DBCODE, and put it behind a button, so it doesn't run every time you leave that field.  

Whether you leave it where it is, or put it behind a button, you would add this line after your existing code:

MakePassThruQuery strsql, strQueryNa, True, True

That will pass the SQL text and the name for the query to the Sub, along with the information that the query is expected to return records, and to run it immediately.
If everything works as expected, the new query strQueryNa will open and display the results when the code is executed.  :)

You might consider having the CSV data from all your application databases go into a single table, and then have the DBCODE as part of the criteria statement... something like this:

strSQL = "SELECT  DISTINCT [Year], [Quarter] FROM DatabaseSizeTb where Application = ' "  & Me.DBCODE.Column(1) & " ' ORDER BY [Year], [Quarter]"

Note that [Year] and [Quarter] wouldn't have to be prefixed by a concatenated table name if you're pulling all your data from a single table...
Julie KurpaSr. Systems Programmer

Author

Commented:
This is kewl.  :)

The query is being created and the correct data is being displayed!  

The FromDate drop down is also populating with the output from the passthru query!   happy happy!

I think now I can get ToDate drop down to populated based on the FromDate now.  

I decided not to make the passthru query name unique to the DBCODE but generic.  It's now just named "qry-YearQuarter".

Can you tell me how to check if the Passthru query already exists and, if so, delete it prior to creating it?
Paul Cook-GilesSenior Application Developer

Commented:
Yay!  And yes, I can.  You'll need to create two new functions:

Public Function IsQueryOpen(strName As String) As Boolean
'accepts a query name;  if the query is open, returns True
    IsQueryOpen = False
    IsQueryOpen = (SysCmd(acSysCmdGetObjectState, acQuery, strName) <> 0)
End Function

Open in new window




and
Public Function ObjectExists(strObjectName As String) As Boolean
'accepts an object's name;  if the object exists, returns True
     ObjectExists = False
     If DLookup("ID", "MSysObjects", "Name = '" & strObjectName & "'") <> 0 Then ObjectExists = True
End Function

Open in new window



Then add these two lines to the sub before the Set DB line:

   If IsQueryOpen(strQueryNa) Then DoCmd.Close acQuery, strQueryNa
   If ObjectExists(strQueryNa) Then db.QueryDefs.Delete strQueryNa

Open in new window


Let me know how that works for you.
Julie KurpaSr. Systems Programmer

Author

Commented:
I had a little trouble with it.  Once I moved those two lines AFTER the set DB Line, it worked.  :)

There was one other thing I had to change in the functions you gave me.  The type of StrQueryNa wouldn't work as "string" but did work as "Variant".  

Changed from this:
Public Sub MakePassThruQuery(strSQL As Variant, strQueryNa As String, ReturnRecords As Boolean, Optional DisplayIn As Boolean, Optional ConnectionType As String)

To this and it worked.  Not sure why:
Public Sub MakePassThruQuery(strSQL As Variant, strQueryNa As Variant, ReturnRecords As Boolean, Optional DisplayIn As Boolean, Optional ConnectionType As String)
Paul Cook-GilesSenior Application Developer

Commented:
>... moved those two lines AFTER the s...

Duh.  Sorry about that.  

>...strQueryNa As Variant, ...

Huh.  I don't know why it wouldn't be happy with the String data type.. but then, I don't remember why I set strSQL as Variant.  At any rate, I'm glad it works for you!  

P
Julie KurpaSr. Systems Programmer

Author

Commented:
Wonderful help and thank you for the code!  I will put this in my bag O' tricks for future projects!
Paul Cook-GilesSenior Application Developer

Commented:
Glad we could help!  :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial