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?
 
James ElliottConnect With a Mentor Managing DirectorCommented:
Maybe this:

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

Open in new window

0
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
All Courses

From novice to tech pro — start learning today.