Determining (in VBA) if a recordset is open

[PW]

Hi,

I'd like to close a recordset and set the database to nothing if a
recordset is open if an error has occured. Leaving a recordset open
and a database open isn't a good idea, right?

Thanks,

-paul w

[Rich P]

Once a recordset object and a database object go out of scope, VBA
automatically closes the objects, although, it is a good practice to
specifically close a recordset >rs.Close and set a database object to
nothing >Set DB = Nothing. It helps to further reduce the possibility
of a null pointer still on the stack (at the system level).

If you are using global variables for your objects, on the other hand,
these objects won't go out of scope when a procedure is finished using
them. In this case, if you don't close a recordset and try to reuse it
again in another procedure, then Access will complain. I find it best
to use Recordset objects at the local level (local to the procedure
using it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[David W. Fenton]

PW <pa********@removewilliamsonenterprises.comwrote in
news:pd********************************@4ax.com:

I'd like to close a recordset and set the database to nothing if a
recordset is open if an error has occured. Leaving a recordset
open and a database open isn't a good idea, right?

The usual method is to test if the recordset variable Is Nothing.
Since there's no way to initialize a recordset variable without it
pointing to an open recordset, this works pretty well.

In the exit routine I use this:

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

For db variables, I do the same, but without the close in cases
where the db variable is pointing to the MDB currently opened in the
Access UI. The .Close causes no problem when the variable is
initialized with CurrentDB() but can cause an error if you
initialized it with DBEngine(0)(0).

I do know that it's possible for a database variable to be Not
Nothing and not be open, and I'd assume that between the rs.Close
and the Set rs = Nothing that the variable is Not Nothing but
closed, but that's only going to happen once you've closed the
recordset. If you only do that in your cleanup, it oughtn't be an
issue.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

PW <pa********@removewilliamsonenterprises.comwrote in
news:pd********************************@4ax.com:

>I'd like to close a recordset and set the database to nothing if a
recordset is open if an error has occured. Leaving a recordset
open and a database open isn't a good idea, right?

The usual method is to test if the recordset variable Is Nothing.
Since there's no way to initialize a recordset variable without it
pointing to an open recordset, this works pretty well.

In the exit routine I use this:

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

For db variables, I do the same, but without the close in cases
where the db variable is pointing to the MDB currently opened in the
Access UI. The .Close causes no problem when the variable is
initialized with CurrentDB() but can cause an error if you
initialized it with DBEngine(0)(0).

I do know that it's possible for a database variable to be Not
Nothing and not be open, and I'd assume that between the rs.Close
and the Set rs = Nothing that the variable is Not Nothing but
closed, but that's only going to happen once you've closed the
recordset. If you only do that in your cleanup, it oughtn't be an
issue.

It's unfortunate the recordset in question isn't an ADO recordset, I
suppose. Then we could just test its State property for

adStateClosed 0 Indicates that the object is closed.
adStateOpen 1 Indicates that the object is open.

and we'd know ... and a few other things.

adStateConnecting 2 Indicates that the object is connecting.
adStateExecuting 4 Indicates that the object is executing a command.
adStateFetching 8 Indicates that the rows of the object are being
retrieved.

But I suppose we can't expect
an obsolete technology to give us very much;
we just have to live with it, for a while that is, while it survives.

Of course if it were an ADO recordset, in this case we wouldn't have to
test it anyway as ADO pointers are gathered up in garbage-collection and
released without any intervention anyway.

I guess it's no wonder that DAO is kaput!

--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadmap.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written
using these technologies, consider migrating those applications to
ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not
be available on the 64-bit Windows operating system.
.....

[Tom van Stiphout]

On Tue, 31 Oct 2006 16:54:31 -0700, PW
<pa********@removewilliamsonenterprises.comwrote :

My approach is to close after I open, and set to nothing in the Exit
section:

sub test()
on error goto ErrHandler
dim rs as dao.recordset
if TheConditionsAreRight()
set rs=currentdb.OpenRecordset(...)
'do your thing
rs.close
end if

ExitHandler:
set rs=nothing
exit sub

Err_Handler
Msgbox Err.Description
resume ExitHandler
end sub

I guess if I really needed to, I would maintain a boolean to indicate
whether I had opened the recordset or not:
set rs=currentdb.OpenRecordset(...)
blnRsOpen = True
'do your thing

ExitHandler:
if blnRsOpen = True then rs.Close
set rs=nothing
exit sub

I agree with Lyle that it's an omission to the DAO object model that a
State property or equivalent is not available. So maintain your own.

-Tom.

>Hi,

I'd like to close a recordset and set the database to nothing if a
recordset is open if an error has occured. Leaving a recordset open
and a database open isn't a good idea, right?

Thanks,

-paul w

[Lyle Fairfield]

Tom van Stiphout <no*************@cox.netwrote in
news:jb********************************@4ax.com:

On Tue, 31 Oct 2006 16:54:31 -0700, PW
<pa********@removewilliamsonenterprises.comwrote :

My approach is to close after I open, and set to nothing in the Exit
section:

That has been my approach with DAO too.

I'm wondering if it is still required. Should this code which creates 100
copies of the current database and has each one open a recordset cause a
problem because it closes nothing and releases nothing?

Sub temp()
Dim db(0 To 99) As DAO.Database
Dim rs(0 To 99) As DAO.Recordset
Dim x&
For x = 0 To 99
Set db(x) = CurrentDb()
Set rs(x) = db(x).OpenRecordset("SELECT * FROM Customers")
With rs(x)
.Move 10
.Edit
.Fields("CustomerID").Value = "JONES"
End With
Next x
'Quit
End Sub

Clearly, some things are left in limbo. I've tried it or something
similar (mostly quitting with the exit button afer doing some other work)
many times over the past couple of months in Access 2003:
no ghosts
no memory problems
nada ill effect that I can observe

Did MS "fix" this?
Did they tell us?
Did I miss it?
Am I doing something wrong (not doing something I should to create a
problem)?

The Customers Table is imported from Northwind db.

[Tom van Stiphout]

On Wed, 01 Nov 2006 03:53:13 GMT, Lyle Fairfield
<ly***********@aim.comwrote:

I wish there was an authorative article from MSFT on this topic. Don't
hold your breath. If they didn't write it in the first 10 years...

In DotNet I don't write it like this because in that environment it
has clearly been stated that unclosed objects will be cleaned up.
Without such statement for DAO/COM I mostly write code like I do to
stay on the safe side.

-Tom.

>Tom van Stiphout <no*************@cox.netwrote in
news:jb********************************@4ax.com :

>On Tue, 31 Oct 2006 16:54:31 -0700, PW
<pa********@removewilliamsonenterprises.comwrot e:

My approach is to close after I open, and set to nothing in the Exit
section:

That has been my approach with DAO too.

I'm wondering if it is still required. Should this code which creates 100
copies of the current database and has each one open a recordset cause a
problem because it closes nothing and releases nothing?

Sub temp()
Dim db(0 To 99) As DAO.Database
Dim rs(0 To 99) As DAO.Recordset
Dim x&
For x = 0 To 99
Set db(x) = CurrentDb()
Set rs(x) = db(x).OpenRecordset("SELECT * FROM Customers")
With rs(x)
.Move 10
.Edit
.Fields("CustomerID").Value = "JONES"
End With
Next x
'Quit
End Sub

Clearly, some things are left in limbo. I've tried it or something
similar (mostly quitting with the exit button afer doing some other work)
many times over the past couple of months in Access 2003:
no ghosts
no memory problems
nada ill effect that I can observe

Did MS "fix" this?
Did they tell us?
Did I miss it?
Am I doing something wrong (not doing something I should to create a
problem)?

The Customers Table is imported from Northwind db.

[PW]

On Tue, 31 Oct 2006 16:54:31 -0700, PW
<pa********@removewilliamsonenterprises.comwrote :

>Hi,

I'd like to close a recordset and set the database to nothing if a
recordset is open if an error has occured. Leaving a recordset open
and a database open isn't a good idea, right?

Thanks,

-paul w

Good stuff everyone! Thanks!

-paul

[David W. Fenton]

Lyle Fairfield <ly***********@aim.comwrote in
news:Xn*********************************@216.221.8 1.119:

I'm wondering if it is still required. Should this code which
creates 100 copies of the current database and has each one open a
recordset cause a problem because it closes nothing and releases
nothing?

Sub temp()
Dim db(0 To 99) As DAO.Database
Dim rs(0 To 99) As DAO.Recordset
Dim x&
For x = 0 To 99
Set db(x) = CurrentDb()
Set rs(x) = db(x).OpenRecordset("SELECT * FROM Customers")
With rs(x)
.Move 10
.Edit
.Fields("CustomerID").Value = "JONES"
End With
Next x
'Quit
End Sub

When I re-use a DAO recordset variable, I close the previous
recordset but don't set it to NOTHING, on the theory that I'm
setting it to SOMETHING ELSE, which by definition will clear
whatever's there in the variable.

I then set to NOTHING after all the recordsets have been used. My
theory is that I .Close to clear the memory used by the recordset
itself, but don't need to set to Nothing for the pointer because I'm
re-using the same variable for a different pointer.

If there's an off chance that the reference count is off because
it's not been set to Nothing, I've never had any problems with it,
and I've done it religiously in every snippet of code that re-uses a
recordset variable.

(BTW, setting a db variable to CurrentDB() 100 times in a loop is
complete nonsense; first off, you can't close it, but you also
should have it outside the loop, as it can't possibly change value
on an interation of the loop)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

Lyle Fairfield <ly***********@aim.comwrote in
news:Xn*********************************@216.221.8 1.119:

>I'm wondering if it is still required. Should this code which
creates 100 copies of the current database and has each one open a
recordset cause a problem because it closes nothing and releases
nothing?

Sub temp()
Dim db(0 To 99) As DAO.Database
Dim rs(0 To 99) As DAO.Recordset
Dim x&
For x = 0 To 99
Set db(x) = CurrentDb()
Set rs(x) = db(x).OpenRecordset("SELECT * FROM Customers")
With rs(x)
.Move 10
.Edit
.Fields("CustomerID").Value = "JONES"
End With
Next x
'Quit
End Sub

(BTW, setting a db variable to CurrentDB() 100 times in a loop is
complete nonsense; first off, you can't close it, but you also
should have it outside the loop, as it can't possibly change value
on an interation of the loop)

I'm sorry that you didn't understand the code.

--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadmap.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written
using these technologies, consider migrating those applications to
ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not
be available on the 64-bit Windows operating system.
.....

[PW]

On Tue, 31 Oct 2006 19:54:11 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:

>PW <pa********@removewilliamsonenterprises.comwrote in
news:pd********************************@4ax.com :

>I'd like to close a recordset and set the database to nothing if a
recordset is open if an error has occured. Leaving a recordset
open and a database open isn't a good idea, right?

The usual method is to test if the recordset variable Is Nothing.
Since there's no way to initialize a recordset variable without it
pointing to an open recordset, this works pretty well.

In the exit routine I use this:

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

I tried that David and received error 3420 "Object invalid or no
longer set". The recordset was already closed before the error
occurred.

-pw

[David W. Fenton]

PW <pa********@removewilliamsonenterprises.comwrote in
news:2f********************************@4ax.com:

On Tue, 31 Oct 2006 19:54:11 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:

>>PW <pa********@removewilliamsonenterprises.comwrote in
news:pd********************************@4ax.co m:

>>I'd like to close a recordset and set the database to nothing if
a recordset is open if an error has occured. Leaving a
recordset open and a database open isn't a good idea, right?

The usual method is to test if the recordset variable Is Nothing.
Since there's no way to initialize a recordset variable without it
pointing to an open recordset, this works pretty well.

In the exit routine I use this:

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

I tried that David and received error 3420 "Object invalid or no
longer set". The recordset was already closed before the error
occurred.

Well, did you close it beforehand or set it to Nothing in your code?
If so, yes, it will not work. You have to leave the cleanup to exit
routine or, yes, you'll get an error.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[PW]

>>>In the exit routine I use this:

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

I tried that David and received error 3420 "Object invalid or no
longer set". The recordset was already closed before the error
occurred.

Well, did you close it beforehand or set it to Nothing in your code?
If so, yes, it will not work. You have to leave the cleanup to exit
routine or, yes, you'll get an error.

I forgot to also set it to nothing! Doh! :-)

thanks,

-pw

[PW]

>>>In the exit routine I use this:

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

I tried that David and received error 3420 "Object invalid or no
longer set". The recordset was already closed before the error
occurred.

Well, did you close it beforehand or set it to Nothing in your code?
If so, yes, it will not work. You have to leave the cleanup to exit
routine or, yes, you'll get an error.

It's working David! Thanks!

-pw

[PW]

On Thu, 02 Nov 2006 09:36:47 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:

>PW <pa********@removewilliamsonenterprises.comwrote in
news:2f********************************@4ax.com :

>On Tue, 31 Oct 2006 19:54:11 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:

>>>PW <pa********@removewilliamsonenterprises.comwrote in
news:pd********************************@4ax.com :

I'd like to close a recordset and set the database to nothing if
a recordset is open if an error has occured. Leaving a
recordset open and a database open isn't a good idea, right?

The usual method is to test if the recordset variable Is Nothing.
Since there's no way to initialize a recordset variable without it
pointing to an open recordset, this works pretty well.

In the exit routine I use this:

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

I tried that David and received error 3420 "Object invalid or no
longer set". The recordset was already closed before the error
occurred.

Well, did you close it beforehand or set it to Nothing in your code?
If so, yes, it will not work. You have to leave the cleanup to exit
routine or, yes, you'll get an error.

Typical buggy Access!

On an AfterUpdate for a combobox the code goes *directly* to:

ExitTag_pw:
If Not rstPurchases_pw Is Nothing Then
rstPurchases_pw.CLOSE
Set rstPurchases_pw = Nothing
End If
If Not rstInventory_pw Is Nothing Then
rstInventory_pw.CLOSE
Set rstInventory_pw = Nothing
End If
Set dbCurr_pw = Nothing
Exit Sub

I get object required on "If Not rstInventory_pw Is Nothing" yet
rstPurchases_pw = Nothing! rstInventory_pw = Empty!!

-pw

[Terry Kreft]

Sounds more like your code.

Show us the declarations for those variables as it looks like you've
declared rstInventory_pw as variant.

--

Terry Kreft
"PW" <pa********@removewilliamsonenterprises.comwrote in message
news:te********************************@4ax.com...

On Thu, 02 Nov 2006 09:36:47 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:

PW <pa********@removewilliamsonenterprises.comwrote in
news:2f********************************@4ax.com:

On Tue, 31 Oct 2006 19:54:11 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:

PW <pa********@removewilliamsonenterprises.comwrote in
news:pd********************************@4ax.co m:

I'd like to close a recordset and set the database to nothing if
a recordset is open if an error has occured. Leaving a
recordset open and a database open isn't a good idea, right?

The usual method is to test if the recordset variable Is Nothing.
Since there's no way to initialize a recordset variable without it
pointing to an open recordset, this works pretty well.

In the exit routine I use this:

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
I tried that David and received error 3420 "Object invalid or no
longer set". The recordset was already closed before the error
occurred.

Well, did you close it beforehand or set it to Nothing in your code?
If so, yes, it will not work. You have to leave the cleanup to exit
routine or, yes, you'll get an error.

Typical buggy Access!

On an AfterUpdate for a combobox the code goes *directly* to:

ExitTag_pw:
If Not rstPurchases_pw Is Nothing Then
rstPurchases_pw.CLOSE
Set rstPurchases_pw = Nothing
End If
If Not rstInventory_pw Is Nothing Then
rstInventory_pw.CLOSE
Set rstInventory_pw = Nothing
End If
Set dbCurr_pw = Nothing
Exit Sub

I get object required on "If Not rstInventory_pw Is Nothing" yet
rstPurchases_pw = Nothing! rstInventory_pw = Empty!!

-pw

[PW]

On Thu, 9 Nov 2006 19:49:10 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:

>Sounds more like your code.

Show us the declarations for those variables as it looks like you've
declared rstInventory_pw as variant.

Option Compare Database
Option Explicit
..
..
..

Private Sub cboItemNo_pw_AfterUpdate()

On Error GoTo ErrorTag_pw

If blnInEditMode_pw And Not blnAlreadyAdjusted_pw Then

Never makes it here (only in edit mode)
..
..
..

End If

With Me
.Requery
.txtPrice_pw = Me.cboItemNo_pw.Column(2)
.txtDesc_pw = Me.cboItemNo_pw.Column(1)
.txtQuantity_pw = 0
.txtQuantity_pw.SetFocus
End With

ExitTag_pw:
If Not rstPurchases_pw Is Nothing Then
rstPurchases_pw.CLOSE
Set rstPurchases_pw = Nothing
End If
If Not rstInventory_pw Is Nothing Then
rstInventory_pw.CLOSE
Set rstInventory_pw = Nothing
End If
Set dbCurr_pw = Nothing
Exit Sub

ErrorTag_pw:
MsgBox "Error #" & Err.number & ": " & Err.description
Resume ExitTag_pw

End Sub

I get object required on "If Not rstInventory_pw Is Nothing" yet
rstPurchases_pw = Nothing! rstInventory_pw = Empty!!

-pw

[Terry Kreft]

That isn't the declaration for the variables, I'm expecting to see

Dim rstInventory_pw as ...
or
Private rstInventory_pw as ...
or
Public rstInventory_pw as ...
or
Global rstInventory_pw as ...
or even
Dim rstInventory_pw
It's the bit represented by ... in the lines above which is really
important.

--

Terry Kreft
"PW" <pa********@removewilliamsonenterprises.comwrote in message
news:dt********************************@4ax.com...

On Thu, 9 Nov 2006 19:49:10 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:

Sounds more like your code.

Show us the declarations for those variables as it looks like you've
declared rstInventory_pw as variant.

Option Compare Database
Option Explicit
.
.
.

Private Sub cboItemNo_pw_AfterUpdate()

On Error GoTo ErrorTag_pw

If blnInEditMode_pw And Not blnAlreadyAdjusted_pw Then

Never makes it here (only in edit mode)
.
.
.

End If

With Me
.Requery
.txtPrice_pw = Me.cboItemNo_pw.Column(2)
.txtDesc_pw = Me.cboItemNo_pw.Column(1)
.txtQuantity_pw = 0
.txtQuantity_pw.SetFocus
End With

ExitTag_pw:
If Not rstPurchases_pw Is Nothing Then
rstPurchases_pw.CLOSE
Set rstPurchases_pw = Nothing
End If
If Not rstInventory_pw Is Nothing Then
rstInventory_pw.CLOSE
Set rstInventory_pw = Nothing
End If
Set dbCurr_pw = Nothing
Exit Sub

ErrorTag_pw:
MsgBox "Error #" & Err.number & ": " & Err.description
Resume ExitTag_pw

End Sub

I get object required on "If Not rstInventory_pw Is Nothing" yet
rstPurchases_pw = Nothing! rstInventory_pw = Empty!!

-pw

[Tim Marshall]

PW wrote:

Typical buggy Access!

You mean typical person who blames the tools, doesn't know what he's
doing and then goes around telling people as though he's an expert that
"<such and such a platformis pretty buggy!"

Access VBA is actually pretty robust and very easy to work with.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[PW]

On Fri, 10 Nov 2006 12:30:17 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:

>That isn't the declaration for the variables, I'm expecting to see

Dim rstInventory_pw as ...
or
Private rstInventory_pw as ...
or
Public rstInventory_pw as ...
or
Global rstInventory_pw as ...
or even
Dim rstInventory_pw
It's the bit represented by ... in the lines above which is really
important.

Oops!

Here ya go:

Dim dbCurr_pw As Database
Dim rstInventory_pw, rstPurchases_pw As Recordset
Dim strFind_pw As String
Dim intPurchaseID_pw As Integer

Set dbCurr_pw = CurrentDb

[Terry Kreft]

Ahh, good, I expected something like that.

The line ...
Dim rstInventory_pw, rstPurchases_pw As Recordset

isn't doing what you think (this is a very common mistake BTW). This
actually declares rstInventory_pw as a Variant not a Recordset.

You should replace this line with either
Dim rstInventory_pw As Recordset, rstPurchases_pw As Recordset

or (preferably in my opinion)
Dim rstInventory_pw As Recordset
Dim rstPurchases_pw As Recordset
Personally, I prefer to declare one variable per line as this makes it
easier to spot errors like this.
--

Terry Kreft
"PW" <pa********@removewilliamsonenterprises.comwrote in message
news:ue********************************@4ax.com...

On Fri, 10 Nov 2006 12:30:17 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:

That isn't the declaration for the variables, I'm expecting to see

Dim rstInventory_pw as ...
or
Private rstInventory_pw as ...
or
Public rstInventory_pw as ...
or
Global rstInventory_pw as ...
or even
Dim rstInventory_pw
It's the bit represented by ... in the lines above which is really
important.

Oops!

Here ya go:

Dim dbCurr_pw As Database
Dim rstInventory_pw, rstPurchases_pw As Recordset
Dim strFind_pw As String
Dim intPurchaseID_pw As Integer

Set dbCurr_pw = CurrentDb

[PW]

On Tue, 14 Nov 2006 20:01:54 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:

>
Ahh, good, I expected something like that.

The line ...
Dim rstInventory_pw, rstPurchases_pw As Recordset

isn't doing what you think (this is a very common mistake BTW). This
actually declares rstInventory_pw as a Variant not a Recordset.

You should replace this line with either
Dim rstInventory_pw As Recordset, rstPurchases_pw As Recordset

or (preferably in my opinion)
Dim rstInventory_pw As Recordset
Dim rstPurchases_pw As Recordset
Personally, I prefer to declare one variable per line as this makes it
easier to spot errors like this.

Well Terry, I have to admit that I messed up there. You are absolutely
correct! I knew better than that at one point in time :-)!

Thank you so much!

-paul

[Terry Kreft]

That would have been when you were a teenager; to quote the sign in a timber
merchants I used to use:-
"Employ a teenager, quick, while they still know it all!"

--

Terry Kreft
"PW" <pa********@removewilliamsonenterprises.comwrote in message
news:bg********************************@4ax.com...

On Tue, 14 Nov 2006 20:01:54 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:

<SNIP>

Well Terry, I have to admit that I messed up there. You are absolutely
correct! I knew better than that at one point in time :-)!

Thank you so much!

-paul

[PW]

On Wed, 15 Nov 2006 20:03:04 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:

>That would have been when you were a teenager; to quote the sign in a timber
merchants I used to use:-
"Employ a teenager, quick, while they still know it all!"

:-)

-pw