QDF Question..

[tEkHEd]

hey..

I have some views set up in SQL 2000 that I have linked into Access XP.

The views will list all of the available data from various tables...

I currently use a dynamic query to pull info from the view into a report, by means of a combo box.

I want to add another combo box to my form, which will allow a user to select a "report type", depending upon what selection is made here, directly relates to the dynamic query that is passed to the report, which pulls the relevant data from the view.

I am able to do this if I have only 1 type of report that needs to be run by using the

For Each qdf In.... blah
Next qdf syntax..

If I try to include a 2nd/3rd selection I am unable to produce any report and generally get an error:

"Object Variable or With Block Variable Not Set"

Can anyone help out??

Here is the code that I am trying in various different formats:

Private Sub cmdRunRpt_Click()
 On Error GoTo Err_cmdRunRpt_Click
   
    Dim dbs As Database, qdf As QueryDef
    Dim strSQLQry1 As String
    Dim strSQLQry2 As String


    ' Return reference to current database.
    Set dbs = CurrentDb
        
    ' Refresh QueryDefs collection.
    dbs.QueryDefs.Refresh
    
    
    strSQLQry1 = "SELECT * FROM vSWInstances WHERE Product = ' " & Me.cboSWProd & " '"
                    '& "AND Version LIKE '%" & Me.cboSWVer & "%' "
                    
    strSQLQry2 = "SELECT * FROM vSWInstallBaseByProductCount WHERE Product LIKE '%" & Me.cboSWProd & "%' "
                    

    If Me.cboRptType = "Installs by Business Unit" Then
        For Each qdf In dbs.QueryDefs
            qdf.Name = "qrySWInst"
            qdf.SQL = strSQLQry1
        Next qdf
            Else
                If Me.cboRptType = "Installs & Purchases By Business Unit" Then
                    For Each qdf In dbs.QueryDefs
                        qdf.Name = "qryPurchVsInstByBU"
                        qdf.SQL = strSQLQry2
                    Next qdf
                End If
    End If

MsgBox qdf.sql 'this is just for errror handling, would b replaced by DoCmd.Openreport [Forms]![frmSWSelect]![cboRptTyp], acViewPreview

Thanks in adv for any help

 

[RobCPA]

The error "Object Variable ... Not Set" means that the query object hasn't been set. In other words, you need to include a statement like:

set qdf = db.querydefs("qrySWInst&quot

After the query object has been set, you can then retrieve it's properties through the qdf variable.


Rob