Modifying SQL through VBA

[blott]

I have a VBA script that I use to modify the SQL of a dataprovider to add an ORDER by clause before the refresh. I am also adding several conditions through "condition.add". I do this through VBA since it will change each time you refresh based on user input parameters (A fix due to bad warehouse design).

The problem I have is that when the "do not generate SQl" checkbox is unmarked the codes works great. After it runs BO then checks the checkbox automatically, I assume since I am modifying the SQL. The second time I run the script the conditions change in the Query screen but when you look at the SQL the conditons have not changed.

Is there a way to programmically change the "do not generate SQL befor running" checkbox? Or is there another workaround I can use.

This is the code I am currently using. Its not very clean since I have been trying anything and everything. SOme of the wiered stuff is because this is connected to a Tandem.

dp_MainClaimsData.Load

' Remove all Conditions

Do While dp_MainClaimsData.Queries.Item(1).Conditions.Count > 0
dp_MainClaimsData.Queries.Item(1).Conditions.Remove (1)
Loop
dp_MainClaimsData.Unload
dp_MainClaimsData.Load


' Add Conditions
Call dp_MainClaimsData.Queries.Item(1).Conditions.Add("Claims", "Claim Status Dt", "Between", "{d '" & StatusStartDate & "'}", , "{d '" & StatusEndDate & "'}")
Call dp_MainClaimsData.Queries.Item(1).Conditions.Add("Dtl ALL Edit/Audits Info", "Dtl ALL Edit/Audits Nbr", "Equal to", "00188")
Call dp_MainClaimsData.Queries.Item(1).Conditions.Add("Hdr ALL Edit/Audits Info", "Hdr ALL Edit/Audits Nbr", "Equal to", "00027")

dp_MainClaimsData.Unload

'Add the order by clause
dp_MainClaimsData.Load

crlf = Chr(13) & Chr(10)
If InStr(dp_MainClaimsData.sql, "ORDER") <> 0 Then
dp_MainClaimsData.sql = Mid(dp_MainClaimsData.sql, 1, InStr(RTrim(dp_MainClaimsData.sql), "ORDER")) & crlf & "ORDER BY" & crlf & " AP_PADMIN.A_FCT_HDR.CHSBP_BILL_PROV_TPI , " & crlf & " AP_PADMIN.A_FCT_HDR.CHSBP_BL_PRAC_LOC_CD," & crlf & " AP_PADMIN.A_FCT_DTL.CH_PROGRAM_CODE," & crlf & " Column__23," & crlf & " AP_PADMIN.A_FCT_DTL.CD_SEQUENCE_NUMBER "
Else
dp_MainClaimsData.sql = RTrim(dp_MainClaimsData.sql) & crlf & "ORDER BY" & crlf & " AP_PADMIN.A_FCT_HDR.CHSBP_BILL_PROV_TPI , " & crlf & " AP_PADMIN.A_FCT_HDR.CHSBP_BL_PRAC_LOC_CD," & crlf & " AP_PADMIN.A_FCT_DTL.CH_PROGRAM_CODE," & crlf & " Column__23," & crlf & " AP_PADMIN.A_FCT_DTL.CD_SEQUENCE_NUMBER "
End If

dp_MainClaimsData.Unload
dp_MainClaimsData.Refresh