Greetings,
I searched through postings and only came up with one match. However, the match did not have a solution.
Here’s my problem.
I have wrote VB code that takes a value from an input box and runs a case select statement. Based on the value, the ‘Where’ clause in the SQL code is altered to allow different Prompts for the same provider. The code works great however, when I refresh the report a second time, the ‘don’t regenerate’ checkbox is checked. This causes the sql code to keep the previous variable. I can’t seem to find a way in vb to tell the provider to regenerate prior to running the case select statement.
Any help would be great. Please keep in mind I’m a DBA, not a VB coder, so if the code is not perfect, please be kind. Also, it appears that tabs don’t show in this forum window, so some of the documented remarks may run to the end of the SQL Code. Sorry.
Here is the code I’m using.
Sub dpPrompts()
'**************************************************************************
' Code created 10/23/2003, Michael Mayhew, Daughter Business Solutions. *
' This Module was created for the end-user as a way to choose between *
' prompts without having all 'OR' sql prompts displayed on-screen. *
'**************************************************************************
Dim dpMyDataProvider As DataProvider 'Used for BO data provider
Dim iA As Integer 'Used for loop counter.
Dim iX As Integer 'Used to hold user input response.
Dim iY As Integer 'Used to hold the position of the 'Group By' in each SQL Code
Dim strCheckFor As String 'Used to hold the comparison string 'Group By'.
Dim strDPSQL As String 'Used to hold the DP's SQL Code
Dim strDPSQLbegin As String 'Used to hold SQL Code prior to 'Group By'.
Dim strDPSQLend As String 'Used to hold SQL Code from 'Group By' to end.
Dim strADDSQL As String 'Used to append the data providers SQL Code.
Dim strDPnewSQL As String 'Used to hold the new SQL Code
'**************************************************************************
'User is prompt for 1=Household_Key 2=LastName or 3=SSN
iX = InputBox("Enter 1=Household Key or 2=Last Name or 3=SSN?", "Please Make a Selection", 1)
'Sets the dataprovider to dataprovider 1 in document.
Set dpMyDataProvider = ThisDocument.DataProviders.Item(1)
'strDPSQL = "" 'Resets string value.
strDPSQL = dpMyDataProvider.SQL 'Captures the SQL code in data provider.
strCheckFor = "Group By" 'Used as the search string against the SQL Code.
iY = InStr(1, strDPSQL, strCheckFor, vbTextCompare) 'Returns the position of the Group By with in the SQL Code.
iY = iY - 1 'This is the insertion point for adding to the 'Where' clause.
strDPSQLbegin = Mid(strDPSQL, 1, iY) 'Beginning Code
strDPSQLend = Mid(strDPSQL, iY + 1) 'Ending Code
'Case Statement based on users response to input box, the system chooses which SQL Code to append to.
Select Case iX
Case 1
strADDSQL = "AND(ADM.HOUSEHOLD.HOUSEHOLD_KEY )= @variable('Enter Household Key:')" 'This is the text to be added in the 'Where' Clause
'New Code for data provider to process.
strDPnewSQL = strDPSQLbegin & vbCrLf & strADDSQL & vbCrLf & strDPSQLend
ThisDocument.DataProviders.Item(1).SQL = strDPnewSQL
Case 2
strADDSQL = "AND ADM.HOUSEHOLD.INDIVIDUAL1_NAME_LAST_2 LIKE @variable('Enter Last Name')" 'This is the text to be added in the 'Where' Clause
'New Code for data provider to process.
strDPnewSQL = strDPSQLbegin & vbCrLf & strADDSQL & vbCrLf & strDPSQLend
ThisDocument.DataProviders.Item(1).SQL = strDPnewSQL
Case 3
strADDSQL = "AND ADM.HOUSEHOLD.INDIVIDUAL1_SSN_TIN = @variable('Enter SSN?')" 'This is the text to be added in the 'Where' Clause
'New Code for data provider to process.
strDPnewSQL = strDPSQLbegin & vbCrLf & strADDSQL & vbCrLf & strDPSQLend
ThisDocument.DataProviders.Item(1).SQL = strDPnewSQL
Case Else
Dim UserError
UserError = MsgBox("You must enter 1, 2 or 3 as your selection!", vbCritical, "IMPORTANT")
End Select
ThisDocument.DataProviders.Item(2).Refresh
End SubMayhewM 
(BOB member since 2003-10-22)
We discusssed that just a bit ago in