Re: Validating data before adding a recordset

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Brendan Reynolds (brenreyn)
Date: 08/10/04 

Date: Tue, 10 Aug 2004 15:23:59 +0100

Well spotted Tim - the recordset is actually closed at that point! See below
...

If DCount("*", "tbldrivers", "drvssn = """ &
Me.drvssn & """") > 0 Then
  ' Duplicate record found***
  MsgBox "The S.S.N. you have entered is in already used"
  drvssn.SetFocus

  rctDriverAdd.Close <--- recordset closed

  db.Close
  Else
  ' Do nothing, record is not a duplicate***
  Exit Sub
End If

rctDriverAdd.AddNew <--- attempt to call AddNew on closed recordset 

-- 
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
"Tim Ferguson" <FergusonTG@softhome.net> wrote in message
news:Xns954197BDD34CDgarbleme4455656@207.46.248.16...
> "Walter" <anonymous@discussions.microsoft.com> wrote in
> news:2fad01c47e95$a6d26650$a301280a@phx.gbl:
>
> > Using DCount was suggested, but when I add this code, I
> > get a runtime error 3420 Object Invalid or no longer set.
> >
> > The debugger references my line of code:
> > rctDriverAdd.AddNew
> >
>
> Well, in that case the problem is with the rctDriverAdd object, not the
> DCount() call...
>
> I suspect that it is not being opened legally but I cannot remember the
> last time I tried to open a dbOpenTable recordset so I don't know why or
> how it might fail. What are you opening it for anyway? You don't seem to
do
> anything with it except for the .Addnew at the end and that would be
> quicker and safer with a "INSERT INTO ..." command anyway. All the
> recordset seems to be doing is slowing down the procedure and screwing the
> network. If you really want to do it this way, you'll need some kind of
>
>   If rctDriverAdd Is Nothing Then ...
>
> but I don't know what you'd do if it is indeed empty.
>
> Hope that helps
>
>
> Tim F
>
>
>

Relevant Pages

  • Re: ADO Recordset.Filter in Excel 2002
    ... Tim ... Dim RS As ADODB.Recordset ... Dim oRS As ADODB.Recordset ... 'Populate the Recordset object with a SQL query ...
    (microsoft.public.excel.programming)
  • Re: DIM and Set db once per module
    ... Const qdfBirthdaysDue = "Birthdays" ... Dim dbe, db, ss ' as DBEngine, as Database, as Recordset ... "Tim Ferguson" wrote: ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO Recordset.Filter in Excel 2002
    ... Tim Williams ... > I used the 4guys example for a disconnected RS - as it in Excel VBA I had ... > I tried typing the returned RS as Recordset, ... >>> Dash ...
    (microsoft.public.excel.programming)
  • RE: assign values from 1st tbl to 2nd tbl
    ... > Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset ... > About the empty table, check if you didn't forget the line MyRec2.Update ... >>> Tim ...
    (microsoft.public.access.modulesdaovba)
  • preventing duplicate record entry
    ... pulling data from a Products table that has many fields ... that do lookups to other tables. ... Are found in any other recordset THEN ... MsgBox "Duplicate Record" ...
    (comp.databases.ms-access)