Solved

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

770 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