Welcome to SPN

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

Sign Up Now!

What am I doing wrong in the VBA code?

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

  1. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    Thanks very much for the replies. The problem, however, is that the
    SRnum field is guaranteed not to be null or an empty string. I get the
    Invalid Use of Null error when the EOF is reached. For some reason it's

    not recognizing that the EOF is reached and it tries to obtain the
    SRnum field again and assign it to the variable. How is this happening?


    Previously posting:
    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
    Exit Do
    Else
    rstSM.MoveNext
    End If
    Loop
    rstHD.MoveNext
    Loop
    rstHD.Close
    rstSM.Close
    Set rstHD = Nothing
    Set rstSM = Nothing


    If I add a check for null before assigning srNumSM = rstSM![srNum] like

    so:


    Do Until rstSM.EOF
    If IsNull(rstSM![SRnum]) Then
    Exit Do
    End If
    srNumSM = rstSM![SRnum]


    And then it works but it doesn't successfully find the duplicates and
    delete the duplicate row. Any help would be greatly appreciated.
     
  2. Loading...

    Similar Threads Forum Date
    How To Meditate (dhyaan / Simran) ? Am I Doing It All Wrong. I Can't Concentrate And Feel Sleepy Intellectual Articles Mar 25, 2016
    Am I doing something wrong? Sikh Youth Nov 23, 2013
    Conentrating while Doing Paath? Blogs Oct 22, 2015
    Covering Kesh While Eating And Doing Other Activities Questions and Answers Mar 27, 2015
    Doing parkash of SGGS at home? Questions and Answers Mar 31, 2014

  3. SusanV

    SusanV
    Expand Collapse
    Guest

    Maybe try Do while Not ?

    "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    news:1150212817.512887.113880@p79g2000cwp.googlegroups.com...
    > Thanks very much for the replies. The problem, however, is that the
    > SRnum field is guaranteed not to be null or an empty string. I get the
    > Invalid Use of Null error when the EOF is reached. For some reason it's
    >
    > not recognizing that the EOF is reached and it tries to obtain the
    > SRnum field again and assign it to the variable. How is this happening?
    >
    >
    > Previously posting:
    > 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
    > Exit Do
    > Else
    > rstSM.MoveNext
    > End If
    > Loop
    > rstHD.MoveNext
    > Loop
    > rstHD.Close
    > rstSM.Close
    > Set rstHD = Nothing
    > Set rstSM = Nothing
    >
    >
    > If I add a check for null before assigning srNumSM = rstSM![srNum] like
    >
    > so:
    >
    >
    > Do Until rstSM.EOF
    > If IsNull(rstSM![SRnum]) Then
    > Exit Do
    > End If
    > srNumSM = rstSM![SRnum]
    >
    >
    > And then it works but it doesn't successfully find the duplicates and
    > delete the duplicate row. Any help would be greatly appreciated.
    >
     
  4. GVR_Mike

    GVR_Mike
    Expand Collapse
    Guest

    Good thought, I tried that too and still got Invalid Use of Null.

    SusanV wrote:
    > Maybe try Do while Not ?
    >
    > "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    > news:1150212817.512887.113880@p79g2000cwp.googlegroups.com...
    > > Thanks very much for the replies. The problem, however, is that the
    > > SRnum field is guaranteed not to be null or an empty string. I get the
    > > Invalid Use of Null error when the EOF is reached. For some reason it's
    > >
    > > not recognizing that the EOF is reached and it tries to obtain the
    > > SRnum field again and assign it to the variable. How is this happening?
    > >
    > >
    > > Previously posting:
    > > 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
    > > Exit Do
    > > Else
    > > rstSM.MoveNext
    > > End If
    > > Loop
    > > rstHD.MoveNext
    > > Loop
    > > rstHD.Close
    > > rstSM.Close
    > > Set rstHD = Nothing
    > > Set rstSM = Nothing
    > >
    > >
    > > If I add a check for null before assigning srNumSM = rstSM![srNum] like
    > >
    > > so:
    > >
    > >
    > > Do Until rstSM.EOF
    > > If IsNull(rstSM![SRnum]) Then
    > > Exit Do
    > > End If
    > > srNumSM = rstSM![SRnum]
    > >
    > >
    > > And then it works but it doesn't successfully find the duplicates and
    > > delete the duplicate row. Any help would be greatly appreciated.
    > >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    I know you've got this working using a query, but I just thought I'd add
    some comments here.

    If it works when you've got

    If IsNull(rstSM![SRnum]) Then
    Exit Do
    End If

    in, that seems to indicate that your assumption that the SRnum field is
    guaranteed not to be null or an empty string is incorrect.

    There's actually no need to be assigning the values to variables anyhow:

    rstHD.MoveFirst
    Do Until rstHD.EOF
    rstSM.MoveFirst
    Do Until rstSM.EOF
    If (rstHD![srNum] = rstSM![srNum]) Or _
    (IsNull(rstHD![srNum]) And IsNull(rstSM![srNum]) Then
    rstHD.Delete
    Exit Do
    End If
    rstSM.MoveNext
    Loop
    rstHD.MoveNext
    Loop
    rstHD.Close
    rstSM.Close
    Set rstHD = Nothing
    Set rstSM = Nothing


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


    "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    news:1150212817.512887.113880@p79g2000cwp.googlegroups.com...
    > Thanks very much for the replies. The problem, however, is that the
    > SRnum field is guaranteed not to be null or an empty string. I get the
    > Invalid Use of Null error when the EOF is reached. For some reason it's
    >
    > not recognizing that the EOF is reached and it tries to obtain the
    > SRnum field again and assign it to the variable. How is this happening?
    >
    >
    > Previously posting:
    > 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
    > Exit Do
    > Else
    > rstSM.MoveNext
    > End If
    > Loop
    > rstHD.MoveNext
    > Loop
    > rstHD.Close
    > rstSM.Close
    > Set rstHD = Nothing
    > Set rstSM = Nothing
    >
    >
    > If I add a check for null before assigning srNumSM = rstSM![srNum] like
    >
    > so:
    >
    >
    > Do Until rstSM.EOF
    > If IsNull(rstSM![SRnum]) Then
    > Exit Do
    > End If
    > srNumSM = rstSM![SRnum]
    >
    >
    > And then it works but it doesn't successfully find the duplicates and
    > delete the duplicate row. Any help would be greatly appreciated.
    >
     
  6. SusanV

    SusanV
    Expand Collapse
    Guest

    Ugh. I ran into this with another function a while back and could NOT find
    what was causing it - but I think I got around it by using a counter instead
    of EOF. ..

    =/


    "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    news:1150225433.201918.147120@g10g2000cwb.googlegroups.com...
    > Good thought, I tried that too and still got Invalid Use of Null.
    >
    > SusanV wrote:
    >> Maybe try Do while Not ?
    >>
    >> "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    >> news:1150212817.512887.113880@p79g2000cwp.googlegroups.com...
    >> > Thanks very much for the replies. The problem, however, is that the
    >> > SRnum field is guaranteed not to be null or an empty string. I get the
    >> > Invalid Use of Null error when the EOF is reached. For some reason it's
    >> >
    >> > not recognizing that the EOF is reached and it tries to obtain the
    >> > SRnum field again and assign it to the variable. How is this happening?
    >> >
    >> >
    >> > Previously posting:
    >> > 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
    >> > Exit Do
    >> > Else
    >> > rstSM.MoveNext
    >> > End If
    >> > Loop
    >> > rstHD.MoveNext
    >> > Loop
    >> > rstHD.Close
    >> > rstSM.Close
    >> > Set rstHD = Nothing
    >> > Set rstSM = Nothing
    >> >
    >> >
    >> > If I add a check for null before assigning srNumSM = rstSM![srNum] like
    >> >
    >> > so:
    >> >
    >> >
    >> > Do Until rstSM.EOF
    >> > If IsNull(rstSM![SRnum]) Then
    >> > Exit Do
    >> > End If
    >> > srNumSM = rstSM![SRnum]
    >> >
    >> >
    >> > And then it works but it doesn't successfully find the duplicates and
    >> > delete the duplicate row. Any help would be greatly appreciated.
    >> >

    >
     

Share This Page