Questions about 'locating'-code

[Guest]

To move to specific record number on my form I use the following borrowed
code (snippet):

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "ID_address = " & fldSearch
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox ("ID-number not found")
Exit Sub
End If
Set rs = Nothing

I have a few questions about it:
1. Is there a shorter way of coding to move to a specific record in a form?
2. What does 'set rs = Nothing' actually do? What can happen if you omit it?

Thanks,
John

 

[Baz]

You can do it in one line by operating directly on the form's recordset,
something like this:

Me.Recordset.FindFirst "ID_address = " & fldSearch

Please be aware, however, that if the record is not found then you will be
moved to the first record! Using the RecordsetClone as per the code you
posted lets you know whether the sought-for record is actually there, and
lets you decide what to do if it isn't.

'Set rs = Nothing' destroys the recordset object and releases the memory
which was allocated to it. If you omit it, then the object will get
destroyed anyway when the object variable rs goes out of scope. There have
been bugs in Access in the past which made it essential to explicitly
destroy objects, and purists still prefer to do so, but it will probably not
hurt you now if you omit it (unless you encounter one of said purists, in
which case you might get a tongue-lashing!)

 

[Guest]

Thanks Baz. It works!
John

You can do it in one line by operating directly on the form's recordset,
something like this:

Me.Recordset.FindFirst "ID_address = " & fldSearch

Please be aware, however, that if the record is not found then you will be
moved to the first record! Using the RecordsetClone as per the code you
posted lets you know whether the sought-for record is actually there, and
lets you decide what to do if it isn't.

'Set rs = Nothing' destroys the recordset object and releases the memory
which was allocated to it. If you omit it, then the object will get
destroyed anyway when the object variable rs goes out of scope. There
have been bugs in Access in the past which made it essential to explicitly
destroy objects, and purists still prefer to do so, but it will probably
not hurt you now if you omit it (unless you encounter one of said purists,
in which case you might get a tongue-lashing!)

 

[George Nicholson]

but it will probably not hurt you now if you omit it (unless you encounter

one of said purists, in which case you might get a tongue-lashing!)

well:
1) if "probably" is a satisfactory standard for you, go for it. My personal
opinion is that when my app starts to act screwy I like to *know* its due to
reasons *other* than unreleased objects that I was simply too lazy to close.
2) depends on what version of Access you are using, doesn't it? And whether
you can guarantee that your code will never, ever, ever be used in an older
version.

not a purist, but far from foolish

 

[Baz]

My assessment is based on more years of continuous Access development than I
care to remember, and an extensive range of very big working applications
out in the wild in various Access versions, some of them live without issues
for many years. Many of them make extensive use of class modules and form
instances, as a result of which I suspect I use far more object variables
than the vast majority of Access programmers who do little more than rustle
up the occasional DAO recordset. I have encountered no problems with object
variables going out of scope since the well-known bugs in Access 97
(although I can't speak for Access 2000, 'cos I skipped it). In my opinion,
the need to explicitly destroy object variables is a shibboleth, but if it
makes you happy then that's fine by me, and the OP can make up his own mind.

I can't guarantee that my code will never be used in an older version, but I
can guarantee that I will never have to support it. Using the version of
Access that I specify is a condition for any system that I supply, and
anyone who uses a different version (older or newer) without me first having
validated and approved it will not get support for it unless they can
replicate any problems in the agreed version.