Code:
Private Sub Command3_Click()
On Error GoTo Err_MyProc
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Bank", dbOpenDynaset)
'NOTE: a & b are declared as Variant
' if you want a, b & c declared as strings then must use
'------------------------
'Dim a As String, b As String, c As String
'------------------------
Dim a, b, c As String
Dim sql As String
Dim LResult As String
Dim trans As String
With rst
'.MoveLast
.MoveFirst
Do While .EOF = False
LResult = Left(rst!field1, 6)
If LResult = "940SWI" Then
a = rst!field1
b = 0
'!Rec_Group = AssignGroup(!Rec_Type)
Else
a = a + rst!field1
b = b + 1
c = a
End If
.MoveNext
If LResult = "940SWI" Then
trans = c
sql = "INSERT INTO Bank2(Field1)Values('" & Nz(trans, "") & "');"
db.Execute sql, dbFailOnError
End If
Loop
End With
rst.Close 'Close what you opened.
Exit_MyProc:
Set rst = Nothing 'Deassign all objects.
Set db = Nothing
Exit Sub
Err_MyProc:
'Error handler here.
Resume Exit_MyProc
End Sub
BTW, You do have a major flaw in your logic.... "trans" will always be zero..