June7,
Tried that and even included additional criteria
Code:
Dim rV As DAO.Recordset
Dim i As Integer, J As Integer, Units As String
Set rV = CurrentDb.OpenRecordset("Data")
Do While Not rV.EOF
rV.Edit
If rV![Desc] Like "I # FIRE" And rV![Para] = "0109" Then
For i = 1 To 3
For J = 1 To 4
K = Chr(J + 64)
If rV![Bumper] = i & "RRR " & K & "/ZZZ" Then
Units = i & " " & K & " " & "ZZZ"
ElseIf rV![Bumper # / Plat ID] = i & "PLT " & K & "/YYY" Then
Units = i & " " & K & " " & "YYY"
ElseIf rV![Bumper # / Plat ID] = i & "PLT " & K & "/XXX" Then
Units = i & " " & K & " " & "XXX"
End If
Next J
Next i
End If
rV![NUnits] = Units
rV.Update
rV.MoveNext
Loop
End Sub
This will keep updating rV![Nunits] until EOF. thus asking about else do nothing
I think it has to do with
If rV![Bumper] = i & "RRR " & K & "/ZZZ" Then
Units = i & " " & K & " " & "ZZZ"
part of the code. maybe the / in the quotes? If the criteria is met then it will update to the correct output, but if it is not and is the last one of the data set, then it will repeat the last output until EOF.
I tried:
Code:
Else: Units = ""
rV![NUnits] = IIF(Units="", rV![Nunits], Units)
rV.Update
rV.MoveNext
Loop
With this, I get no updates, indicating The If Code isn't identifying correctly.
Examples of rV![Bumper] to Units:
1RRR A/ZZZ 1 AZZZ
1RRR B/ZZZ 1 BZZZ
1RRR C/ZZZ 1 CZZZ
1RRR D/ZZZ 1 DZZZ
2RRR A/ZZZ 2 AZZZ
2RRR B/ZZZ 2 BZZZ
2RRR C/ZZZ 2 CZZZ
2RRR D/ZZZ 2 DZZZ
3RRR A/ZZZ 3 AZZZ
3RRR B/ZZZ 3 BZZZ
3RRR C/ZZZ 3 CZZZ
3RRR D/ZZZ 1 DZZZ........So on and so forth
1RRR A/YYY
1RRR B/YYY
1RRR C/YYY
1RRR D/YYY
2RRR A/YYY
2RRR B/YYY
2RRR C/YYY
2RRR D/YYY
3RRR A/YYY
3RRR B/YYY
3RRR C/YYY
3RRR D/YYY
1RRR A/XXX
1RRR B/XXX
1RRR C/XXX
1RRR D/XXX
2RRR A/XXX
2RRR B/XXX
2RRR C/XXX
2RRR D/XXX
3RRR A/XXX
3RRR B/XXX
3RRR C/XXX
3RRR D/XXX