Help with Variable

[shaggy]

I can't think of any way to do this in a query. I think I need to do this in code so I can store a variable from the previous record.

I want to create Field2. Field2 in the first record will always be 1. In each successive record if Field1 is "57" I want to use the same Fields2 value in the previous record. If Field1 is any non-null value other than "57" I want it incremented Field2 up by 1.

Example:
Field1 Field2
37 1
57 1
22 2
57 2
57 2
57 2
23 3
57 3

I have no idea how to write any code so the more detailed the help the better.

 

[RuralGuy]

This UNTESTED code should get you started:

Public Sub FillF2()

   Dim rs As DAO.Recordset
   Dim PrevValue As Integer

   Set rs = CurrentDb().OpenRecordset("SELECT * FROM YourTable", dbOpenDynaset)

   With rs
      If .RecordCount > 0 Then
         PrevValue = !Field2
         .MoveNext
         Do While Not .EOF
            .Edit
            If !Field1 = "57" Or IsNull(!Field1) Then
               !Field2 = PrevValue
            Else
               PrevValue = PrevValue + 1
               !Field2 = PrevValue
            End If
            .Update
            .MoveNext
         Wend
      End If
      .Close
   Wend
   Set rs = Nothing
End Sub

...replacing all of the table and field names with you actual names of course.