Avatar of Julie Kurpa
Julie Kurpa
Flag 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

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.
Microsoft Access

Avatar of undefined
Last Comment
Paul Cook-Giles

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Paul Cook-Giles

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Paul Cook-Giles

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 Kurpa

ASKER
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
Julie Kurpa

ASKER
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. :(
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Paul Cook-Giles

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 Kurpa

ASKER
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-Giles

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Julie Kurpa

ASKER
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-Giles

>... 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 Kurpa

ASKER
Wonderful help and thank you for the code!  I will put this in my bag O' tricks for future projects!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Paul Cook-Giles

Glad we could help!  :)