Need assistance with modifying SQL statement (in form)

Experts:

I need some assistance with tweaking a query (generated which is generated via a form).

Please see attached database (zipped).  In contains the following objects:

1. Table1
2. Query1
3. frmLogin

Process upon opening:
1. Open form "frmLogin"
2. Select any of the three values from the listbox
3. This will open Query1 and display the associated responses.

Here's what I need some help with:
- Tweak the SQL (in frmLogin) so that the selected value from the listbox (e.g., "Display 2-digit numbers)
is added as records (in new column [Selection]).
- Attached JPG "Query -- Current and New" illustrates the current and new query views.

Any idea as to how the SQL can be modified to achieve the new query display?

Thanks,
EEH
Query.zip
Query----Current-and-New.JPG
ExpExchHelpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
Maybe this:

sql = "SELECT [" & strGenericField & "] as X_Digit, '" & Me.ListBoxTest.Value & "' as Selection FROM Table1"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
Public Sub ListBoxTest_AfterUpdate()

On Error Resume Next
       Dim qd As DAO.QueryDef, db As DAO.Database, sql As String
       Dim strGenericField As String
       Set db = CurrentDb
       Set qd = db.QueryDefs("Query1")
       
       Select Case Me.ListBoxTest
            Case "Display 2-digit numbers": strGenericField = "2-DigitNumber"
            Case "Display 3-digit numbers": strGenericField = "3-DigitNumber"
            Case "Display 4-digit numbers": strGenericField = "4-DigitNumber"
       End Select
       
'       TempVars.Add "GenericField", Me.ListBoxTest.Value  <<< REVISED see next line
       TempVars.Add "GenericField", strGenericField
       sql = "Select ([" & strGenericField & "]) AS X_Digit FROM Table1"
       qd.sql = sql
       
       DoCmd.OpenQuery "Query1"

End Sub
0
ExpExchHelpAuthor Commented:
James -- thank you... your solution is perfect!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ExpExchHelpAuthor Commented:
Rey -- I just noticed your response.   I already had tested James' solution before I saw your feedback.

Thank you for chiming in... points already had been awarded though.  

Again, I thank you both for your assistance.
0
Rey Obrero (Capricorn1)Commented:
correction, copied the wrong codes

Public Sub ListBoxTest_AfterUpdate()

On Error Resume Next
       Dim qd As DAO.QueryDef, db As DAO.Database, sql As String
       Dim strGenericField As String
       Set db = CurrentDb
       Set qd = db.QueryDefs("Query1")
       
       Select Case Me.ListBoxTest
            Case "Display 2-digit numbers": strGenericField = "2-DigitNumber"
            Case "Display 3-digit numbers": strGenericField = "3-DigitNumber"
            Case "Display 4-digit numbers": strGenericField = "4-DigitNumber"
       End Select
       
'       TempVars.Add "GenericField", Me.ListBoxTest.Value
       TempVars.Add "GenericField", strGenericField
'       sql = "Select ([" & strGenericField & "]) AS X_Digit FROM Table1"   <<< REVISED see next line
        sql = "SELECT [" & strGenericField & "] as X_Digit, '" & Me.ListBoxTest.Value & "' as Selection FROM Table1"
       qd.sql = sql
       
       DoCmd.OpenQuery "Query1"

End Sub
0
Rey Obrero (Capricorn1)Commented:
np...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.