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 this VBA code?

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

  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
    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. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    If you specifically want to be able to have the code continue even when the
    rstSM![SRnum] field has a Null value in it, then you'll need to use a
    Variant variable in place of srNumHD variable (which I assume is a String
    variable).

    --

    Ken Snell
    <MS ACCESS MVP>

    "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    news:1150167653.246832.271570@h76g2000cwa.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
    > 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. Grinder

    Grinder
    Expand Collapse
    Guest

    GVR_Mike wrote:
    > 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.


    I'm a little rusty, but I'll give it a shot. "Invalid Use of Null"
    happens when you try to treat a Null value as if it were valued, like a
    string, or a double, etc. Remember, any field in a database can be
    null, even if it is defined as a specific type, such as string, double, etc.

    > rstHD.MoveFirst
    > Do Until rstHD.EOF
    > rstSM.MoveFirst
    > srNumHD = rstHD![srNum]


    Ok, if srNumHD is a string, and the recordset field value is Null,
    you'll get an "Invalid Use of Null"

    There's a trick you can use, presuming it still works, that would
    resolve the error:

    srNumHD = "" & rstHD![srNum]

    That forces a concatenation of a string type with the field, which will
    be converted to a string if at all possible. Even though this works, I
    think it's better to be explicit:

    If IsNull(rstHd.Fields("srNum").Value) Then
    srNumHD = ""
    Else
    srNumHD = rstHd.Fields("srNum").Value
    End If

    It makes the code explicit, but you'll have to decide if that's a good
    thing. Sometimes people will wrap this logic into a short function names
    something like FieldValueToString(), to keep their code tidy *and* explicit.

    > Do Until rstSM.EOF
    > srNumSM = rstSM![srNum]


    Same business here with srNumSM.

    > 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.


    It sounds like your srNumSM is the problem. Make an explicit conversion
    as previously outlined, and you should be good to go. Be a little
    cautious, however, that you're making a *reductive* conversion. In some
    databases, the difference between an zero-lengthed string and null might
    be relevant. For instance, it could be "declined to answer" versus
    "unknown answer." Be aware that by using the reductions outlined, you
    will be obscuring that distinction.
     
  5. 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?


    Grinder wrote:
    > GVR_Mike wrote:
    > > 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.

    >
    > I'm a little rusty, but I'll give it a shot. "Invalid Use of Null"
    > happens when you try to treat a Null value as if it were valued, like a
    > string, or a double, etc. Remember, any field in a database can be
    > null, even if it is defined as a specific type, such as string, double, etc.
    >
    > > rstHD.MoveFirst
    > > Do Until rstHD.EOF
    > > rstSM.MoveFirst
    > > srNumHD = rstHD![srNum]

    >
    > Ok, if srNumHD is a string, and the recordset field value is Null,
    > you'll get an "Invalid Use of Null"
    >
    > There's a trick you can use, presuming it still works, that would
    > resolve the error:
    >
    > srNumHD = "" & rstHD![srNum]
    >
    > That forces a concatenation of a string type with the field, which will
    > be converted to a string if at all possible. Even though this works, I
    > think it's better to be explicit:
    >
    > If IsNull(rstHd.Fields("srNum").Value) Then
    > srNumHD = ""
    > Else
    > srNumHD = rstHd.Fields("srNum").Value
    > End If
    >
    > It makes the code explicit, but you'll have to decide if that's a good
    > thing. Sometimes people will wrap this logic into a short function names
    > something like FieldValueToString(), to keep their code tidy *and* explicit.
    >
    > > Do Until rstSM.EOF
    > > srNumSM = rstSM![srNum]

    >
    > Same business here with srNumSM.
    >
    > > 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.

    >
    > It sounds like your srNumSM is the problem. Make an explicit conversion
    > as previously outlined, and you should be good to go. Be a little
    > cautious, however, that you're making a *reductive* conversion. In some
    > databases, the difference between an zero-lengthed string and null might
    > be relevant. For instance, it could be "declined to answer" versus
    > "unknown answer." Be aware that by using the reductions outlined, you
    > will be obscuring that distinction.
     
  6. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Dumb question from someone who hasn't used active sql enought to know
    some of the more detailed aspects of SQL.

    If you delete a record, is that going to trigger every event the same
    as doing a movenext.

    If you delete the last record and then go back up as your code is
    saying to do. will the EOF be set even without attempting to go to the
    next record?

    Ron
     
  7. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Try using explicit test in the Do loop. Change this step:

    Do Until rstHD.EOF


    to this:

    Do Until rstHD.EOF = True

    --

    Ken Snell
    <MS ACCESS MVP>

    "GVR_Mike" <mjbruesch@triad.rr.com> wrote in message
    news:1150211793.074078.228980@g10g2000cwb.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?
    >
    >
    > Grinder wrote:
    >> GVR_Mike wrote:
    >> > 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.

    >>
    >> I'm a little rusty, but I'll give it a shot. "Invalid Use of Null"
    >> happens when you try to treat a Null value as if it were valued, like a
    >> string, or a double, etc. Remember, any field in a database can be
    >> null, even if it is defined as a specific type, such as string, double,
    >> etc.
    >>
    >> > rstHD.MoveFirst
    >> > Do Until rstHD.EOF
    >> > rstSM.MoveFirst
    >> > srNumHD = rstHD![srNum]

    >>
    >> Ok, if srNumHD is a string, and the recordset field value is Null,
    >> you'll get an "Invalid Use of Null"
    >>
    >> There's a trick you can use, presuming it still works, that would
    >> resolve the error:
    >>
    >> srNumHD = "" & rstHD![srNum]
    >>
    >> That forces a concatenation of a string type with the field, which will
    >> be converted to a string if at all possible. Even though this works, I
    >> think it's better to be explicit:
    >>
    >> If IsNull(rstHd.Fields("srNum").Value) Then
    >> srNumHD = ""
    >> Else
    >> srNumHD = rstHd.Fields("srNum").Value
    >> End If
    >>
    >> It makes the code explicit, but you'll have to decide if that's a good
    >> thing. Sometimes people will wrap this logic into a short function names
    >> something like FieldValueToString(), to keep their code tidy *and*
    >> explicit.
    >>
    >> > Do Until rstSM.EOF
    >> > srNumSM = rstSM![srNum]

    >>
    >> Same business here with srNumSM.
    >>
    >> > 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.

    >>
    >> It sounds like your srNumSM is the problem. Make an explicit conversion
    >> as previously outlined, and you should be good to go. Be a little
    >> cautious, however, that you're making a *reductive* conversion. In some
    >> databases, the difference between an zero-lengthed string and null might
    >> be relevant. For instance, it could be "declined to answer" versus
    >> "unknown answer." Be aware that by using the reductions outlined, you
    >> will be obscuring that distinction.

    >
     

Share This Page