[AccessD] Hi I am back and stuck

A.D.TEJPAL adtp at hotmail.com
Mon Jun 18 02:03:30 CDT 2007


Darren,

    There is no contradiction. When you invoke a recordset (or its clone) directly as form's property, you do not need to set any reference at all - neither to DAO nor to ADO. This is because you are not creating an object related to either of these two libraries, but using an intrinsic feature of the form itself.

    In fact, a single statement, as given below would suffice. There is no need to use the clone and no reference to DAO or ADO is required. If there is no match, there will be no error message (you will simply land up in the first record).
    
    Me.Recordset.FindFirst "ID = " & Nz(Me.CboID, 0)

    Note - Use of Nz() function is necessary - in order to avoid error 3077 "Syntax error (missing operator) in expression" in case of null value.

    As correctly pointed out by Arthur, as soon as you attempt to set an explicit recordset object (say rst) for manipulating the form's recordset or its clone, it will be found that FindFirst method is available only for a DAO recordset and setting a reference to DAO is necessary. If due to some reason your db has reference to both DAO & ADO, you should qualify the recordset object suitably (eg. Dim rst As DAO.Recordset).

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: Arthur Fuller 
  To: Access Developers discussion and problem solving 
  Sent: Monday, June 18, 2007 06:48
  Subject: Re: [AccessD] Hi I am back and stuck


  I don't know. I defer to the really smart people here.

  A.


  On 6/17/07, Darren D <darrend at nimble.com.au> wrote:
  >
  > Howdy
  >
  > Thanks for this
  >
  > No fault here Arthur - I actually look forward to your posts
  >
  > I am in fact most ignorant of recordset objects
  >
  > In the list of references - DAO is not mentioned as I removed it - But the FindFirst still works!
  >
  > This is the bit I did not understand??
  >
  > If it is a DAO capability - why does it still work after I remove DAO from the ref lists
  >
  > I even Compacted and repaired the dB - and re-opened it after removal of DAO
  >
  > Still the combo After_Update event fired - no errors - and it even found the relevant record
  >
  > Using
  > ~~~~~~~~~~~~~~~~~~~~
  > Me.RecordsetClone.FindFirst "SomePKinThedB =" & Me![cmbSomeCoolCombo]
  > Me.Bookmark = Me.RecordsetClone.Bookmark
  > ~~~~~~~~~~~~~~~~~~~~
  >
  > Darren
  > ------------------
  > T: 0424 696 433
  >
  > -----Original Message-----
  > From: Arthur Fuller [mailto:fuller.artful at gmail.com]
  > Sent: Monday, 18 June 2007 10:45 AM
  > To: Access Developers discussion and problem solving
  > Subject: Re: [AccessD] Hi I am back and stuck
  >
  > Forgive me. I thought I made it clear, but obviously I failed. My fault.
  >
  > 1. If you are going to mix ADO and DAO, then you should declare each object explicitly, so Access knows which one you mean. (I gave an example creating two recordsets, one of each type.) Should you try rs2.FindFIrst(...) you will get busted. Should you try rs1.Find(...) you will get busted.
  >
  > 2. You can mix and match, and I do now and then, but most often I make a global (app-centric) decision as to which side of the street I'm going to walk. Thereafter, I stick to it, and no problems. I don't care which side you choose, but I recommend that you choose one and stick to it throughout. Then you don't have to bother with with "ADODB" prefix or its altenative.
  >
  > The list as you presented it does not include DAO, and therefore cannot execute FindFirst. You Must include the other library as previously described, DAO 3.6 or whatever the latest version is.
  >
  > I hope this didn't come across as pedantic. (I'm conscious of this since receiving a few emails lately.) All I mean to say is that if you want to use "FindFirst" then you MUST include a reference to DAO. Otherwise Access will look at the ADO library, fail to find "FindFirst", and bust you.
  >
  > hth,
  > Arthur
  >
  >
  > On 6/17/07, Darren D <darrend at nimble.com.au> wrote:
  > >
  > > Hi Arthur
  > >
  > > Thanks for this
  > >
  > > Just to try
  > >
  > > 1       I made sure the DAO was above the ActiveX Data Objects ref in the list All worked well - as expected
  > >
  > > 2       Made the ActiveX Data Objects Ref higher than the DAO
  > >        compacted and repaired and re-opened - it still worked
  > >
  > > 3       So to take it to the fullest level I removed the DAO ref
  > > altogether Compacted repaired and re-opened
  > >        (Couldn't compile because of explicit DAO references)
  > >        but the (search feature) combo still worked
  > >
  > > What gives?
  > >
  > > Full Ref list =
  > > 1       Visual Basic for Applications
  > > 2       Microsoft Access 11 Object Library
  > > 3       OLE Automation
  > > 4       Microsoft ActiveX data Objects 2.1 Library
  > >
  > > Darren
  > > ------------------
  > > T: 0424 696 433
  > >
  > > -----Original Message-----
  > > From: Arthur Fuller [mailto:fuller.artful at gmail.com]
  > > Sent: Monday, 18 June 2007 9:48 AM
  > > To: Access Developers discussion and problem solving
  > > Subject: Re: [AccessD] Hi I am back and stuck
  > >
  > > As I mentioned previously, this code will get busted if the default data access method is ADO. There is no FindFirst in ADO; only Find.
  > >
  > > A.
  > >
  > >
  > > On 6/17/07, Darren D <darrend at nimble.com.au> wrote:
  > > >
  > > > Hi Joe
  > > >
  > > > In my code I use just the following - without referencing recordsets
  > > >
  > > > Private Sub SomeCombo_AfterUpdate()> > >
  > > >       Me.RecordsetClone.FindFirst "SomePKinThedB =" &
  > > > Me![cmbSomeCoolCombo]
  > > >       Me.Bookmark = Me.RecordsetClone.Bookmark
  > > > End sub
  > > >
  > > > Pretty much the same as what you have - slightly different though
  > > >
  > > > That's it
  > > >
  > > > Darren


More information about the AccessD mailing list