Persistent Object Reference

[Weaver]

I need to create an object (a recordset) that will not go out of scope
outside the sub that creates it. I've tried placing the Dim statement
in the General area and in another mod as Global; neither works.

(I'm creating the recordset with dynamic SQL.)

Any ideas on this?

[Tom van Stiphout]

On 15 Nov 2004 17:40:53 -0800, we*****@verizon.net (Weaver) wrote:

Global objects are declared with the Global keyword:
(in a standard module)
Global g_rsSomething As Recordset

-Tom.

I need to create an object (a recordset) that will not go out of scope
outside the sub that creates it. I've tried placing the Dim statement
in the General area and in another mod as Global; neither works.

(I'm creating the recordset with dynamic SQL.)

Any ideas on this?

[Steve Jorgensen]

On Mon, 15 Nov 2004 20:20:46 -0700, Tom van Stiphout <no*************@cox.net>
wrote:

On 15 Nov 2004 17:40:53 -0800, we*****@verizon.net (Weaver) wrote:

Global objects are declared with the Global keyword:
(in a standard module)
Global g_rsSomething As Recordset

-Tom.

I need to create an object (a recordset) that will not go out of scope
outside the sub that creates it. I've tried placing the Dim statement
in the General area and in another mod as Global; neither works.

(I'm creating the recordset with dynamic SQL.)

Any ideas on this?

Also, unless this is a disconnected ADO recordset, you'll have to keep a
reference to the Database (DAO) or Connection (DAO) as well, since the
recordset needs its database/connection to function.

[Weaver]

This is what I've done:

In a separate module kept for globals and widely used functions:

Global gblLMDrsPrev As Recordset
Global gblLMDdb As Database

In the form's module:

SQL = "SELECT * FROM tblForecast WHERE uidConfidence = " +
Format(uidNow)
Set gblLMDrsPrev = gblLMDdb.OpenRecordset(SQL, dbOpenSnapshot)

gblLMDrsPrev is lost to me after the form is moved to another record
in it's own recordsource.

(BTW, In the 'SQL =' line of code above, the 'Format' function call is
actually on the same line as the rest.)

Thanks again for your help.

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<fh********************************@4ax.com>. ..

On Mon, 15 Nov 2004 20:20:46 -0700, Tom van Stiphout <no*************@cox.net>
wrote:

On 15 Nov 2004 17:40:53 -0800, we*****@verizon.net (Weaver) wrote:

Global objects are declared with the Global keyword:
(in a standard module)
Global g_rsSomething As Recordset

-Tom.

I need to create an object (a recordset) that will not go out of scope
outside the sub that creates it. I've tried placing the Dim statement
in the General area and in another mod as Global; neither works.

(I'm creating the recordset with dynamic SQL.)

Any ideas on this?

Also, unless this is a disconnected ADO recordset, you'll have to keep a
reference to the Database (DAO) or Connection (DAO) as well, since the
recordset needs its database/connection to function.

[Weaver]

This is actually a followup to my previous post, which hasn't appeared
yet.

I was, out of habit, closing the database object. Sorry if I waisted
anyone's time on this one.

Thanks for your help.
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<fh********************************@4ax.com>. ..

On Mon, 15 Nov 2004 20:20:46 -0700, Tom van Stiphout <no*************@cox.net>
wrote:

On 15 Nov 2004 17:40:53 -0800, we*****@verizon.net (Weaver) wrote:

Global objects are declared with the Global keyword:
(in a standard module)
Global g_rsSomething As Recordset

-Tom.

I need to create an object (a recordset) that will not go out of scope
outside the sub that creates it. I've tried placing the Dim statement
in the General area and in another mod as Global; neither works.

(I'm creating the recordset with dynamic SQL.)

Any ideas on this?

Also, unless this is a disconnected ADO recordset, you'll have to keep a
reference to the Database (DAO) or Connection (DAO) as well, since the
recordset needs its database/connection to function.

[Penguin]

You reminded me of something persistent I've never solved. I have a
little form to filter reports by dates. Each report has (in my
tblReports) the name of the date field by which one is likely to
filter it, thus, the code below.

However, if I call it with, say, rptListChecksToPrint, the silly
OpenArgs seems to hold onto that report name. When I later call it
with rptChecks, I get the previous report, unless I close the
database. I tried tracing OpenArgs, and, sure enough, it's the
previous value.

Any ideas?

Private Sub Preview_Click()
Dim stCriteria As String
If IsNull([Beginning Date]) And IsNull([End Date]) Then
MsgBox ("Running report for all dates")
stCriteria = ""
ElseIf IsNull([Beginning Date]) Or IsNull([End Date]) Then
MsgBox "You must either enter both dates or no date at all!"
DoCmd.GoToControl "Beginning Date"
ElseIf [Beginning Date] > [End Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Date"
Else
stCriteria = "[" & tlookup("DateFieldName", "tblReports",
"Reportname='" & Me.OpenArgs & "'")
stCriteria = stCriteria & " ] between #" & [Beginning Date]
& "# and #" & [End Date] & "#"
End If

DoCmd.OpenReport Me.OpenArgs, A_PREVIEW, , stCriteria
Me.Visible = False
End Sub

[Steve Jorgensen]

I use OpenArgs to pass parameters to reports all the time, and have never seen
the problem you describe. Are you shure its not the calling code that has
somehow held on to the value in a variable that gets passed to OpenArgs?

I have seen bugs in my own code with similar symptoms to what you describe,
and it usually means I've passed the wrong piece of data to OpenArgs, but it
just happened to have the correct value for the first case I tested.

On 16 Nov 2004 20:11:54 -0800, pe***********@aol.com (Penguin) wrote:

You reminded me of something persistent I've never solved. I have a
little form to filter reports by dates. Each report has (in my
tblReports) the name of the date field by which one is likely to
filter it, thus, the code below.

However, if I call it with, say, rptListChecksToPrint, the silly
OpenArgs seems to hold onto that report name. When I later call it
with rptChecks, I get the previous report, unless I close the
database. I tried tracing OpenArgs, and, sure enough, it's the
previous value.

Any ideas?

Private Sub Preview_Click()
Dim stCriteria As String
If IsNull([Beginning Date]) And IsNull([End Date]) Then
MsgBox ("Running report for all dates")
stCriteria = ""
ElseIf IsNull([Beginning Date]) Or IsNull([End Date]) Then
MsgBox "You must either enter both dates or no date at all!"
DoCmd.GoToControl "Beginning Date"
ElseIf [Beginning Date] > [End Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Date"
Else
stCriteria = "[" & tlookup("DateFieldName", "tblReports",
"Reportname='" & Me.OpenArgs & "'")
stCriteria = stCriteria & " ] between #" & [Beginning Date]
& "# and #" & [End Date] & "#"
End If

DoCmd.OpenReport Me.OpenArgs, A_PREVIEW, , stCriteria
Me.Visible = False
End Sub

[Penguin]

Well, I knew I must be making a mistake somewhere, because there
couldn't possibly be a bug in Access, could there (VBG)?

So, I decided to examine all the code again very carefully, as you
suggested. The main resource being my 12-year-old son looking over my
shoulder. And, lo and behold, he said, "Look at:

DoCmd.OpenReport Me.OpenArgs, A_PREVIEW, , stCriteria

Me.Visible = False
End Sub

You are hiding your form, Mom, but you're never closing it, so of
course, it doesn't get new OpenArgs when you think you're re-opening
it, because you're only making the old one visible!"

"Wow, how'd you figure that out?" I asked him.

"Oh, you once showed me something similar," he replied modestly.

So thanks for pointing in this direction, Steve.

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<n8********************************@4ax.com>. .. I use OpenArgs to pass parameters to reports all the time, and have never seen
the problem you describe. Are you shure its not the calling code that has
somehow held on to the value in a variable that gets passed to OpenArgs?

I have seen bugs in my own code with similar symptoms to what you describe,
and it usually means I've passed the wrong piece of data to OpenArgs, but it
just happened to have the correct value for the first case I tested.

On 16 Nov 2004 20:11:54 -0800, pe***********@aol.com (Penguin) wrote:

You reminded me of something persistent I've never solved. I have a
little form to filter reports by dates. Each report has (in my
tblReports) the name of the date field by which one is likely to
filter it, thus, the code below.

However, if I call it with, say, rptListChecksToPrint, the silly
OpenArgs seems to hold onto that report name. When I later call it
with rptChecks, I get the previous report, unless I close the
database. I tried tracing OpenArgs, and, sure enough, it's the
previous value.

Any ideas?

Private Sub Preview_Click()
Dim stCriteria As String
If IsNull([Beginning Date]) And IsNull([End Date]) Then
MsgBox ("Running report for all dates")
stCriteria = ""
ElseIf IsNull([Beginning Date]) Or IsNull([End Date]) Then
MsgBox "You must either enter both dates or no date at all!"
DoCmd.GoToControl "Beginning Date"
ElseIf [Beginning Date] > [End Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Date"
Else
stCriteria = "[" & tlookup("DateFieldName", "tblReports",
"Reportname='" & Me.OpenArgs & "'")
stCriteria = stCriteria & " ] between #" & [Beginning Date]
& "# and #" & [End Date] & "#"
End If

DoCmd.OpenReport Me.OpenArgs, A_PREVIEW, , stCriteria
Me.Visible = False
End Sub