Link to home
Start Free TrialLog in
Avatar of Julie Kurpa
Julie KurpaFlag for United States of America

asked on

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.

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

2017  1ST      FINA         FINA_DATA   29383
2017  2ND      FINA         FINA_DATA   33228
2018  1ST      FINA         FINA_DATA   38222

TABLE:   tbl-PAYR_Data

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


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.   :)

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.
Avatar of Paul Cook-Giles
Paul Cook-Giles
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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...
Avatar of Julie Kurpa


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
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. :(
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...
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?
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

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.
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)
>... 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!  

Wonderful help and thank you for the code!  I will put this in my bag O' tricks for future projects!
Glad we could help!  :)