Solved

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

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

828 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