asked on

ChangeSQL for 2+ qrys

Design:
SP on SQL 2k5
PT qrys in Access executing SP
User selects report from main menu
a form appears asking for parameter
user selects a month from the drop down list
cmdButton changes sql in pt qry accordingly

This code sounded like a great design until I got to the report that has three queries as a data source. (Detail and two charts)

Anyone have an idea of how to restructure this code without junking it up?

adria

************************
Private Sub cmdOpenRpt_Click()
Dim strSQL As String
Dim strOldSQL As String
Dim strQry As String
Dim strRpt As String

Select Case txtRptName
Case Is = "ISP Orders"
strQry = "qryOrders_ISP"
strSQL = "execute upOrders_ISP"
strRpt = "rptOrders_ISP"
Case Is = "Orders By Month"
strQry = "qryOrders_ByMonth"
strSQL = "execute upOrders_ByMonth"
strRpt = "rptOrders_Lookup"
Case Else
Exit Sub
End Select
strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
Forms![frmSys_RunSPs]![cmbRptMonth] & "'")
DoCmd.OpenReport strRpt, acPreview
DoCmd.Close acForm, "frmSys_RunSPs", acSaveNo

End Sub

Function ChangeSQL(strQry As String, strSQL As String) As String

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(strQry)
qd.SQL = strSQL
ChangeSQL = qd.SQL
Set qd = Nothing
Set db = Nothing
End Function

ASKER CERTIFIED SOLUTION

stevbe

membership

This solution is only available to members.

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

Start Free Trial

adraughn

ASKER

this particular report is based off of three access queries that all require parameters. so in changing them to SQL, they will all need to be SPs.

I'm thinking right now that option #1 might be best, but I was hoping for a better solution.

With this migration, I will eventually need to change multiple reports that have more than one query as a data source.

Thanks for the response. I'll try one and two and see how it does.

adria

stevbe

here is a chnage to ChangeSQL to handle passing multiple queries (separated by | character as it will likely never be used in a SQL statement). You don;t actually use the return value from the function so I didn't bother returning anything

Function ChangeSQL(strQry As String, strSQL As String) As String

Dim astrQry As Variant
Dim astrSQL As Variant
Dim intCount As Integer

astrQry = Split(strQry, "|")
astrSQL = Split(strSQL, "|")

For intCount = 0 To UBound(astrQry)
CurrentDb.QueryDefs(astrQry(intCount)).SQL = astrSQL(intCount)
Next

End Function