Run Time Error 3021

[SPL26]

Hi,

Can anybody help me, I'm getting a error message with the following code. The error message is RUN-TIME ERROR '3021' NO CURRENT RECORD.

What I'm trying to do is to update a value in one table from another table. The table I'm updating has a one-to-many relationship with the table which I'm updating from, and the error message occures at the very end after it has updated the records.

Regards
Paul L

Public Function Update_Lvl3()
Dim db As Database, level3 As Recordset, Activ As Recordset, ReturnValue As Variant
Set db = CurrentDb()
Set level3 = db.OpenRecordset("SELECT DISTINCTROW LEVEL_3_DETAILS.* FROM LEVEL_3_DETAILS ORDER BY ACP_NO;")
Set Activ = db.OpenRecordset("SELECT DISTINCTROW RESOURCES.* FROM RESOURCES ORDER BY ACP_NO;")
DoCmd.Hourglass True
Do While Not level3.NoMatch
CURRENT_ESTIMATE = 0#
EARNED_HOURS = 0#
Do While level3!ACP_NO = Activ!ACP_NO
CURRENT_ESTIMATE = CURRENT_ESTIMATE + (Activ![WEIGHTING])
EARNED_HOURS = EARNED_HOURS + (Activ![PROGRESS_WEIGHT])
Activ.MoveNext
Loop
level3.LockEdits = False
level3.Edit
level3!CURRENT_ESTIMATE = CURRENT_ESTIMATE
level3!EARNED_HOURS = EARNED_HOURS
If level3!CURRENT_ESTIMATE = 0 Or level3!EARNED_HOURS = 0 Then
level3!Percent_Complete = 0
Else
level3!Percent_Complete = (level3!EARNED_HOURS / level3!CURRENT_ESTIMATE) * 100
End If
If level3!CURRENT_ESTIMATE <> 0 Then
If level3!PRODUCTIVITY = 0 Then
level3!FORECAST_REMAINING = level3!CURRENT_ESTIMATE - level3!EARNED_HOURS
Else
level3!FORECAST_REMAINING = (level3!CURRENT_ESTIMATE - level3!EARNED_HOURS) / level3!PRODUCTIVITY
End If
Else
level3!FORECAST_REMAINING = level3!CURRENT_ESTIMATE
End If
level3.Update
level3.MoveNext
Loop
level3.LockEdits = True
DoCmd.Hourglass False
End Function

 

[Alibaba2003]

SPL,
This is a common error when data is missing in the one side table. If referential integrity is enforced, you must fill all of values that has referential integrity on them.
Example:
Table Employee requires a value from states table and RI is enforced. If you dont supply a value for state in Employee you will get err 3021. Save the record before adding the many side part.

Regards
Alibaba2003

It's Nice to Be Important But It's more Important to BE Nice

 

[SPL26]

Thanks for the response Alibaba2003, but what I trying to do is effectivly calculate a value from the many side and update the value in the table not add a record.

Regards

 

[ZmrAbdulla]

Normally I add an error handler to trap error.

Public Function Update_Lvl3()

    On Error GoTo ErrorHandler

 [COLOR=green] 'Your code here
'...........................
[/color]ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then    [COLOR=green]' no current record
[/color]        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If

End Function

Also to make sure your SQL not returning empty recordset.

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[SPL26]

Thanks for replying so quickly ZmrAbdulla, i've put this into my code but for some reason the last record in the main table has not been updated.

regards

 

[ZmrAbdulla]

Are you sure the EndIF are at correct place. especailly the one in bold black. Blue Bold to add new

        If level3!CURRENT_ESTIMATE = 0 Or level3!EARNED_HOURS = 0 Then
            level3!Percent_Complete = 0
        Else
            level3!Percent_Complete = (level3!EARNED_HOURS / level3!CURRENT_ESTIMATE) * 100
       [b] [s]End If[/s][/b]
        [b][COLOR=blue]Else[/color][/b]
        If level3!CURRENT_ESTIMATE <> 0 Then
            If level3!PRODUCTIVITY = 0 Then
                level3!FORECAST_REMAINING = level3!CURRENT_ESTIMATE - level3!EARNED_HOURS
            Else
                level3!FORECAST_REMAINING = (level3!CURRENT_ESTIMATE - level3!EARNED_HOURS) / level3!PRODUCTIVITY
            End If
        Else
            level3!FORECAST_REMAINING = level3!CURRENT_ESTIMATE
        End If
[b][COLOR=blue]End If[/color][/b]

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[SPL26]

Its get hang up on the line that says

Do While level3!ACP_NO = Activ!ACP_NO

it doesn't get as far as the updating the last record

 

[RoyVidar]

By a quick glance:

It probably stops there because there's no current record - either the "activ" recordset is .Bof (didn't contain anything from the beginning), or it's .Eof (reached the end) - no current record. You will need to test for .Eof/.Bof

Same with the first loop, you test for .NoMatch, but need to test for .eof.

I e, test for .Bof/.Eof first, then do while not .eof ...

Roy-Vidar

 

[ZmrAbdulla]

also

  Do While level3!ACP_NO = Activ!ACP_NO
      [b]??![/b]CURRENT_ESTIMATE = CURRENT_ESTIMATE + (Activ![WEIGHTING])
      [b]??![/b]EARNED_HOURS = EARNED_HOURS + (Activ![PROGRESS_WEIGHT])
      Activ.MoveNext

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[SPL26]

Thanks for you help I've manage to sort it now

Regards

 

[ZmrAbdulla]

Could you tell us what was the problem? probably with the solution so that others too benefit..

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[SPL26]

What I've done under the ErrorHandler section, see below, is instead of using Resume Next I've just put the code for updating the record again with an exit function at the end. This solves the problem with the last record not being updated. I know this is probably not the best solution to the problem, but it works for me.

ErrorHandler:
    If Err = 3021 Then    ' no current record
       level3.LockEdits = False
       level3.Edit
       If CURRENT_ESTIMATE <> 0 Then
          level3!CURRENT_ESTIMATE = CURRENT_ESTIMATE
          level3!EARNED_HOURS = EARNED_HOURS
          If level3!CURRENT_ESTIMATE = 0 Or level3!EARNED_HOURS = 0 Then
             level3!Percent_Complete = 0
          Else
             level3!Percent_Complete = (level3!EARNED_HOURS / level3!CURRENT_ESTIMATE) * 100
          End If
          If level3!CURRENT_ESTIMATE <> 0 Then
             If level3!PRODUCTIVITY = 0 Then
                level3!FORECAST_REMAINING = level3!CURRENT_ESTIMATE - level3!EARNED_HOURS
             Else
                level3!FORECAST_REMAINING = (level3!CURRENT_ESTIMATE - level3!EARNED_HOURS) / level3!PRODUCTIVITY
             End If
          Else
             level3!FORECAST_REMAINING = level3!CURRENT_ESTIMATE
          End If
       End If
       level3.Update
       level3.MoveNext
       level3.LockEdits = True
       ReturnValue = SysCmd(acSysCmdClearStatus)
       Update_Lvl3 = "OK"
       DoCmd.Hourglass False
       Exit Function

Regards

 

[RoyVidar]

In stead of using errorhandling code for normal occurence, you may want to investigate the more usual way of doing loops/working with recordsets.

[tt]Set db = CurrentDb()
Set rs = db.OpenRecordset(<some select>)
' check whether the recordset contains records
if not rs.bof and not rs.eof then

' start the looping, and continue that until
' one reaches the end of the recordset
' can of course also add additional restrictions
do while not rs.eof

' do stuff with the recordset

rs.movenext
loop
end if[/tt]

It is essential, I think, to test for .Eof whenver you've to the next record, else the 3021 has a tendency of occuring ... (and .bof, if you're doing a .moveprevious)

It seems what you are doing, is storing calculated values in one of your tables. YOu really shouldn't! You should rather calculate them each time you need them, else you'll probably end up with a lot of extra work, and multile versions of the thruth, not if, but when the calculated data is out of synch with the data from which it is derived. Not sure I've got it all right, but here are two attempts to play with.

[tt]SELECT DISTINCTROW l.*
, (Select Sum(r.WEIGHTING)
From RESOURCES r
Where l.ACP_NO = r.ACP_NO) as CURRENT_ESTIMATE
, (Select Sum(q.PROGRESS_WEIGHT)
From RESOURCES q
Where l.ACP_NO = q.ACP_NO) as EARNED_HOURS
FROM LEVEL_3_DETAILS l ORDER BY l.ACP_NO[/tt]

[tt]SELECT DISTINCTROW l.*, s.CURRENT_ESTIMATE, s.EARNED_HOURS
FROM LEVEL_3_DETAILS l INNER JOIN
(Select Sum(WEIGHTING) as CURRENT_ESTIMATE,
Sum(PROGRESS_WEIGHT) as EARNED_HOURS, ACP_NO
From RESOURCES
Group By.ACP_NO) as S
ON s.ACP_NO = l.ACP_NO
ORDER BY l.ACP_NO[/tt]

Aside from the "breaking of rules", you will probably find recommandations to reduce recordsetapproaches as much as you can, in favour of using queries. There are several reasons for that, but speed is one in particular.

Roy-Vidar