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.
Julie KurpaSr. Systems ProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
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.  :)  


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

DoCmd.Hourglass False
Exit Sub
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul Cook-GilesSenior Application DeveloperCommented:
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...
Julie KurpaSr. Systems ProgrammerAuthor 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 Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Julie KurpaSr. Systems ProgrammerAuthor 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 DeveloperCommented:
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 ProgrammerAuthor 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 DeveloperCommented:
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.
Julie KurpaSr. Systems ProgrammerAuthor 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 DeveloperCommented:
>... 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!  

Julie KurpaSr. Systems ProgrammerAuthor 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 DeveloperCommented:
Glad we could help!  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.