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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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. :(
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/improv
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...
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...
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?
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:
and
Then add these two lines to the sub before the Set DB line:
Let me know how that works for you.
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 Functionand
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 FunctionThen 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 strQueryNaLet me know how that works for you.
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)
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!
P
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
ASKER
Wonderful help and thank you for the code! I will put this in my bag O' tricks for future projects!
Glad we could help! :)
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...