asked on

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

ASKER CERTIFIED SOLUTION

James Elliott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

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

ExpExchHelp

ASKER

James -- thank you... your solution is perfect!

ExpExchHelp

ASKER

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.

Rey Obrero (Capricorn1)

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

Rey Obrero (Capricorn1)

np...