Cannot Requery a recordset

I just decided to try setting the parameters in the VBA code because the queries are re-used in multiple places and I'd have to duplicate every query once with a parameter and once with the form's control.

I may have to go that route, but am trying to avoid duplicating the queries.
 
You say the queries are used in multiple places but the criteria will be based on something different each time? For example, one time it will be by date and another by some other field? Is that correct?

I would suggest that you create a form with the base query as the recordset and then open that form with a Where clause in each place you need to view it (you can set it as Datasheet view to make it look like the query is being opened).

You can use

DoCmd.OpenForm "YourFormNameHere", acNormal, , "[YourDateField]=#" Forms!YourFormName.YourDateTextBoxNameHere & "#"

and then if by a different field elsewhere you can use

DoCmd.OpenForm "YourFormNameHere", acNormal, , "[YourTextField]='" Forms!YourFormName.YourDateTextBoxNameHere & "'"

or

DoCmd.OpenForm "YourFormNameHere", acNormal, , "[YourNumberField]=" Forms!YourFormName.YourDateTextBoxNameHere
 
boblarson said:
You say the queries are used in multiple places but the criteria will be based on something different each time? For example, one time it will be by date and another by some other field? Is that correct?
Click to expand...

not exactly, just that the query w/ parameter popup is fine for one implementation, while I want to set the parameter programatically in another.

I may end up going that route anyways, but will hold off to see if what Banana can show me can be implemented more easily than creating new forms and queries.
 
natsirtm said:
but will hold off to see if what Banana can show me can be implemented more easily than creating new forms and queries.
Click to expand...
I don't think you understood me - I didn't say formS and querIES I said you can create ONE form and ONE query to accomplish this. What it pulls is dependent on what you pass to it in the Where clause of the DoCmd.OpenForm code. So, you can set it to whatever you want at each place you want to open it. No multiple queries or forms required.
 
Sorry to keep you waiting.

This is straight out of Alison Baltier's book, a very handy guide, by the way. If you can, snatch up a used copy for cheap. I've used this code modified to my database, and did well.

To modify parameters in VBA:
Code: 
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db= CurrentDb()

Set qdf = db.QueryDefs("QueryName")

'Set parameters of the querydef object
qdf.Parameters("Enter the question you want to pop up") = VariableName

Set rst = qdf.OpenRecordset 'Doh! I forgot there was another way to point to a recordset of a querydef :o

'Do whatever you wanted to do here; if this is an action query, qdf.Execute goes here. If it's just a select query for display, point it to whatever form control or loop or something

When the code executes, a small dialog box will pop up prompting for the parameter data. If you already know the variable from somewhere, you can just pass the data using a variable.

If you will have more than just one parameter, then I'd suggest doing what Bob suggested- create a special popup form for that query to enter in all parameters and whenever you need this query, open the same form.

I hope that helps.

PS: VariableName should be same as whatever you specified in query design view parameter.
 
boblarson said:
I don't think you understood me - I didn't say formS and querIES I said you can create ONE form and ONE query to accomplish this. What it pulls is dependent on what you pass to it in the Where clause of the DoCmd.OpenForm code. So, you can set it to whatever you want at each place you want to open it. No multiple queries or forms required.
Click to expand...

No i understood :) but I have quite a bit built and if I can use that with some changes will be faster & less effort than starting nearly from scratch.
 
Banana said:
Sorry to keep you waiting.

This is straight out of Alison Baltier's book, a very handy guide, by the way. If you can, snatch up a used copy for cheap. I've used this code modified to my database, and did well.

To modify parameters in VBA:
Code: 
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db= CurrentDb()

Set qdf = db.QueryDefs("QueryName")

'Set parameters of the querydef object
qdf.Parameters("Enter the question you want to pop up") = VariableName

Set rst = qdf.OpenRecordset 'Doh! I forgot there was another way to point to a recordset of a querydef :o

'Do whatever you wanted to do here; if this is an action query, qdf.Execute goes here. If it's just a select query for display, point it to whatever form control or loop or something

When the code executes, a small dialog box will pop up prompting for the parameter data. If you already know the variable from somewhere, you can just pass the data using a variable.

If you will have more than just one parameter, then I'd suggest doing what Bob suggested- create a special popup form for that query to enter in all parameters and whenever you need this query, open the same form.

I hope that helps.

PS: VariableName should be same as whatever you specified in query design view parameter.
Click to expand...

lol.. uh that's exactly what I was doing.. and I am trying to avoid that parameter popup since it is the same parameter across all five queries..

i will figure out some combination of bob's suggestion. thanks.
 
Just in case you missed- if you don't want the parameter to pop up, just pass the variable directly.

E.g.

Code: 
qdf.Parameters("Variable1") = 1
qdf.Parameters("Variable2") = VBAVariable

Won't pop up.
 
That is exactly what I thought I had done, and the parameter popup still appears.

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
For i = 1 To 1 ' Test single TTC

    Dim qdf1 As QueryDef, qdf2 As QueryDef, qdf3 As QueryDef, qdf4 As QueryDef, [B]qdfSC As QueryDef[/B]
    [B]Dim rstSC As DAO.Recordset[/B]
    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")
    [B]Set qdfSC = CurrentDb.QueryDefs("00ChkSanity")[/B]
    
    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
    
    [B]qdfSC.Parameters(0) = qryProcessDate[/B]
    
    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
    [B]Set rstSC = qdfSC.OpenRecordset(dbOpenDynaset)[/B]
    
    ' 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
    [B]qdfSC.Close[/B]
    [B]rstSC.Close[/B]
    
    Set qdf1 = Nothing
    Set qdf2 = Nothing
    Set qdf3 = Nothing
    Set qdf4 = Nothing
    [B]Set qdfSC = Nothing[/B]
    [B]Set rstSC = Nothing[/B]

Next i

End Sub

It uses the "qryProcessDate" for each of the four first queries, then displays a popup for the parameter of the last query.
 
Firstly, I'm not seeing what you are doing with the open recordset. Right now, you're just opening it and closing it. It doesn't seem to be even used for the report. If you wanted to use that open recordset, you need to update the report's recordsource to match with that open recordset.

Secondly, are you using this in every of your module in the first line:

Code: 
Option Compare Database
Option Explicit

Right now, I'm not seeing how you have your variables defined, and this could be why you're still getting popup- without option explicit, it's possible to have one variable and accidentally create another variable when you only wanted to point toward first variable.

Also, I would avoid this:

Code: 
Dim qdf1 As QueryDef, Dim qdf2 As QueryDef ... ... Dim qdfSC As QueryDef

Try this instead:

Code: 
Dim qdf1 As QueryDef
Dim qdf2 As QueryDef
... ...
Dim qdfSC As QueryDef

This may help compiler catch any error, if any. As mentioned earlier, I try to avoid doing multiple things in one line; it may seems to save time but is hell for debugging.

Post back when you've turned on option explicit.
 
Banana said:
you need to update the report's recordsource to match with that open recordset
Click to expand...

that's got to be it.

done a quick google, should I be setting it in the reports onopen event?
or can i do it in the same VBA code?

Code: 
Reports!subreportname.RecordSource = rstSC
 
Well, the VBA code you posted will be fine, BUT you need to do some rearranging-

After executing your action queries, you should then open the report, assign the parameter variable to the query, then set open recordset to that query, then set the report's recordsource to that open recordset.

You shouldn't then get that pop-up, unless there's a variable with wrong name or whatever and option explicit isn't on.
 
As such?
Getting compile error, type mismatch when setting reports recordsource to the recordset.

Code: 
Option Compare Database
Option Explicit

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
For i = 1 To 1 ' Test single TTC

    Dim qdf1 As QueryDef
    Dim qdf2 As QueryDef
    Dim qdf3 As QueryDef
    Dim qdf4 As QueryDef
    Dim 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")
'    Set qdfSC = CurrentDb.QueryDefs("00ChkSanity_ar")
    
    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
    
    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
    
    ' Open report
    Dim stSubDocName As String
    stSubDocName = "WFMAging_sub_00ChkSanity"
    
    DoCmd.OpenReport stSubDocName
    
    qdfSC.Parameters(0) = qryProcessDate
    
    Set rstSC = qdfSC.OpenRecordset(dbOpenDynaset)
    
    [B]Reports!WFMAging_sub_00ChkSanity.RecordSource = rstSC[/B]
    
    ' 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
 
Hmm, I had forgotten about that. Access bitched to me about same thing when I tried to do this few weeks ago. I digged up my code and apparently what I did was-
Code: 
Set Me.Recordsource = qdf.SQL

and that worked. But the strange thing was I never understood why it complained that it wouldn't accept a open recordset when it did just perfectly fine with another form I had.

I don't know if passing the SQL will keep the parameters as well. Give it a whirl.

Your code looks good otherwise, BTW.
 
Since this code is in the command button on a form, as opposed to the subreport itself.. Can I try putting something similar in the subreports OnOpen event? or OnLoad?
 
Sure why not. You just need to make sure you can pass the variable from that command button sub to the event.

FYI:

OnLoad -> OnOpen -> OnClose -> OnUnload

OnLoad and OnClose event can be cancelled, IIRC whereas OnOpen and OnUnload cannot be cancelled. Therefore, OnLoad would be a good place to do something and still back out if there's a problem preventing it from opening (e.g. type mismatch or whatever).
 
Banana said:
Sure why not. You just need to make sure you can pass the variable from that command button sub to the event.

FYI:

OnLoad -> OnOpen -> OnClose -> OnUnload

OnLoad and OnClose event can be cancelled, IIRC whereas OnOpen and OnUnload cannot be cancelled. Therefore, OnLoad would be a good place to do something and still back out if there's a problem preventing it from opening (e.g. type mismatch or whatever).
Click to expand...

Slight error here. The UNLOAD event can be canceled but the Close event cannot. And the order is:

OPEN > LOAD > UNLOAD > CLOSE

more info here
http://office.microsoft.com/en-us/access/HP051867611033.aspx
 
There is no OnLoad for Reports.
I've made the recordset a global variable. Something is not quite right though as i barely see the "outputting report bla bla" screen and it isn't generating a word doc.

Will try just opening the report.
 
I'm stabbing in dark here, but I think reports can't be modified once it's been opened.

Try opening it in Design view and hidden, modify the recordsource, close it then reopen it in preview or whatever.

Does that work?

Bob- Thanks for the correction. It is a bit odd as I'd have thought load came first before opening as loading had to do with putting object into the system's memory and open for actually displaying on screen... :o
 
:\
Code: 
DoCmd.OpenReport "reportname", acViewNormal
is automatically printing the report?!?
 
You must log in or register to reply here.

Similar threads

N
Solved How get lid of domain function and replace them with opening recordset in MS Access VBA
Replies
10
Views
512
nector
N
E
Generating report on a dynamic subform
Replies
6
Views
536
The_Doc_Man
The_Doc_Man
K
Solved Freeze the recordset of a search form
2
Replies
33
Views
2,280
cheekybuddha
cheekybuddha
A
Secure Storage Model for Microsoft Access: Stream-Based Encrypted Data Architecture(for discussion)
Replies
5
Views
428
GPGeorge
GPGeorge
M
Solved Form Filter and RecordSource Don't Query New Record
Replies
11
Views
1,441
maxwhipw
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom