Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Invalid Use of Null

Discussion in 'Information Technology' started by GVR_Mike, Jul 28, 2006.

Tags:
  1. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    Can someone please look at my code and tell me what I'm doing wrong, I
    keep getting an Invalid Use of Null error. I'm new to VBA so please
    forgive me. Basically I have 2 recordsets of 2 different tables. I'm
    trying to see if any records in rstHD are duplicated in rstSM, if so
    then delete the record from rstHD.

    rstHD.MoveFirst
    Do Until rstHD.EOF
    rstSM.MoveFirst
    srNumHD = rstHD![srNum]
    Do Until rstSM.EOF
    srNumSM = rstSM![srNum]
    If srNumHD = srNumSM Then
    rstHD.Delete
    rstHD.MoveNext
    Exit Do
    Else
    rstSM.MoveNext
    End If
    Loop
    Loop
    rstHD.Close
    rstSM.Close
    Set rstHD = Nothing
    Set rstSM = Nothing
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh rendered invalid in ’84 riots seeks justice (Gulf Times) Breaking News Mar 14, 2008
    Sikh News 23 years later, invalid Sikh seeks justice (Calcutta News) Breaking News Mar 13, 2008
    Sikh News 23 years later, invalid Sikh seeks justice (New Kerala) Breaking News Mar 13, 2008
    Sikh News 23 years later, invalid Sikh seeks justice (Hindustan Times) Breaking News Mar 13, 2008
    Sikh News Sikh woman's forced marriage annulled in Britain (New Kerala) Breaking News Oct 22, 2008

  3. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    Whooops, had my rstHD.MoveNext in the wrong place but that's still not
    where my error is, see following code:

    rstHD.MoveFirst
    Do Until rstHD.EOF
    rstSM.MoveFirst
    srNumHD = rstHD![srNum]
    Do Until rstSM.EOF
    srNumSM = rstSM![srNum]
    If srNumHD = srNumSM Then
    rstHD.Delete
    Exit Do
    Else
    rstSM.MoveNext
    End If
    Loop
    rstHD.MoveNext 'This was moved from If statement in last
    posting
    Loop
    rstHD.Close
    rstSM.Close
    Set rstHD = Nothing
    Set rstSM = Nothing
     
  4. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    What's wierd is if I start only a few records from the end of rstSM
    instead of at the beginning then it works just fine. Instead of doing
    rstSM.MoveFirst I do a rstSM.MoveLast then a few rstSM.MovePrevious and
    it works perfectly. I don't get it...

    Please help!!
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    You'd be far better off using a Delete statement, rather than looping
    through 2 recordsets.

    DELETE FROM HD
    WHERE HD.ID IN (SELECT ID FROM SM)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    news:1150155867.871533.149830@c74g2000cwc.googlegroups.com...
    > Can someone please look at my code and tell me what I'm doing wrong, I
    > keep getting an Invalid Use of Null error. I'm new to VBA so please
    > forgive me. Basically I have 2 recordsets of 2 different tables. I'm
    > trying to see if any records in rstHD are duplicated in rstSM, if so
    > then delete the record from rstHD.
    >
    > rstHD.MoveFirst
    > Do Until rstHD.EOF
    > rstSM.MoveFirst
    > srNumHD = rstHD![srNum]
    > Do Until rstSM.EOF
    > srNumSM = rstSM![srNum]
    > If srNumHD = srNumSM Then
    > rstHD.Delete
    > rstHD.MoveNext
    > Exit Do
    > Else
    > rstSM.MoveNext
    > End If
    > Loop
    > Loop
    > rstHD.Close
    > rstSM.Close
    > Set rstHD = Nothing
    > Set rstSM = Nothing
    >
     
  6. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    Is that an SQL statement? So I'd write:

    DoCmd.RunSQL "DELETE FROM rstHD WHERE rstHD.srNum IN (SELECT srNum FROM
    rstSM)"

    Is that correct?


    Douglas J. Steele wrote:
    > You'd be far better off using a Delete statement, rather than looping
    > through 2 recordsets.
    >
    > DELETE FROM HD
    > WHERE HD.ID IN (SELECT ID FROM SM)
     
  7. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    Actually I should use the table names I guess like so?

    DoCmd.RunSQL "DELETE FROM TBLHDRandSR WHERE TBLHDRandSR.SRnum IN
    (SELECT SRnum FROM TBLSurveymain)"

    Is that correct?


    > DoCmd.RunSQL "DELETE FROM rstHD WHERE rstHD.srNum IN (SELECT srNum FROM
    > rstSM)"
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Looks right. Try on a copy of your database first, of course, just in
    case...

    Of course, if you use RunSQL, you're going to get the "You are about to
    delete n records..." prompt. If you don't want that, you can either put
    DoCmd.SetWarnings False before you run the statement (and DoCmd.SetWarnings
    True after), or use:

    CurrentDb.Execute "DELETE FROM TBLHDRandSR WHERE TBLHDRandSR.SRnum IN
    (SELECT SRnum FROM TBLSurveymain)", dbFailOnError

    The use of the dbFailOnError parameter means that a trappable error will be
    raised if there are any problems.

    For the record, it's almost always better to use SQL statements than to loop
    through recordsets.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    news:1150161682.874526.253430@g10g2000cwb.googlegroups.com...
    > Actually I should use the table names I guess like so?
    >
    > DoCmd.RunSQL "DELETE FROM TBLHDRandSR WHERE TBLHDRandSR.SRnum IN
    > (SELECT SRnum FROM TBLSurveymain)"
    >
    > Is that correct?
    >
    >
    > > DoCmd.RunSQL "DELETE FROM rstHD WHERE rstHD.srNum IN (SELECT srNum FROM
    > > rstSM)"

    >
     
  9. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    Looks like what I need, I'll give it a shot when my boss stops buggin
    me about other things. Thanks a bunch Doug!


    Douglas J Steele wrote:
    > Looks right. Try on a copy of your database first, of course, just in
    > case...
    >
    > Of course, if you use RunSQL, you're going to get the "You are about to
    > delete n records..." prompt. If you don't want that, you can either put
    > DoCmd.SetWarnings False before you run the statement (and DoCmd.SetWarnings
    > True after), or use:
    >
    > CurrentDb.Execute "DELETE FROM TBLHDRandSR WHERE TBLHDRandSR.SRnum IN
    > (SELECT SRnum FROM TBLSurveymain)", dbFailOnError
    >
    > The use of the dbFailOnError parameter means that a trappable error will be
    > raised if there are any problems.
    >
    > For the record, it's almost always better to use SQL statements than to loop
    > through recordsets.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    > news:1150161682.874526.253430@g10g2000cwb.googlegroups.com...
    > > Actually I should use the table names I guess like so?
    > >
    > > DoCmd.RunSQL "DELETE FROM TBLHDRandSR WHERE TBLHDRandSR.SRnum IN
    > > (SELECT SRnum FROM TBLSurveymain)"
    > >
    > > Is that correct?
    > >
    > >
    > > > DoCmd.RunSQL "DELETE FROM rstHD WHERE rstHD.srNum IN (SELECT srNum FROM
    > > > rstSM)"

    > >
     
  10. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    That worked like a charm. You and Allen Browne are geniuses!!! Quite
    worth of the MVP title ;) Thanks again.


    Douglas J Steele wrote:
    > Looks right. Try on a copy of your database first, of course, just in
    > case...
    >
    > Of course, if you use RunSQL, you're going to get the "You are about to
    > delete n records..." prompt. If you don't want that, you can either put
    > DoCmd.SetWarnings False before you run the statement (and DoCmd.SetWarnings
    > True after), or use:
    >
    > CurrentDb.Execute "DELETE FROM TBLHDRandSR WHERE TBLHDRandSR.SRnum IN
    > (SELECT SRnum FROM TBLSurveymain)", dbFailOnError
    >
    > The use of the dbFailOnError parameter means that a trappable error will be
    > raised if there are any problems.
    >
    > For the record, it's almost always better to use SQL statements than to loop
    > through recordsets.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    > news:1150161682.874526.253430@g10g2000cwb.googlegroups.com...
    > > Actually I should use the table names I guess like so?
    > >
    > > DoCmd.RunSQL "DELETE FROM TBLHDRandSR WHERE TBLHDRandSR.SRnum IN
    > > (SELECT SRnum FROM TBLSurveymain)"
    > >
    > > Is that correct?
    > >
    > >
    > > > DoCmd.RunSQL "DELETE FROM rstHD WHERE rstHD.srNum IN (SELECT srNum FROM
    > > > rstSM)"

    > >
     

Share This Page