Solved

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

Posted on 2014-11-12
5
671 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unknown computers in my LAN 12 97
Handle Apostrophes in SQL Parameter 16 66
Best way to get data into a database 12 54
Ms Access 2010 Setup (Executable file) 4 66
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

734 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