Insert into table from recordset

[Colargol]

Hi,

How do I make the variable trans to be inserted into table Bank2 ?

sql = "INSERT INTO Bank2(Field1)Values(trans);"

I am using access 2010.

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)
Dim a, b, c As String
Dim sql As String
Dim LResult As String
Dim trans As String

With rst
'.MoveLast
.MoveFirst

sql = "INSERT INTO Bank2(Field1)Values(trans);"


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
DoCmd.RunSQL sql
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

Regards
Ronny

[hertfordkc]

See http://allenbrowne.com/ser-29.html and other of Allen Browne's help files.

I find it easier to work with .Addnew, & .Update than to deal with SQL.

[ssanfu]

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..