Chi Is Current
asked on
Null Recordset Value Generating VBA Run-time Error #3021 - No Current Record
Following VBA expression generates Run-time Error #3021 - No Current Record
I would appreciate knowing how to circumvent this error.
Have tried:
Many Thanks ~ Jacob
srNetInvTotal = srNetInvTotal - Nz(rs2!NETDisc)
debug.print rs2!NETDisc
Result = NullI would appreciate knowing how to circumvent this error.
Have tried:
If rs2!NETDisc <> Null Then
srNetInvTotal = srNetInvTotal - Nz(rs2!NETDisc)
end if
also:If rs2!NETDisc <> "" Then
srNetInvTotal = srNetInvTotal - Nz(rs2!NETDisc)
end if
In both cases, line #2 is flagged w/ Error #3021Many Thanks ~ Jacob
ASKER
Thank you, Joe, for your comment.
Preceding code is:
Preceding code is:
srNetInvTotal = 0
If Not rs2.EOF Then
rs2.MoveFirst
Do Until rs2.EOF
'Net Invoice Amount
srNetInvTotal = srNetInvTotal + (rs2!ExtendedNetPr)
rs2.MoveNext
Loop
If rs2!NETDisc = Null Then
srNetInvTotal = srNetInvTotal - Nz(rs2!NETDisc)
End If
End If
ASKER
Should be:
Line 9: If rs2!NETDisc <> Null Then
Line 9: If rs2!NETDisc <> Null Then
Try:
If Not IsNull(rs2!NETDisc) Then
If Not IsNull(rs2!NETDisc) Then
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, Joe!
Debug.Print rs2!NETDisc
srNetInvTotal = 0
'If Not rs2.EOF Then
If rs2.RecordCount = 0 Then
' Outta here
GoTo Next2
Else
rs2.MoveFirst
Do Until rs2.EOF
'Net Invoice Amount
srNetInvTotal = srNetInvTotal + (rs2!ExtendedNetPr)
rs2.MoveNext
Loop
If Not IsNull(rs2!NETDisc) Then
srNetInvTotal = srNetInvTotal - Nz(rs2!NETDisc)
End If
End If
Next2:
Still Error #3021 on Line 16
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Heading out. Will rejoin this conversation when sanity returns. Many Thanks, Jacob
Happy Saturday Gustav :-)
ASKER
Gustav, thank you for your comment!
Debug.Print rs2!NETDisc
srNetInvTotal = 0
'If Not rs2.EOF Then
If rs2.RecordCount = 0 Then
' Outta here
GoTo Next2
Else
rs2.MoveFirst
Do Until rs2.EOF
'Net Invoice Amount
srNetInvTotal = srNetInvTotal + (rs2!ExtendedNetPr)
rs2.MoveNext
Loop
'If Not IsNull(rs2!NETDisc) Then
If Not IsNull(rs2!NETDisc.Value) Then
srNetInvTotal = srNetInvTotal - Nz(rs2!NETDisc)
End If
End If
Next2:
Still Error #3021 on Line 17
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice Joe ... a nighthawk?
Here it is only 9 o'clock and I've had my morning coffee.
/gustav
Here it is only 9 o'clock and I've had my morning coffee.
/gustav
2357 here ... :-)
Oh, not that bad. Have a good night!
/gustav
/gustav
We're still not seeing all the code. Where is srNetInvTotal defined?
FYI Joe told you to "try this" but the explanation is that you cannot compare any variable to null because the result would be null. There is an explanation of why if you read about relational database theory. That is the reason he had you change the code to use the IsNull() function. In SQL, you use:
Where somefield Is Null
but in VBA you use:
If IsNull(somefield) = true
You can also use VBA functions but I would always use "Is Null" in the where clause and use IsNull() when creating a calculated result.
FYI Joe told you to "try this" but the explanation is that you cannot compare any variable to null because the result would be null. There is an explanation of why if you read about relational database theory. That is the reason he had you change the code to use the IsNull() function. In SQL, you use:
Where somefield Is Null
but in VBA you use:
If IsNull(somefield) = true
You can also use VBA functions but I would always use "Is Null" in the where clause and use IsNull() when creating a calculated result.
"FYI Joe told you to "try this" but the explanation is"
@ Pat ... fyi ... Not sure why you feel the need to always come in and repeat everything I say? I like to see it something is going to work or solve the problem .. *before* I explain why. Clearly things are still not working :-) After almost 11 years on EE 24x7x365 ... I've learned that TMI is not always a good thing or a good use of one's time.
Here is one example of what I am talking about ... fyi.
@ Pat ... fyi ... Not sure why you feel the need to always come in and repeat everything I say? I like to see it something is going to work or solve the problem .. *before* I explain why. Clearly things are still not working :-) After almost 11 years on EE 24x7x365 ... I've learned that TMI is not always a good thing or a good use of one's time.
Here is one example of what I am talking about ... fyi.
We're going to have to agree to disagree on the point of explanations. Understanding null is an important concept. You keep throwing fish and I'll keep teaching people how to fish so they can fend for themselves.
I'm sorry ... did you look at the other example I posted ?
My history of 'explaining' things to OPs is well documented :-)
Sometimes, I don't always have time at the 'moment'. :-)
My history of 'explaining' things to OPs is well documented :-)
Sometimes, I don't always have time at the 'moment'. :-)
Joe, there is no need to take this personally. You are acting like I jump in and stomp all over your answers. Yes, I looked at the other thread and in that thread the poster came back and asked for an explanation of your "try this" and Jacob might have this time also. But if he didn't, he would have made the same mistake again or a slightly different one. For example
If fldA <> fldB Then
If one of the fields is null, the result is NOT true as most people expect. The result will always be False if one or both the operands are null. To handle a comparison of two variables when one or both might be null, you have to do something like:
If fldA & "" <> fldB & ""
Concatenating a ZLS to a field will convert it from null to ZLS due to the way the & works as a concatenation operator.
If fldA <> fldB Then
If one of the fields is null, the result is NOT true as most people expect. The result will always be False if one or both the operands are null. To handle a comparison of two variables when one or both might be null, you have to do something like:
If fldA & "" <> fldB & ""
Concatenating a ZLS to a field will convert it from null to ZLS due to the way the & works as a concatenation operator.
" But if he didn't, he would have made the same mistake again or a slightly different one. For example "
I was already typing those responses when he responded back :-)
PS ...
Thanks for the (unnecessary) lesson on Nulls above ... again, TMI :-)
I was already typing those responses when he responded back :-)
PS ...
Thanks for the (unnecessary) lesson on Nulls above ... again, TMI :-)
The lesson wasn't for you. I didn't realize that you were such a delicate daisy. Now that you've decided to be unpleasant, you should make your comments off line if you have anything else to add.
LOL ... you are confused. In no way do I take this personally nor am I offended. I didn't have anything to add to start with ... but you did :-)
ASKER
Gustav ~
"Your code makes little sense."
...
' Here you have moved out of rs2 to EOF.
' Thus, the next code will always fail.
If Not IsNull(rs2!NETDisc.Value) Then
srNetInvTotal = srNetInvTotal - Nz(rs2!NETDisc)
End If
' -------------------------- ---------
This is precisely the issue!
Thank you for noticing it!!!
I need to pull the value for rs2!NETDisc while focus is still in rs2.
(Since I did not want the value to compound, I moved it out of the loop...)
Joe & Pat - Thank you as well! I certainly have plenty to learn here and I appreciate your thoughts and guidance.
In Health and Peace ~ Jacob
"Your code makes little sense."
...
' Here you have moved out of rs2 to EOF.
' Thus, the next code will always fail.
If Not IsNull(rs2!NETDisc.Value) Then
srNetInvTotal = srNetInvTotal - Nz(rs2!NETDisc)
End If
' --------------------------
This is precisely the issue!
Thank you for noticing it!!!
I need to pull the value for rs2!NETDisc while focus is still in rs2.
(Since I did not want the value to compound, I moved it out of the loop...)
Joe & Pat - Thank you as well! I certainly have plenty to learn here and I appreciate your thoughts and guidance.
In Health and Peace ~ Jacob
What is the code before this ?