ChangeSQL for 2+ qrys
Posted on 2007-04-06
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?
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"
strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
Forms![frmSys_RunSPs]![cmbRptMonth] & "'")
DoCmd.OpenReport strRpt, acPreview
DoCmd.Close acForm, "frmSys_RunSPs", acSaveNo
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