do i need to set objects to nothing

[xtra]

Hi Folk

I have about 1000 procedures in my project. Many, many of them are along
the lines of

function myfuntion () as boolean
on error goto er
'-
Dim Dbs as dao.database
Dim Rst as dao.recordset
Dim SqlS as string
'-
sqls = "..."
set dbs = currentdb
set rst = dbs.openrecordset(sqls)
do while not rst.eof
.....
rst.movenext
loop
'-
xt:
exit function
er:
msgbox error$
resume xt
end function
Should set dbs and rst to nothing or is that not necessary. If so, does
anyone know of an easy way to update all my procedures at once?

TIA

- Nicolaas

[Trevor Best]

Steve Jorgensen wrote:

On Mon, 16 May 2005 11:05:27 +1200, "xtra" <wi**********@hottermail.com>
wrote:

How about leaving the Dbs = currentdb open "forever". It is used all the
time and so it would be helpfull just to sit there open and ready for action
at any time...

If you do that, you have to decide where to initialize it and make sure it
gets reinitialized if it gets lost when you reset your code during debugging.

I do that and I have a place to initialise it, before I use it! :-) I
also have a place to re-initialise it when its lost due to debugging, in
my global error logger.

--
1f u c4n r34d th1s u r34lly
n33d t0 g37 l41d

[Chuck Grimsby]

You read my post backwards.

Not 10 creates... 1.

[Steve Jorgensen]

On 16 May 2005 02:12:07 -0700, "Chuck Grimsby" <c.*******@worldnet.att.net>
wrote:

You read my post backwards.

Not 10 creates... 1.

Then I read it forwards, but I don't know what you're saying. Could you walk
though a hypothetical?

[rkc]

Steve Jorgensen wrote:

On 16 May 2005 02:12:07 -0700, "Chuck Grimsby" <c.*******@worldnet.att.net>
wrote:

You read my post backwards.

Not 10 creates... 1.

Then I read it forwards, but I don't know what you're saying. Could you walk
though a hypothetical?

A single method wraps a process that calls multiple methods each of
which uses a single database object passed to it by the single calling
method. The database object is created once. Not much different than
creating a private member in a class that is then used by the methods
of the class.

[David W. Fenton]

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

On Sun, 15 May 2005 18:01:39 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

...

Well, I'm not sure what you mean, then, by "event handler."

Oh, OnClick, BeforeUpdate, etc. As I know you know (but for the
benefit of lurkers) these are all things the environment can
cause to run that were not called directly by other code, so
there can be no higher-level error handler to trap errors that
happen inside them.
Well, I disagree with that. Whether or not you need an error
handler depends entirely on what the code in the event handler
does.

My policy on these is that each one needs its own error handler
or it needs to only call code defined within the same class
module or a standard module in the VB project that does have an
error handler, and it may only pass constants or trivial local
variables as parameters

I think it's going overboard to have such a hard-and-fast rule,
but that's vintage Steve Jorgensen! :)

Are you sure you disagree with that? How many places where an
event handler has code behind it don't need error handling?

That entirely depends on what the code does.

The key is not that it's an error handler, it's that it does
something that needs an error handler.
How about ...

Private Sub Form_Current()
Debug.Print Me!txtName
End Sub

Of course, there are many ways that could fail. . . .
I can't conceive of any that would not be errors by the coder (i.e.,
the control doesn't exist), or that would prevent Access from
working in any useful sense.

In short, I don't think an error handler is an appropriate fix for
referring to a non-existent control.
. . . Pretty much any
event handler is going to deal with data on the object that called
it, and attempts to do that have failure modes.

If the event handler calls code that has error handlers, why would
the event handler need an error handler?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

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

On Sun, 15 May 2005 18:04:38 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"xtra" <wi**********@hottermail.com> wrote in
news:2E***************@news.xtra.co.nz:

As another idea, would it not be practical just to create a
global variable Dbs, set it to currentdb and leave it open
"forever"?

As I said, there are about 800 procedures in my database that
use dbs = currentdb so why not keep it open at all times, or
would that cause chaos if more than one procedure would use it
at the same time?

I used to do that, but the problem is that if there's any
possibility of a code reset, your global variable could be
Nothing. Likewise, you have to initialize it somewhere.

I posted this elsewhere in the thread, but what I use for this is
a function that uses a Static db variable and sets it to
CurrentDB() if it's not already set, and just returns the value
otherwise. The code is posted after my signature.

Just a warning - if you extend this idea to recordsets or some
such, you need a way to explicitly clean them up before the
application closes. I usually open a hidden form when the
application starts and use its Unload event handler to start a
clean-up routine.

Well, yes, it's definitely different for every DAO object other than
references to CurrentDB(), which is a different animal in comparison
to all others.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Trevor Best <no****@besty.org.uk> wrote in
news:42**********************@news.zen.co.uk:

Steve Jorgensen wrote:

On Mon, 16 May 2005 11:05:27 +1200, "xtra"
<wi**********@hottermail.com> wrote:

How about leaving the Dbs = currentdb open "forever". It is used
all the time and so it would be helpfull just to sit there open
and ready for action at any time...

If you do that, you have to decide where to initialize it and
make sure it gets reinitialized if it gets lost when you reset
your code during debugging.

I do that and I have a place to initialise it, before I use it!
:-) I also have a place to re-initialise it when its lost due to
debugging, in my global error logger.

But my code does all of that and allows you to treat the function as
thought it's a db variable. If you call:

Set rs = dbLocal.OpenRecordset(strSQL)

it doesn't matter what state things are in:

- if it's the first call, it will initialize

- if it's been initialized, it just works

- if there's been a code reset, it re-initializes transparently

So, you get all of what you describe in a single piece of code that
is called in a manner that, except for the lack of explicit
initialization (and the need to check if it needs to be
re-initialized), is indistinguishable from using a global variable.

In fact, a couple of weeks ago I revisited and revised an older app
where I'd used just such a global db variable, and every time I used
it, I prefaced it with:

If dbCurrent Is Nothing Then Set dbCurrent = CurrentDB()

Searching and replacing it out of the code found literally 100s of
instances of this. My method removes all of that, as well as
completely encapsulating the whole process in one function that can
be used as easily as the object it is a wrapper around.

What's not to like?

It has certainly made my coding life much, much easier!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tim Marshall]

David W. Fenton wrote:

What's not to like?

It has certainly made my coding life much, much easier!

I like it a lot, thanks.

Now the trick is to come up with something where we don't need to worry
about recordsets... 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Trevor Best]

David W. Fenton wrote:

What's not to like?

I didn't say I didn't like it. I just responded to Steve's comment on
the global database object.

--
1f u c4n r34d th1s u r34lly
n33d t0 g37 l41d

[Chuck Grimsby]

Example Function, using only the database object, as an optional
parameter, which the function will create and/or destroy depending upon
whether or not the database object was passed to it:

Public Function GetValue(strSQL As String, _
Optional whatDB As DAO.Database = Nothing, _
Optional WhatColumn As Integer = 0) _
As Variant
Dim myRST As DAO.Recordset
Dim bolISetTheDB As Boolean

If whatDB Is Nothing Then
Set whatDB = CurrentDb
bolISetTheDB = True
End If

Set myRST = whatDB.OpenRecordset(strSQL, dbOpenForwardOnly)
If Not myRST.EOF Then GetValue = myRST.Fields(WhatColumn)
myRST.Close
Set myRST = Nothing
If bolISetTheDB = True Then
whatDB.Close
End If
End Function

[Steve Jorgensen]

On Mon, 16 May 2005 16:37:39 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

....

Are you sure you disagree with that? How many places where an
event handler has code behind it don't need error handling?
That entirely depends on what the code does.

The key is not that it's an error handler, it's that it does
something that needs an error handler.

How about ...

Private Sub Form_Current()
Debug.Print Me!txtName
End Sub

Of course, there are many ways that could fail. . . .

I can't conceive of any that would not be errors by the coder (i.e.,
the control doesn't exist), or that would prevent Access from
working in any useful sense.

For one thing, there are times when Access will fire Current before the
recordset is valid. For another thing, the recordset may be temporarily blank
for legitimate reasons.
In short, I don't think an error handler is an appropriate fix for
referring to a non-existent control.

. . . Pretty much any
event handler is going to deal with data on the object that called
it, and attempts to do that have failure modes.

If the event handler calls code that has error handlers, why would
the event handler need an error handler?

I did say that it's OK to have the error handler in the called procedure so
long as the event handler isn't doing anything that can fail. In my
experience, getting the value of a control is something that can fail for
correctly written code.

[Bri]

David W. Fenton wrote:

But my code does all of that and allows you to treat the function as
thought it's a db variable. If you call:

Set rs = dbLocal.OpenRecordset(strSQL)

it doesn't matter what state things are in:

- if it's the first call, it will initialize

- if it's been initialized, it just works

- if there's been a code reset, it re-initializes transparently

What's not to like?

It has certainly made my coding life much, much easier!

David,

I thought I recalled reading that you were using Michka's Property Get
solution for this. His solution does all of what you suggest your code
does (and I'm sure it does) but with a lot less code. If you were using
it, why did you change to your current solution? If you weren't then my
memory is going foggy... In any case, can you see any reason why this
wouldn't be just as good?

Michka's Code:
==============
Private dbC As DAO.Database

Public Property Get db() As DAO.Database
If (dbC Is Nothing) Then
Set dbC = CurrentDb
End If

Set db = dbC
End Property
===============

--
Bri

[David W. Fenton]

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

On Mon, 16 May 2005 16:37:39 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

...

Are you sure you disagree with that? How many places where an
event handler has code behind it don't need error handling?
That entirely depends on what the code does.

The key is not that it's an error handler, it's that it does
something that needs an error handler.

How about ...

Private Sub Form_Current()
Debug.Print Me!txtName
End Sub

Of course, there are many ways that could fail. . . .

I can't conceive of any that would not be errors by the coder
(i.e., the control doesn't exist), or that would prevent Access
from working in any useful sense.

For one thing, there are times when Access will fire Current
before the recordset is valid. . . .

What times would those be?
. . . For another thing, the recordset
may be temporarily blank for legitimate reasons.

If there's no record, OnCurrent doesn't fire.

In short, I don't think an error handler is an appropriate fix for
referring to a non-existent control.

. . . Pretty much any
event handler is going to deal with data on the object that
called it, and attempts to do that have failure modes.

If the event handler calls code that has error handlers, why would
the event handler need an error handler?

I did say that it's OK to have the error handler in the called
procedure so long as the event handler isn't doing anything that
can fail. In my experience, getting the value of a control is
something that can fail for correctly written code.

Well, I think I'd handle that kind of thing with defensive coding
rather than with an error handler:

If Me.Recordsetclone.Recordcount <> 0 Then Debug.Print Me!txtName

What would be wrong with that?

Philosophically, I much prefer anticipating and handling a possible
erroneous condition to simply throwing error handlers at everything
in the hope that it will catch anything that goes wrong.

To me, avoiding the problem altogether is vastly preferable.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:d6**********@coranto.ucs.mun.ca:

David W. Fenton wrote:

What's not to like?

It has certainly made my coding life much, much easier!

I like it a lot, thanks.

Now the trick is to come up with something where we don't need to
worry about recordsets... 8)

Actually, I think the only way to accomplish that is with Steve's
classes. I have not seen it as worth the effort, myself.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Bri <no*@here.com> wrote in news:%wpie.1389525$8l.792710@pd7tw1no:

David W. Fenton wrote:

But my code does all of that and allows you to treat the function
as thought it's a db variable. If you call:

Set rs = dbLocal.OpenRecordset(strSQL)

it doesn't matter what state things are in:

- if it's the first call, it will initialize

- if it's been initialized, it just works

- if there's been a code reset, it re-initializes transparently

What's not to like?

It has certainly made my coding life much, much easier!

I thought I recalled reading that you were using Michka's Property
Get solution for this. His solution does all of what you suggest
your code does (and I'm sure it does) but with a lot less code. If
you were using it, why did you change to your current solution? If
you weren't then my memory is going foggy... In any case, can you
see any reason why this wouldn't be just as good?

Michka's Code:
==============
Private dbC As DAO.Database

Public Property Get db() As DAO.Database
If (dbC Is Nothing) Then
Set dbC = CurrentDb
End If

Set db = dbC
End Property
===============

If you'd read the comments in my code, you'd no why: it's because
the db variable can return *not* nothing and still be closed, and,
thus, invalid. I don't know the exact scenario that caused that, but
it shocked the hell out of me, and caused me to alter the code.

I encountered the problem after starting to use MichKa's initial
suggestion, which was just a function, rather than a property Get (I
don't know where that would be useful, actually, as your code
doesn't say what it's a property *of*).

Also, my code has the optional argument allowing you to force
re-initialization even when it's already been set.

But you are certainly right that the code I posted started from
MichKa's suggestion in this newsgroup. It became what it is through
alterations based on problems encountered during extensive use of
it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Chuck Grimsby" <c.*******@worldnet.att.net> wrote in
news:11*********************@g43g2000cwa.googlegro ups.com:

Example Function, using only the database object, as an optional
parameter, which the function will create and/or destroy depending
upon whether or not the database object was passed to it:

Public Function GetValue(strSQL As String, _
Optional whatDB As DAO.Database = Nothing, _
Optional WhatColumn As Integer = 0) _
As Variant
Dim myRST As DAO.Recordset
Dim bolISetTheDB As Boolean

If whatDB Is Nothing Then
Set whatDB = CurrentDb
bolISetTheDB = True
End If

Set myRST = whatDB.OpenRecordset(strSQL, dbOpenForwardOnly)
If Not myRST.EOF Then GetValue = myRST.Fields(WhatColumn)
myRST.Close
Set myRST = Nothing
If bolISetTheDB = True Then
whatDB.Close
End If
End Function

All of you who depend on "db Is Nothing" to indicate a valid pointer
are skating on thin ice. The variable can be Not Nothing and still
be invalid.

I ran onto it and that's why my code has an error handler for error
3420 (Object invalid or no longer set).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Wed, 18 May 2005 00:51:44 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

....

For one thing, there are times when Access will fire Current
before the recordset is valid. . . .
What times would those be?

Well, I've seen it happen several times. I believe it happens in many cases
with subforms. I promise you it happened because my code crashed when it did,
and I had to fix it in muliple places until I started engineering around it
up-front.

. . . For another thing, the recordset
may be temporarily blank for legitimate reasons.

If there's no record, OnCurrent doesn't fire.

The only time OnCurrent doesn't fire is when there -is- a recordset, there are
no records in it, and the form doesn't allow additions. Otherwise, OnCurrent
fires.

....

I did say that it's OK to have the error handler in the called
procedure so long as the event handler isn't doing anything that
can fail. In my experience, getting the value of a control is
something that can fail for correctly written code.

Well, I think I'd handle that kind of thing with defensive coding
rather than with an error handler:

If Me.Recordsetclone.Recordcount <> 0 Then Debug.Print Me!txtName

What would be wrong with that?

It implies that there will always be a recordset. If you then decide to
optimize a form by not querying it up-front, the code will fail. I make it a
policy to have code work independently of the order of operations whenever
possible.
Philosophically, I much prefer anticipating and handling a possible
erroneous condition to simply throwing error handlers at everything
in the hope that it will catch anything that goes wrong.

To me, avoiding the problem altogether is vastly preferable.

Sure, but only if you're sure you've avoided all the problem cases. I've
learned to assume that I don't know everything that could break, and I don't
knwo that just because it doesn't break now, it still won't break when I
change something somewhere else that affects the order in which things happen.

[Bri]

David W. Fenton wrote:

If you'd read the comments in my code, you'd no why: it's because
the db variable can return *not* nothing and still be closed, and,
thus, invalid. I don't know the exact scenario that caused that, but
it shocked the hell out of me, and caused me to alter the code.
Can something based on CurrentDB be Closed? I thought that couldn't be?
If it can be closed I would like to know what would do it and then avoid
it like the plague!
I encountered the problem after starting to use MichKa's initial
suggestion, which was just a function, rather than a property Get (I
don't know where that would be useful, actually, as your code
doesn't say what it's a property *of*).
I've wondered why Michka used a Property Get myself. It looks like this
could just as easily be done with a Function with the same results. Do
you know why he changed from a Function to a Property?

As for what it is a property of... Help states: "When you create a
Property procedure, it becomes a property of the module containing the
procedure." So, I assume it is technically a Property of the standard
module it is in. But it acts like a Function.
Also, my code has the optional argument allowing you to force
re-initialization even when it's already been set.
I can see a use for this if you are creating/deleting
Tabledefs/QueryDefs on the fly.
But you are certainly right that the code I posted started from
MichKa's suggestion in this newsgroup. It became what it is through
alterations based on problems encountered during extensive use of
it.

I've never come across any of these problems, so I guess I've been
lucky. I've started using it in all of my new apps and have even gone
back and updated older ones to use it. It has cut down the 'I got a
runtime error 91' phone calls to almost nothing.

--
Bri

[David W. Fenton]

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

On Wed, 18 May 2005 00:51:44 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

To me, avoiding the problem altogether is vastly preferable.

Sure, but only if you're sure you've avoided all the problem
cases. I've learned to assume that I don't know everything that
could break, and I don't knwo that just because it doesn't break
now, it still won't break when I change something somewhere else
that affects the order in which things happen.

Well, my point is simply that a brain-dead application of your
"rule" on error handlers is no replacement for attempting to
identify the likely error conditions that could occur and trying to
avoid them, instead of raising and handling the error.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Bri <no*@here.com> wrote in news:VCKie.1406721$8l.789604@pd7tw1no:

David W. Fenton wrote:

If you'd read the comments in my code, you'd no why: it's because
the db variable can return *not* nothing and still be closed,
and, thus, invalid. I don't know the exact scenario that caused
that, but it shocked the hell out of me, and caused me to alter
the code.
Can something based on CurrentDB be Closed? I thought that
couldn't be? If it can be closed I would like to know what would
do it and then avoid it like the plague!

No, it can't, but the *variable* is what is invalid. I don't know
how it gets invalidated, but it is not Nothing, but is also not
usable, either, triggering error 3420.

I encountered the problem after starting to use MichKa's initial
suggestion, which was just a function, rather than a property Get
(I don't know where that would be useful, actually, as your code
doesn't say what it's a property *of*).

I've wondered why Michka used a Property Get myself. It looks like
this could just as easily be done with a Function with the same
results. Do you know why he changed from a Function to a Property?

I never saw any of his code using a property -- I only remember the
function, and that's what I started with.
As for what it is a property of... Help states: "When you create a
Property procedure, it becomes a property of the module containing
the procedure." So, I assume it is technically a Property of the
standard module it is in. But it acts like a Function.

But then you have to call it from that module or fully qualified
with the name of the module. The function requires no parent, and
that makes it vastly superior, in my opinion.

Also, my code has the optional argument allowing you to force
re-initialization even when it's already been set.

I can see a use for this if you are creating/deleting
Tabledefs/QueryDefs on the fly.

Well, you wouldn't really need to initialize in that case -- you'd
only need to refresh the collection that you've been editing.

But you are certainly right that the code I posted started from
MichKa's suggestion in this newsgroup. It became what it is
through alterations based on problems encountered during
extensive use of it.

I've never come across any of these problems, so I guess I've been
lucky. I've started using it in all of my new apps and have even
gone back and updated older ones to use it. It has cut down the 'I
got a runtime error 91' phone calls to almost nothing.

I use it in both Access 97 and Access 2K, and perhaps the problems
occurred in Access 2K. I don't remember. All I know is that I was
shocked that it happened and then had to make the code substantially
more complicated to make it work.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Thu, 19 May 2005 00:42:35 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

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

On Wed, 18 May 2005 00:51:44 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

To me, avoiding the problem altogether is vastly preferable.

Sure, but only if you're sure you've avoided all the problem
cases. I've learned to assume that I don't know everything that
could break, and I don't knwo that just because it doesn't break
now, it still won't break when I change something somewhere else
that affects the order in which things happen.

Well, my point is simply that a brain-dead application of your
"rule" on error handlers is no replacement for attempting to
identify the likely error conditions that could occur and trying to
avoid them, instead of raising and handling the error.

I agree - thinking and attempting to understand each case is still required.

[rkc]

David W. Fenton wrote:

I never saw any of his code using a property -- I only remember the
function, and that's what I started with.

You participated in the thread in which Michka laid down the law
concerning setting a variable returned by currentdb() to nothing.
Even argued with him about it. He posted his global solution as
a Property Get method in a module in that thread as well as several others.

I'm curious as to why you went with a static variable in your function
rather than using a global variable as suggested by Michka.

[Bri]

David W. Fenton wrote:

Bri <no*@here.com> wrote in news:VCKie.1406721$8l.789604@pd7tw1no:

Can something based on CurrentDB be Closed? I thought that
couldn't be? If it can be closed I would like to know what would
do it and then avoid it like the plague!

No, it can't, but the *variable* is what is invalid. I don't know
how it gets invalidated, but it is not Nothing, but is also not
usable, either, triggering error 3420.

Yikes, glad I've never encountered it. I've used this code in AC97
(Majority), AC2K and AC2k3.

I've wondered why Michka used a Property Get myself. It looks like
this could just as easily be done with a Function with the same
results. Do you know why he changed from a Function to a Property?

I never saw any of his code using a property -- I only remember the
function, and that's what I started with.

I found something that might explain the Function vs Property Get:
http://groups.google.ca/group/comp.d...a065edac3bc14e
"Even better, instead of a function, make it a Property Get/Property Set
pair, so that you can use the same name when referring to the instance
and setting the private variable to Nothing. " -Dimitri Furman in a
reply to you as it turns out.

And you did participate in a lengthy thread where Michka brought up this
technique (point 7 of the post):
http://groups.google.ca/group/comp.d...251e3ac5c8012d

As for what it is a property of... Help states: "When you create a
Property procedure, it becomes a property of the module containing
the procedure." So, I assume it is technically a Property of the
standard module it is in. But it acts like a Function.

But then you have to call it from that module or fully qualified
with the name of the module. The function requires no parent, and
that makes it vastly superior, in my opinion.

Nope, just use it exactly the same as if it was a global variable (it is
a PUBLIC Property):
Set rs = db.OpenRecordset(stSQL)

I can see a use for this if you are creating/deleting
Tabledefs/QueryDefs on the fly.

Well, you wouldn't really need to initialize in that case -- you'd
only need to refresh the collection that you've been editing.

Agreed, that would be more efficient.
I use it in both Access 97 and Access 2K, and perhaps the problems
occurred in Access 2K. I don't remember. All I know is that I was
shocked that it happened and then had to make the code substantially
more complicated to make it work.

As I mentioned it above, I've used it in three generations of Access
without a problem. I'm not saying that the problem can't occur, just
that I don't seem to be using the code that triggers the problem in
association with the Property Get. Hmm, or maybe the problem occurs with
a Function but not the Property Get?

Interesting discussion!

--
Bri

[David W. Fenton]

Bri <no*@here.com> wrote in news:g85je.1416116$Xk.548934@pd7tw3no:

As I mentioned it above, I've used it in three generations of
Access without a problem. I'm not saying that the problem can't
occur, just that I don't seem to be using the code that triggers
the problem in association with the Property Get. Hmm, or maybe
the problem occurs with a Function but not the Property Get?

Well, I can't see that it would have anything to do with that,
because what the problem taught me was that testing the the variable
against Nothing is not a 100% reliably proxy for whether or not the
variable contents are themselves valid.

Too many double negatives!

Anyway, what it means is that a db variable can be Not Nothing and
still not contain a usable reference to a db.

Of course, I've had a lot of problems with this kind of thing in
Access 2K, in one client's app in particular, that I programmed in
A97 and converted to A2K for their use. That's how I found out all
sorts of things about little gotchas between A97 and A2K, where code
that is perfectly valid in A97 fails in A2K. I've now mostly moved
to programming in A2K directly, though I still find it very painful
(especially working with subforms -- whoever thought it was a good
idea to show the embedded subform obviously had never actually
worked with Access for any length of time).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bri]

David W. Fenton wrote:

Well, I can't see that it would have anything to do with that,
because what the problem taught me was that testing the the variable
against Nothing is not a 100% reliably proxy for whether or not the
variable contents are themselves valid.
Anyway, what it means is that a db variable can be Not Nothing and
still not contain a usable reference to a db.
OK, that's clearer. I haven't seen this either, but I'll be watching for
it now.
Too many double negatives!
If Not DB Is Nothing Then :{)
Of course, I've had a lot of problems with this kind of thing in
Access 2K, in one client's app in particular, that I programmed in
A97 and converted to A2K for their use. That's how I found out all
sorts of things about little gotchas between A97 and A2K, where code
that is perfectly valid in A97 fails in A2K. I've now mostly moved
to programming in A2K directly, though I still find it very painful
(especially working with subforms -- whoever thought it was a good
idea to show the embedded subform obviously had never actually
worked with Access for any length of time).

I still do almost all of my coding in AC97 and then convert or import
objects into the AC2k or AC2k3 app. I have also found a lot of things
that work fine in 97 but fail in the others. Fortunately, most of the
workarounds I've done wind up working in both. I also hate developing
outside of AC97. Not only are the subForms a problem, but I keep getting
caught where it doesn't ask me if I want to save something and then
silently discards all my work when I close down or Compact. And I miss
the Expression Builder (I know Michka has a replacement, but I'm
stubborn! ) Oh, when you finally do get to working with SQL Server
backends, there are some strange differences between SQL 7 and SQL 2K
that are very hard to diagnose, but overall SQL 2K is an improvment over
7 (mostly that you can do real relationships rather than have to setup
triggers to enforce the referential integrety)

--
Bri

[Lyle Fairfield]

Bri wrote:

but overall SQL 2K is an improvment over
7 (mostly that you can do real relationships rather than have to setup
triggers to enforce the referential integrety)

What is a "real" relationship? MS-SQL 2K gives more options, including
cascade, for referential action than MS-SQL 7 gives, but MS-SQL
Declarative Referential Integrity (DRI) has been available for almost 10
years, not only in version 7 but also in versions 6.5 and 6.

--
--
Lyle

To subject an enemy belligerent to an unfair trial, to charge him with
an unrecognized crime, or to vent on him our retributive emotions only
antagonizes the enemy nation and hinders the reconciliation necessary to
a peaceful world.

Justice Frank Murphy
Yamashita v. Styer, 327 U.S. 1 (1946)

[Bri]

Lyle Fairfield wrote:

Bri wrote:

but overall SQL 2K is an improvment over 7 (mostly that you can do
real relationships rather than have to setup triggers to enforce the
referential integrety)

What is a "real" relationship? MS-SQL 2K gives more options, including
cascade, for referential action than MS-SQL 7 gives, but MS-SQL
Declarative Referential Integrity (DRI) has been available for almost 10
years, not only in version 7 but also in versions 6.5 and 6.

I started with SQL Server 7 via the AC97 Upsizing Wizard. It had an
option for DRI, but I couldn't figure out how to work with it in
Enterprise Manager (my lack of experience with SQL Server I'm sure) so I
went with the Triggers option. Later, with SQL 2K I noticed the
Relationships and found them very intuitive to use (very similar to AC
relationships). My reference to "real" was to the Triggers.

--
Bri

[Trevor Best]

Bri wrote:

I started with SQL Server 7 via the AC97 Upsizing Wizard. It had an
option for DRI, but I couldn't figure out how to work with it in
Enterprise Manager (my lack of experience with SQL Server I'm sure) so I
went with the Triggers option. Later, with SQL 2K I noticed the
Relationships and found them very intuitive to use (very similar to AC
relationships). My reference to "real" was to the Triggers.

I think you probably fell foul b4 as you had cascading relationships,
which DRI didn't have b4 so the Upsizing wizard would use triggers instead.

--
[OO=00=OO]

[Bri]

Trevor Best wrote:

Bri wrote:

I started with SQL Server 7 via the AC97 Upsizing Wizard. It had an
option for DRI, but I couldn't figure out how to work with it in
Enterprise Manager (my lack of experience with SQL Server I'm sure) so
I went with the Triggers option. Later, with SQL 2K I noticed the
Relationships and found them very intuitive to use (very similar to AC
relationships). My reference to "real" was to the Triggers.

I think you probably fell foul b4 as you had cascading relationships,
which DRI didn't have b4 so the Upsizing wizard would use triggers instead.

Most of the PKs were Autonumber (Converted to Identity) so I didn't have
cascading. The wizard had the option of either. I tried various options
when converting the first time to see what the differences were. With
DRI, I couldn't find a place in EM to view what was created or to modify
them, so I moved on and tried the triggers option. While the triggers
were more difficult to work with, at least I could work with them.

--
Bri

[Trevor Best]

Bri wrote:

Most of the PKs were Autonumber (Converted to Identity) so I didn't have
cascading. The wizard had the option of either. I tried various options
when converting the first time to see what the differences were. With
DRI, I couldn't find a place in EM to view what was created or to modify
them, so I moved on and tried the triggers option. While the triggers
were more difficult to work with, at least I could work with them.

In EM it's the diagrams you want to look at.

--
[OO=00=OO]

[Steve Jorgensen]

On Fri, 20 May 2005 22:44:23 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Bri <no*@here.com> wrote in news:g85je.1416116$Xk.548934@pd7tw3no: ....Of course, I've had a lot of problems with this kind of thing in
Access 2K, in one client's app in particular, that I programmed in
A97 and converted to A2K for their use. That's how I found out all
sorts of things about little gotchas between A97 and A2K, where code

Note that a few (not most) of the things that were broken in A2K were fixed
again in A2K2 and above. For instance, in A2K, anything that relies on the
Access 97 behavior of a form's Recordsetclone property will break, and it will
be a real pain to fix. In A2K2, the old A97 behavior of Recordsetclone is
restored.

[Steve Jorgensen]

On Fri, 20 May 2005 23:38:27 GMT, Bri <no*@here.com> wrote:

....

Too many double negatives!

If Not DB Is Nothing Then :{)

I have a standard set of functions I add to my projects now to help eliminate
double-negatives. I think this vocabulary would make a good de facto standard
for VB/Access programming.

HasValue(...) = Not IsNull(...)
IsAssigned(...) = Not IsEmpty(...)
Exists(...) = Not ... Is Nothing
IsSupplied(...) = Not IsMissing(...)
HasText(...) = Len(...) > 0

[windandwaves]

Steve Jorgensen wrote:

On Fri, 20 May 2005 23:38:27 GMT, Bri <no*@here.com> wrote:

...

Too many double negatives!

If Not DB Is Nothing Then :{)

I have a standard set of functions I add to my projects now to help
eliminate double-negatives. I think this vocabulary would make a
good de facto standard for VB/Access programming.

HasValue(...) = Not IsNull(...)
IsAssigned(...) = Not IsEmpty(...)
Exists(...) = Not ... Is Nothing
IsSupplied(...) = Not IsMissing(...)
HasText(...) = Len(...) > 0

That is great Steve, that clarifies a lot of things for me. How exactly do
you use the above code? I must say that in PHP I really enjoyed the if
statement where

- is null
- 0
- ""
- empty
- nothing
- etc...
all equate to false and anything else is true, makes life a lot easier.

Cheers

- Nicolaas

[David W. Fenton]

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

On Fri, 20 May 2005 22:44:23 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Bri <no*@here.com> wrote in news:g85je.1416116$Xk.548934@pd7tw3no:

...

Of course, I've had a lot of problems with this kind of thing in
Access 2K, in one client's app in particular, that I programmed in
A97 and converted to A2K for their use. That's how I found out all
sorts of things about little gotchas between A97 and A2K, where
code

Note that a few (not most) of the things that were broken in A2K
were fixed again in A2K2 and above. For instance, in A2K,
anything that relies on the Access 97 behavior of a form's
Recordsetclone property will break, and it will be a real pain to
fix. In A2K2, the old A97 behavior of Recordsetclone is restored.

I've never seen anything with regard to Recordsetclone break in A2K,
and it's something I use frequently, but only for bookmark
navigation.

What areas of it break in A2K?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

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

On Fri, 20 May 2005 23:38:27 GMT, Bri <no*@here.com> wrote:

...

Too many double negatives!
If Not DB Is Nothing Then :{)

I have a standard set of functions I add to my projects now to
help eliminate double-negatives. I think this vocabulary would
make a good de facto standard for VB/Access programming.

HasValue(...) = Not IsNull(...)
IsAssigned(...) = Not IsEmpty(...)
Exists(...) = Not ... Is Nothing

That's not, strictly speaking, what Not Is nothing actually means.
IsSupplied(...) = Not IsMissing(...)
HasText(...) = Len(...) > 0

I don't have too much trouble with these until I end up with complex
Boolean expressions where I want to know that something is Not Not.
In one query-by-form interface, I had a mess of check boxes that
were OR'ed together, but they were *negatives*.

That code went through many rewrites. ;)

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Wed, 25 May 2005 03:14:04 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

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

On Fri, 20 May 2005 23:38:27 GMT, Bri <no*@here.com> wrote:

...

Too many double negatives!

If Not DB Is Nothing Then :{)
I have a standard set of functions I add to my projects now to
help eliminate double-negatives. I think this vocabulary would
make a good de facto standard for VB/Access programming.

HasValue(...) = Not IsNull(...)
IsAssigned(...) = Not IsEmpty(...)
Exists(...) = Not ... Is Nothing

That's not, strictly speaking, what Not Is nothing actually means.

You're right. That's the only one where the name doesn't really say something
sort of accurate about what it means. It is, however, a term for "something"
(except, perhaps "something"?) that's not already taken by one of the other
checks.

Perhaps, IsSomething would be better? Any other suggestions.

IsSupplied(...) = Not IsMissing(...)
HasText(...) = Len(...) > 0

I don't have too much trouble with these until I end up with complex
Boolean expressions where I want to know that something is Not Not.
In one query-by-form interface, I had a mess of check boxes that
were OR'ed together, but they were *negatives*.

That code went through many rewrites. ;)

Yeah - I hate those. Seems like they always come up once in a blue moon, and
there's no trivial (takes less than a month) way to engineer them out.

[Steve Jorgensen]

On Wed, 25 May 2005 03:11:54 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

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

On Fri, 20 May 2005 22:44:23 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Bri <no*@here.com> wrote in news:g85je.1416116$Xk.548934@pd7tw3no:

...

Of course, I've had a lot of problems with this kind of thing in
Access 2K, in one client's app in particular, that I programmed in
A97 and converted to A2K for their use. That's how I found out all
sorts of things about little gotchas between A97 and A2K, where
code

Note that a few (not most) of the things that were broken in A2K
were fixed again in A2K2 and above. For instance, in A2K,
anything that relies on the Access 97 behavior of a form's
Recordsetclone property will break, and it will be a real pain to
fix. In A2K2, the old A97 behavior of Recordsetclone is restored.

I've never seen anything with regard to Recordsetclone break in A2K,
and it's something I use frequently, but only for bookmark
navigation.

What areas of it break in A2K?

In my experience, in A2K, Recordsetclone is a synonym for Recordset, so if you
navigate in the recordset, the form navigates along with you. If you want to
find a record in the clone without moving the focus in the form, you have to
make a Clone of the Recordset. Unfortunately, it turns out that clone doesn't
behave exactly like Recordsetclone either, though I can't recall excactly how.

I had to fight with this when trying to use the code example for A97 to trap
server-side error messages in bound forms. It was a major engineering effort
that took about 4 days.

[Bri]

Steve Jorgensen wrote:

On Fri, 20 May 2005 23:38:27 GMT, Bri <no*@here.com> wrote:

...

Too many double negatives!

If Not DB Is Nothing Then :{)

I have a standard set of functions I add to my projects now to help eliminate
double-negatives. I think this vocabulary would make a good de facto standard
for VB/Access programming.

HasValue(...) = Not IsNull(...)
IsAssigned(...) = Not IsEmpty(...)
Exists(...) = Not ... Is Nothing
IsSupplied(...) = Not IsMissing(...)
HasText(...) = Len(...) > 0

Excellent!

Now taking this a step further, could a sub/function do the cleanup?

air code:

Sub CloseRS(rs as DAO.Recordset)
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
End Sub

Since the variable is By Ref this should work, but I haven't tried it yet.

--
Bri

[Steve Jorgensen]

On Wed, 25 May 2005 16:27:13 GMT, Bri <no*@here.com> wrote:

Steve Jorgensen wrote:

On Fri, 20 May 2005 23:38:27 GMT, Bri <no*@here.com> wrote:

...

Too many double negatives!

If Not DB Is Nothing Then :{)

I have a standard set of functions I add to my projects now to help eliminate
double-negatives. I think this vocabulary would make a good de facto standard
for VB/Access programming.

HasValue(...) = Not IsNull(...)
IsAssigned(...) = Not IsEmpty(...)
Exists(...) = Not ... Is Nothing
IsSupplied(...) = Not IsMissing(...)
HasText(...) = Len(...) > 0

Excellent!

Now taking this a step further, could a sub/function do the cleanup?

air code:

Sub CloseRS(rs as DAO.Recordset)
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
End Sub

Since the variable is By Ref this should work, but I haven't tried it yet.

Well, that has basically nothing to do with the double-negative issue, but
yes, it does work. I've used code like that in some of my projects, and the
byref rs variable does get set to Nothing in the calling procedure.

[Bri]

Trevor Best wrote:

Bri wrote:

Most of the PKs were Autonumber (Converted to Identity) so I didn't
have cascading. The wizard had the option of either. I tried various
options when converting the first time to see what the differences
were. With DRI, I couldn't find a place in EM to view what was created
or to modify them, so I moved on and tried the triggers option. While
the triggers were more difficult to work with, at least I could work
with them.

In EM it's the diagrams you want to look at.

I do remember thinking that they must be related, but when I picked the
DRI option it did not create a Diagram (like Access does) so I assumed
(perhaps incorrectly) that they were unrelated to each other. I'll have
to explore this more since I do still have a SQL 7 app out there.

Thanks for the tip.

--
Bri

[Trevor Best]

Bri wrote:

In EM it's the diagrams you want to look at.

I do remember thinking that they must be related, but when I picked the
DRI option it did not create a Diagram (like Access does) so I assumed
(perhaps incorrectly) that they were unrelated to each other. I'll have
to explore this more since I do still have a SQL 7 app out there.

Ah yes, the diagrams aren't created automatically but the relationships
are there non the less. Also there's a popup property box for tables
with tabs for indices and foreign keys, existing ones will appear here
and you can create new ones there but I find the diagram an easier place
to create relationships. Now if only the diagrams would link the tables
up by the keys instead of just attaching the lines anywhere on the table
borders...

--
[OO=00=OO]

[Bri]

Trevor Best wrote:

Bri wrote:

I do remember thinking that they must be related, but when I picked
the DRI option it did not create a Diagram (like Access does) so I
assumed (perhaps incorrectly) that they were unrelated to each other.
I'll have to explore this more since I do still have a SQL 7 app out
there.

Ah yes, the diagrams aren't created automatically but the relationships
are there non the less. Also there's a popup property box for tables
with tabs for indices and foreign keys, existing ones will appear here
and you can create new ones there but I find the diagram an easier place
to create relationships. Now if only the diagrams would link the tables
up by the keys instead of just attaching the lines anywhere on the table
borders...

Umm, I have found and used the Indexes many times, but I can't see the
Foreign Keys. How are you getting to that?

--
Bri

[Bri]

Steve Jorgensen wrote:

Well, that has basically nothing to do with the double-negative issue, but
yes, it does work. I've used code like that in some of my projects, and the
byref rs variable does get set to Nothing in the calling procedure.

True, but it was your functions that got me thinking along that new
line. I will try this out, anything to streamline code and reduce
redundancy. Thanks.

--
Bri

[David W. Fenton]

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

On Wed, 25 May 2005 03:11:54 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:34********************************@4ax.co m:

On Fri, 20 May 2005 22:44:23 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Bri <no*@here.com> wrote in
news:g85je.1416116$Xk.548934@pd7tw3no:
...
Of course, I've had a lot of problems with this kind of thing in
Access 2K, in one client's app in particular, that I programmed
in A97 and converted to A2K for their use. That's how I found
out all sorts of things about little gotchas between A97 and
A2K, where code

Note that a few (not most) of the things that were broken in A2K
were fixed again in A2K2 and above. For instance, in A2K,
anything that relies on the Access 97 behavior of a form's
Recordsetclone property will break, and it will be a real pain
to fix. In A2K2, the old A97 behavior of Recordsetclone is
restored.
I've never seen anything with regard to Recordsetclone break in
A2K, and it's something I use frequently, but only for bookmark
navigation.

What areas of it break in A2K?

In my experience, in A2K, Recordsetclone is a synonym for
Recordset, so if you navigate in the recordset, the form navigates
along with you. . . .

Er, I'm afraid you are wrong:

http://trigeminal.com/usenet/usenet022.asp?1033
. . . If you want to find a record in the clone without
moving the focus in the form, you have to make a Clone of the
Recordset. Unfortunately, it turns out that clone doesn't behave
exactly like Recordsetclone either, though I can't recall excactly
how.
I don't see why you'd do that. Recordsetclone still exists, and
works just like it always has.
I had to fight with this when trying to use the code example for
A97 to trap server-side error messages in bound forms. It was a
major engineering effort that took about 4 days.

After reading MichKa's article, are you sure you just weren't wrong
about it?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Thu, 26 May 2005 00:35:42 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:hp********************************@4ax.com : ....
Er, I'm afraid you are wrong:

http://trigeminal.com/usenet/usenet022.asp?1033

I could easily be wrong about the specifics - it's been a couple of years.
I'm certain, though, that there was something seriously wrong with
RecordsetClone in some circumstances in Access 2000 that were fine in 97 and
also in 2002. I discovered the problem when I developed and tested the code
successfully in 2002, and it would not run in 2000.

[Trevor Best]

Bri wrote:

Umm, I have found and used the Indexes many times, but I can't see the
Foreign Keys. How are you getting to that?

In Table design, there's a toolbar button for relationships.

--
[OO=00=OO]

[Trevor Best]

Steve Jorgensen wrote:

On Thu, 26 May 2005 00:35:42 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:hp********************************@4ax.co m:

...

Er, I'm afraid you are wrong:

http://trigeminal.com/usenet/usenet022.asp?1033

I could easily be wrong about the specifics - it's been a couple of years.
I'm certain, though, that there was something seriously wrong with
RecordsetClone in some circumstances in Access 2000 that were fine in 97 and
also in 2002. I discovered the problem when I developed and tested the code
successfully in 2002, and it would not run in 2000.

You might have run into the "feature" where you have more than one
recordset variable set to .RecordsetClone, move one and it moves the other.

--
[OO=00=OO]

[David W. Fenton]

Trevor Best <no****@besty.org.uk> wrote in
news:42***********************@news.zen.co.uk:

Steve Jorgensen wrote:

On Thu, 26 May 2005 00:35:42 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

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

...

Er, I'm afraid you are wrong:

http://trigeminal.com/usenet/usenet022.asp?1033

I could easily be wrong about the specifics - it's been a couple
of years. I'm certain, though, that there was something seriously
wrong with RecordsetClone in some circumstances in Access 2000
that were fine in 97 and also in 2002. I discovered the problem
when I developed and tested the code successfully in 2002, and it
would not run in 2000.

You might have run into the "feature" where you have more than one
recordset variable set to .RecordsetClone, move one and it moves
the other.

Er, wouldn't that be the case in A97, too?

And, given the concept of what .RecordsetClone is supposed to be
*for*, isn't that the way you'd *want* it to work?

I've never understood the use of recordset variables with
..RecordsetClone. What, exactly, are you accomplishing by doing that,
except complicating things?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bri]

Trevor Best wrote:

Bri wrote:

Umm, I have found and used the Indexes many times, but I can't see the
Foreign Keys. How are you getting to that?

In Table design, there's a toolbar button for relationships.

Found it. It is a Tab on the Table Properties Button. I never noticed it
before and I thought I had looked at every possible place for it. Thanks.

--
Bri

[Trevor Best]

David W. Fenton wrote:

You might have run into the "feature" where you have more than one
recordset variable set to .RecordsetClone, move one and it moves
the other.

Er, wouldn't that be the case in A97, too?

I haven't used that for about 6 years. :-)
And, given the concept of what .RecordsetClone is supposed to be
*for*, isn't that the way you'd *want* it to work?
<fx: long sigh> That's why I mentioned it. It's more likely that it's
that what happened to Steve than what he said (moving recordsetclone
moved the form as well, see below, that requires a bit more effort)
I've never understood the use of recordset variables with
.RecordsetClone. What, exactly, are you accomplishing by doing that,
except complicating things?

Find feature on a form, e.g.

set rst = me.recordsetclone
rst.findfirst strCriteria
if rst.nomatch then
msgbox "not found"
else
me.bookmark = rst.bookmark
end if
....

--
[OO=00=OO]