Last Field Doesn't get updated...

[DanKay1]

I wrote the code and it works perfect except it doesn't update the last field in the table. Can you tell me if you see my error. Thank you.

Dim LastPolicy As String
Dim MAX_KEEP As Double
Dim MAX_CEDE As Double
Dim SUM As Double
Dim MAX_TO_KEEP As Double
Dim MAX_TO_CEDE As Double
Dim AMOUNT As Double

MAX_KEEP = 3000000
MAX_CEDE = 3000000
AMOUNT_TO_KEEP = 0
AMOUNT_TO_CEDE = 0
SUM = 0
LastPolicy = -100 ' initialize this as a policy number you will never ever use

Set rs1 = CurrentDb.OpenRecordset("query2")

rs1.MoveFirst
While Not rs1.EOF()
LastPolicy = rs1.[Taix-ID]
If rs1.[Taix-ID] <> LastPolicy Then
AMOUNT_TO_KEEP = 0
AMOUNT_TO_CEDE = 0

End If

AMOUNT = rs1.[taix-benefit]

If (AMOUNT_TO_KEEP = MAX_KEEP And AMOUNT_TO_CEDE = MAX_CEDE) Then
With rs1
.edit
.Fields("Taix-retan") = AMOUNT
.Update
End With
ElseIf (AMOUNT_TO_KEEP = MAX_KEEP And AMOUNT_TO_CEDE < MAX_CEDE) Then
If (AMOUNT_TO_CEDE + AMOUNT > MAX_CEDE) Then
With rs1
.edit
.Fields("Taix-retan") = AMOUNT - (MAX_CEDE - AMOUNT_TO_CEDE)
.Fields("TAIX-reins") = MAX_CEDE - AMOUNT_TO_CEDE
.Update
End With
AMOUNT_TO_CEDE = MAX_CEDE
Else
With rs1
.edit
.Fields("TAIX-reins") = AMOUNT
.Update
End With
AMOUNT_TO_CEDE = AMOUNT_TO_CEDE + AMOUNT
End If
ElseIf (AMOUNT_TO_KEEP < MAX_KEEP And AMOUNT_TO_CEDE < MAX_CEDE) Then
If (AMOUNT_TO_KEEP + AMOUNT > MAX_KEEP) Then
With rs1
.edit
.Fields("TAIX-retan") = MAX_KEEP - AMOUNT_TO_KEEP
.Update
End With
If ((AMOUNT_TO_KEEP + AMOUNT - MAX_KEEP) < (MAX_CEDE - AMOUNT_TO_CEDE)) Then
With rs1
.edit
.Fields("TAIX-reins") = AMOUNT_TO_KEEP + AMOUNT - MAX_KEEP
.Update
End With
AMOUNT_TO_CEDE = AMOUNT_TO_CEDE + (AMOUNT_TO_KEEP + AMOUNT - MAX_KEEP)
AMOUNT_TO_KEEP = MAX_KEEP
Else
With rs1
.edit
.Fields("TAIX-reins") = MAX_CEDE
.Fields("TAIX-retan") = rs1.[taix-retan] + AMOUNT - MAX_KEEP - MAX_CEDE
.Update
End With
AMOUNT_TO_CEDE = MAX_CEDE
AMOUNT_TO_KEEP = MAX_KEEP
End If
Else
With rs1
.edit
.Fields("TAIX-retan") = AMOUNT
.Update
End With
AMOUNT_TO_KEEP = AMOUNT_TO_KEEP + AMOUNT
End If
End If


With rs1
.MoveNext
End With

Wend

 

[SkipVought]

Dan,

I think that you have to look at the LAST ElseIf structure to see if you have a missing Else statement here...

         ElseIf (AMOUNT_TO_KEEP < MAX_KEEP And AMOUNT_TO_CEDE < MAX_CEDE) Then


         Else 
           'missing

         End if

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[DanKay1]

SkipVought,
I don't have problems with syntax error, I have a problem with a logical error. The logic works for all the fields except when it gets to the last field.

I think the problem is somewhere here since thats where i control if the policy has changed.

rs1.MoveFirst
While Not rs1.EOF()
LastPolicy = rs1.[Taix-ID]
If rs1.[Taix-ID] <> LastPolicy Then
AMOUNT_TO_KEEP = 0
AMOUNT_TO_CEDE = 0

End If

 

[SkipVought]

It's NOT syntax, its LOGIC!

1) You are reading to EOF

2) the last record is NOT updating anything.

THEREFORE...

the LOGIC is flawed by NOT including an ELSE condition for UPDATING UNDER THIS PARTICULAR CONDITION.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244