• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

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

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
ExpExchHelp
Asked:
ExpExchHelp
  • 3
  • 2
1 Solution
 
FlysterCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
... 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
 
ExpExchHelpAuthor Commented:
Not exactly what I was looking for (i.e,., it's not fully dynamic) but an acceptable solution.
0
 
FlysterCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now