Cannot Requery a recordset

[natsirtm]

A bit of background.
Trying to automate production of a report with subreport.
I have a single date parameter selected from a form for the report's queries.
I want to use the same date as the parameter for the subreports' query.

The report queries to which the parameter applies are action queries, which I run with the Execute command. The subreport query is a select query, so I need to try to requery the recordset as just setting the recordset after setting the parameter doesn't seem to apply them.

On this page I found some notes on the requery method.
http://www.microsoft.com/technet/prodtechnol/office/office2000/solution/part3/ch16.mspx?mfr=true

Specifically this

When you use the Requery method with DAO, you must first determine if the Recordset object supports the Requery method by checking the value of the Restartable property of the Recordset object. If the value is True, you can refresh the Recordset object's contents by using the Requery method. If the Recordset object doesn't support the Requery method, you must open the Recordset object again with the Open method.

I have tested the restartable property of my recordset and it returns true, yet when I try to use the requery method;
either

rst.Requery

or

With rst
.Requery
End With

I get an invalid argument error.

Run-time error '3001':

Invalid Argument

Any thoughts?

 

[Banana]

If you step through the code, where does error trip? I want to make sure if the requery line is really tripping up the error or not.

Furthermore, it would help to see the routine where this runs to better understand why requery isn't working.

 

[natsirtm]

Here's the code.

Private Sub runrpt_day_Click()

' Process date
Dim qryProcessDate As String, qryProcessDateTXT As String
If Not IsNull(Me.qryProcessDate) Then
    qryProcessDate = Me.qryProcessDate
Else
    MsgBox ("Please enter a process date")
End If
qryProcessDateTXT = Replace(qryProcessDate, "/", "-")

Dim i As Integer
i = 1

For i = 1 To 4

    Dim qdf1 As QueryDef, qdf2 As QueryDef, qdf3 As QueryDef, qdf4 As QueryDef, qdfSC As QueryDef
    Dim rstSC As DAO.Recordset
    Dim qryTTC As Byte
    qryTTC = i
    
    Set qdf1 = CurrentDb.QueryDefs("WFMAging_REJ_Day_MT")
    Set qdf2 = CurrentDb.QueryDefs("WFMAging_REF_Day_MT")
    Set qdf3 = CurrentDb.QueryDefs("WFMAging_HLD_Day_MT")
    Set qdf4 = CurrentDb.QueryDefs("WFMAging_CMP_Day_MT")
    Set qdfSC = CurrentDb.QueryDefs("00ChkSanity")
    
    qdf1.Parameters(0) = qryProcessDate
    qdf1.Parameters(1) = qryTTC
    
    qdf2.Parameters(0) = qryProcessDate
    qdf2.Parameters(1) = qryTTC
    
    qdf3.Parameters(0) = qryProcessDate
    qdf3.Parameters(1) = qryTTC
    
    qdf4.Parameters(0) = qryProcessDate
    qdf4.Parameters(1) = qryTTC
    
    qdfSC.Parameters(0) = qryProcessDate
    
    DoCmd.DeleteObject acTable, "WFMAging_REJ"
    DoCmd.DeleteObject acTable, "WFMAging_REF"
    DoCmd.DeleteObject acTable, "WFMAging_HLD"
    DoCmd.DeleteObject acTable, "WFMAging_CMP"
    
    qdf1.Execute
    qdf2.Execute
    qdf3.Execute
    qdf4.Execute
    Set rstSC = qdfSC.OpenRecordset(dbOpenDynaset)
'    rstSC.Requery
    
    With rstSC
        .Requery
    End With
    
    ' Publish report
    Dim stDocName As String
    stDocName = "WFMAging_ALL_Day_SUMMARY"
    DoCmd.OutputTo acOutputReport, stDocName, acFormatRTF, _
        "C:\CSSRMDS\Cindy\AutoReports\WFMAging_ALL_Day_SUMMARY_TTC" _
        & i & " (" & qryProcessDateTXT & ").rtf", False
    
    'Close all objects
    qdf1.Close
    qdf2.Close
    qdf3.Close
    qdf4.Close
    qdfSC.Close
    rstSC.Close
    
    Set qdf1 = Nothing
    Set qdf2 = Nothing
    Set qdf3 = Nothing
    Set qdf4 = Nothing
    Set qdfSC = Nothing
    Set rstSC = Nothing

Next i

End Sub

I will try stepping through now.

 

[natsirtm]

I can't seem to step-through this procedure, it is called from a button click - would that affect stepping-through?

 

[Banana]

Any code can be stepped through.

You just need to insert a breakpoint (click on the gray column immediately adjacent to the window where you type in code and a red dot will appear. Once it's up, you then go back to view mode and run the form and click the button, then it'll bring you to the breakpoint and you can then step through.

 

[natsirtm]

Error is tripping on the requery method, whether using the with rst or not.

 

[natsirtm]

The results from the query are not actually displayed in the form, this form just opens a report. I want the query the subform is based on to use the same date parameter as the query for the main form. Perhaps requery won't work since it's not actually affecting a form??

 

[Banana]

This is your problem:

Set rstSC = qdfSC.OpenRecordset(dbOpenDynaset)

You need to specify a table or query.

Set rstSC = qdfSC.OpenRecordset([b]"YourTableNameHere"[/b], dbOpenDynaset)

 

[natsirtm]

This is your problem:

Set rstSC = qdfSC.OpenRecordset(dbOpenDynaset)

You need to specify a table or query.

Set rstSC = qdfSC.OpenRecordset([b]"YourTableNameHere"[/b], dbOpenDynaset)

Does this not set the query?

Set qdfSC = CurrentDb.QueryDefs("00ChkSanity")

 

[boblarson]

Does this not set the query?

Set qdfSC = CurrentDb.QueryDefs("00ChkSanity")

It doesn't assign it to the recordset, so the current recordset doesn't have anything assigned. That's what Banana was telling you.

 

[natsirtm]

Not that I doubt you are correct, but could you maybe explain a bit more?

I set a querydef, then use the openrecordset method on that querydef, why do I need to assign a query again?

That change has also given me a "Data type conversion error (3421)"

 

[boblarson]

wrong syntax (at least that I know of - DAO is not my specialty, ADO is mine):

Try this instead:

rstSC.OpenRecordset(qdfSC, dbOpenDynaset)

 

[natsirtm]

That line stays red, says it is expecting an =

I've tried fixing it to;

rstSC.OpenRecordset qdfSC, dbOpenDynaset

or

rstSC.OpenRecordset "qdfSC", dbOpenDynaset

and am getting an "Object variable or With block variable not set" error.

 

[boblarson]

Instead of using the querydef object why can't you just open the query directly with the recordset?

 

[natsirtm]

It was the only way I found to apply a the parameter to the query.

 

[Banana]

I've never used a Querydef, and I usually avoid trying to do several things in one line.

Here's my usual code:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = Currentdb()

Set rst = db.OpenRecordSet("QueryName", dbOpenDynaset)

As for your question why didn't your code work-

What you did was set a querydef to a query you stored. This did not assign the query to the open recordset where VBA can run through. As you can see from my code, I don't use a querydef at all- I only reference querydef if I need to modify SQL or execute an action, but not to point to for a recordset, as Access need to know what recordset you want to point at; it knows that you set a querydef and a recordset but it doesn't know that the recordset is supposed to be pointing at the query so therefore looking into that recordset, you get nothing.

HTH.

PS Saw your post about applying a parameter. That can be done using my code. Give me 1/2 hour and I'll get you that code.

 

[boblarson]

If you open the query in design view and right click in the area where the table is shown you can select PARAMETERS and you can define them there.

 

[boblarson]

Thanks Banana - as I said DAO is not my strong point and I forgot about the db object.

 

[natsirtm]

If you open the query in design view and right click in the area where the table is shown you can select PARAMETERS and you can define them there.

I've defined the parameters in the query, but setting the parameter needs to be dynamic (ie. set to the date in my forms textbox)

 

[boblarson]

If the parameter needs to be set from the form, why not just reference the form's control in the query's criteria itself? That's a normal way of passing parameter information to the query.