Object Variable 1

[yamafopa]

Just moving up from Access 97 and trying 2K. I am used to using the FindFirst method of the RecordsetClone object in order to do form navigation with a combo box. I notice that the wizard in 2K writes the following code in order to accomplish this...

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[req_no] = " & Str(Me![Combo98])
Me.Bookmark = rs.Bookmark

Set rs = Me.Recordset.Clone

My questions are;

1. Why dim the recordset as Object rather than as ADODB.Recordset?
2. Why not use rs.close and/or Set rs = nothing

If if was me doing this I would write...

On Error GoTo EH

Dim rst As ADODB.Recordset

Set rst = Me.Recordset.Clone
rst.Find "req_no = " & Str(Me!cboRequestCode)
Me.Bookmark = rst.Bookmark
rst.Close

SeeYa:
Set rst = Nothing
Exit Sub

EH:
MsgBox Err.Number & " - " & Err.Description
Resume SeeYa

Comments??
Thanks, Chris

 

[jimb0ne]

I'd just say that the wizard writes hurtin code, thats why you should do everything yourself. I guess it works, but you're right about the way it should be written.

 

[Rolliee]

ACCESS '97 use the DAO protocol as default and 2000 uses ADO. Using recordset as an object is a poor work-around, In the design mode open Tools/References and check ms DAO 3.6 Library and you can use DAO.recordset and the Helps are much better. The wiz takes the easy way out. WROX publisher in their newest books recommend using DAO and I have found it to be true. There are some queries for which the findfirst does not work. In these, a simple set of VB code lines will do the same without the agony.

rollie@bwsys.net

 

[yamafopa]

Thanks for the input.

Good suggestion on the DAO ref. I could easily do that and hadn't thought of it. Still curious as to why the use the variable of type Object and why they don't destroy it when that are finished. Perhaps it is as jimb says, crappy coding by the manufacture. Wouldn't be the first time, I guess, but still surprising....

any other thoughts???

yamafopa!

 

[Rolliee]

object does not trigger the compiler. It is like calling a variable a variant. It has the ease of being non-specific but also the hazards.

Rollie

 

[alr0]

Hey Rollie,

This is the first recommendation I have seen to use DAO for new work in Acc2k+.

I do not have any recent Wrox books. Would you please point us to the important references.

My problems involve .ADPs and ODBC connections to SQLServer with Access 2k and up. Is DAO a better choice in some of these situations?

Thanks,

al


_____________________________________
If information is the currency of democracy then, secrecy allows one to print counterfeit money.

 

[Rolliee]

The book on ACCESS Programming by WROX is great. I also like the one on VB but the former is better. DAO seems more straighforward to me. There have to be some advantages in ADO but I have bot found them. I believe there is a WROX book on Databases that I found good - describing multi tier applications. The present problem is that I am in Florida for the cold two winter months and my books are back in Illinois - the land of present snow.

Rollie