Change Query Definition for a Report... 1

[PNC]

I open a form and depending on the command button that I click, the values for the report and query name are placed in 2 text fields.

I select a value from the combo box, and modify my where clause depending on the value selected, then I click on the command button to open the report.

Problem: on the first try the query is not change and/or the report does not load with the modified query!!! But when I close the report, and click the command button again, it works!?!

When I put breakpoints, everything works fine...

    Dim stDocName As String
    Dim qryDef As QueryDef
    Dim Q As String
    Dim Rel As String
    Dim QryName As String
    
    stDocName = Me!RptName.Value
    QryName = Me!QryName.Value
        
    Me.Combo_Release.SetFocus
    
    'Specify the release name
    If IsNull(Me.Combo_Release) = True Then
        Rel = "dbo.Incident.ReleaseName or dbo.Incident.ReleaseName is null)"
    Else
        Rel = "'" & Me.Combo_Release.Text & "')"
    End If
        
        Set qryDef = CurrentDb.QueryDefs(QryName)
        Q = qryDef.SQL
    
    'Modify query
    qryDef.SQL = Q & " And (dbo.Incident.ReleaseName =" & Rel
    

    DoCmd.OpenReport stDocName, acPreview
    DoCmd.Maximize
    
    ' Reset query to default
    qryDef.SQL = Q

Any suggestions?

Thanks,
PNC.

 

[VBslammer]

Why bother with all that when you can append criteria to the where clause in the "[WhereCondition]" argument:
[tt]
DoCmd.OpenReport stDocName, acPreview, , "dbo.Incident.ReleaseName=" & Rel
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBslammer]

Q = qryDef.SQL

Doesn't the SQL in a saved QueryDef always terminate with a semi-colon? It seems to me that the Query editor always appends it when you save the query, so if you try to append additional criteria you'd have to remove the semi-colon first using:

Q = Left(qryDef.SQL, InStrRev(qryDef.SQL, ";") - 1)

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PNC]

Thanks a lot VBSlammer,

I'm using this solution:

DoCmd.OpenReport stDocName, acPreview, , "ReleaseName=" & Rel

It works very well, but I don't know how to use it with a sub-report???

Concerning your second post, I didn't need to remove the semi-colon because I didn't put it at the end of saved the query... In order for it to work, I should probably refresh/requery/execute the query with the new definition before opening the report, but I didn't know how do it.

Thx,
PNC.

 

[VBslammer]

You could try to refresh the collection:
[tt]
Dim db As DAO.Database
Dim qdf As QueryDef
Dim sql As String
Dim rel As String

Rel = "'" & Me.Combo_Release.Text & "')"

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryRelease")

sql = qryDef.SQL
qryDef.SQL = sql & " And (ReleaseName =" & Rel

[blue]db.QueryDefs.Refresh[/blue]

[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PNC]

Thanks again VBSlammer, it's working.

Any idea on how to use the WHERE clause option of DoCmd.OpenReport for a sub-report???

 

[VBslammer]

The subreport is normally linked to a field on the main report so each instance of the subreport displays different information. You might try using a reference to the main form in the RecordSource SQL statement:
[tt]
RecordSource: = "SELECT * FROM tblName WHERE [NumberField] > " & Nz(Forms!MainForm!txtNumber, 0)
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PNC]

So I guess I have to do:

DoCmd.OpenReport stDocName, acPreview, , "Field= " & X

Where [ FIELD ] is the link between the main report and the sub-report.

I wanted to bypass the main report, but I guess it's not possible... thanks again VBSlammer... here's another star for you.

- PNC.