Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need assistance with modifying SQL statement (in form)

Posted on 2014-11-20
6
Medium Priority
?
152 Views
Last Modified: 2014-11-20
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
0
Comment
Question by:ExpExchHelp
  • 3
  • 2
6 Comments
 
LVL 12

Accepted Solution

by:
James Elliott earned 2000 total points
ID: 40455233
Maybe this:

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

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40455239
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
 

Author Closing Comment

by:ExpExchHelp
ID: 40455240
James -- thank you... your solution is perfect!
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:ExpExchHelp
ID: 40455245
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40455249
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40455251
np...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

926 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