Solved

MS-Access -- how to create a dynamic query based on listbox value selection

Posted on 2014-11-12
5
707 Views
Last Modified: 2014-11-15
Hello:

I need some assistance with creating dynamic queries.   Allow me to provide some background on the MS-Access (test) database first (see attachment).

Table "Table1":
- It includes two fields: [2-DigitNumber] and [3-DigitNumber].
- For testing purposes, I've added four arbitrary records (2-digit numbers and four 3-digit numbers), respectively.

Query "GenericQuery":
- This query could be deleted afterwards.
- I've merely used the query wizard to generate the SQL code such as: "SELECT Avg(Table1.[2-DigitNumber]) AS Average FROM Table1;"

Form "frmLogin":
- It includes an unbound list box with the following VBA-driven row source (which have not been tied to actions yet):
-- "Run Average of 2-Digit Numbers"
-- "Run Average of 3-Digit Numbers"


My goal (again, testing purposes only right now):
- User opens the database (frmLogin)
- If user selects the list box option "Run Average of 3-Digit Numbers", then a report (or query) opens.  
- The data source is dynamically generated based on the list box's value section of either "Run Average of 2-Digit Numbers" or "Run Average of 3-Digit Numbers".  
- Thus, SQL code for the report's data source is either "SELECT Avg(Table1.[2-DigitNumber]) AS Average FROM Table1;" or "SELECT Avg(Table1.[3-DigitNumber]) AS Average FROM Table1;"

Given that "Run Average of 2-Digit Numbers" is NOT the field name, I somehow have to cross-reference the string to field, e.g., [3-DigitNumber].  
- If that's accomplished, then the new SQL brings up a report that shows "552.5" (the average of the four 3-digit records) vs. "18.5" (the average of the four 2-digit records).

Again, this simple example (using averages) is for creating a general framework first.  Once that works, I will expand the framework in support of the actual database.

Any thoughts as to how this can be accomplished?   Please see attached database as a starting point.

Thanks,
EEH
Testing.zip
0
Comment
Question by:ExpExchHelp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 40439070
Looks like you re on the right track. In the attached, a second field was added to the query for the 2-digit number average. Reports were then created based on each field. Your existing code was tweaked to get the reports to open upon selection within the form:

Private Sub ListBoxTest_AfterUpdate()
       
    If Me.ListBoxTest.Value = "Run Average of 2-Digit Numbers" Then
       On Error Resume Next
       DoCmd.OpenReport "rpt2Digit", acViewPreview
       DoCmd.Maximize
       DoCmd.RunCommand acCmdZoom100
    End If
       
    If Me.ListBoxTest.Value = "Run Average of 3-Digit Numbers" Then
       On Error Resume Next
       DoCmd.OpenReport "rpt3Digit", acViewPreview
       DoCmd.Maximize
       DoCmd.RunCommand acCmdZoom100
    End If

End Sub

Flyster
Testing.accdb
0
 

Author Comment

by:ExpExchHelp
ID: 40439085
Flyster:

Thank you so much for your response... 'very promising approach.

Is there any chance that I can utilize a single report?   In the actual database, I may have 50 different scenarios that are tested... thus, I don't want to create 50 reports.   So, basically, I'd like to have only a single query and a single report... all else will be driven by the value selection of the user.

Doable?
EEH
0
 

Author Comment

by:ExpExchHelp
ID: 40439086
... oh, the same goes for the function "If Me.ListBoxTest.Value ="... ideally, I won't have to create, e.g., 50 of those IF statements.

Thank you in advance,
EEH
0
 

Author Closing Comment

by:ExpExchHelp
ID: 40441807
Not exactly what I was looking for (i.e,., it's not fully dynamic) but an acceptable solution.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 40444989
Thanks. I'm still working on a more "flexible" solution. Just having a little problem finding the time to work on it.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question