Update table2 from table1- Runtime error

[albasel]

Hi everybody
I am going to update table1 from table2 but I face an error said
"Run-time error'-2147417848(80010108)'
Method 'Fields' of object 'Recordset2' failed"

Can any body help me in that???

Private Sub updateTable(tbl11 As String, tbl22 As String)
Dim dbs11, dbs22 As DAO.Database
Dim rs11, rs22 As DAO.Recordset
Dim c1, c2 As Integer
Dim f As Boolean
f = False
    Set dbs11 = CurrentDb
    Set dbs22 = CurrentDb
    Set rs11 = dbs11.OpenRecordset(tbl11, dbOpenDynaset, dbOpenDynamic)
    Set rs22 = dbs22.OpenRecordset(tbl22, dbOpenDynaset, dbOpenDynamic)
        
    If rs11.EOF Then Exit Sub
        
    With rs11
        Do While Not .EOF
            For c1 = 0 To dbs11.Recordsets.Count - 1
                f = False
                For c2 = 0 To (dbs22.Recordsets.Count - 1) Or f = True
                    If .Fields(0) = rs22.Fields(0) And .Fields(1) = rs22.Fields(1) And .Fields(2) = rs22.Fields(2) Then
                        rs22.Fields(3).Value = .Fields(3).Value
                        rs22.Fields(4).Value = .Fields(4).Value
                        rs22.Fields(5).Value = .Fields(5).Value
                        f = True
                    End If
                Next
                rs22.AddNew
                For c = 0 To .Fields.Count - 1
                    rs22.Fields(c) = .Fields(c)
                Next
            rs22.Update
              'rs22.MoveNext
              'rs11.MoveNext
            Next
        Loop
    
    End With
    
    rs11.Close
    rs22.Close
      
    
End Sub

[June7]

First of all, don't need two database objects because all the data is in the current db.

Why are you doing a count of Recordsets collection?

Also, VBA requires every variable to be explicitly declared otherwise they will default to variant.

Dim dbs11 As DAO.Database, dbs22 As DAO.Database
Dim rs11 As DAO.Recordset, rs22 As DAO.Recordset
Dim c1 As Integer, c2 As Integer

Have you step debugged? Refer to link at bottom of my post for debugging guidelines.

Why are you using VBA to update one table with data from another table? Can't do an UPDATE query?

Why does data need to be in both tables?

[albasel]

one table is temp table(embeded in a form) that contain the tasks that are not done yet and the other one is to have all the tasks that are done or not. So we update the permenant table from the temp. table and then we will delete all the data in the temp. table.

[June7]

Why have a temp table?

[albasel]

- I creat a subform in a form that shows me all the tasks that are not done yet.
- The subform is actually an equary which filtered the data from tasks table to incomplete tasks only.
- After updating the subform, I copy all the data in the subform to tasks table.

So, how can I do all that??

[June7]

I'm lost. Not sure answered my question. Do all what? Don't you already have a form/subform? Is subform bound to temp table or to query?

[albasel]

subform bound to query

[June7]

And the query pulls data from what table?

I still don't understand this situation. There is NOT a temp table involved? What are you really trying to accomplish? Why don't you answer all questions?

[albasel]

Actually, we have two tables: table A has all the tasks data and table B has only the incompleted tasks data and also we can use the table to enter the new tasks(through form).

I want to have a function that updates table A using the data in table B. How can we do this function?

THANKS

[June7]

An UPDATE query should be able to accomplish that.

I still don't understand why you have table B. Why not just do data entry/edit in table A?

[albasel]

I still don't understand why you have table B. Why not just do data entry/edit in table A?

I desgin a form to show only the incomplete task and to enter any new task in the same table which is table B

[June7]

That can still be done with one table. Form can be bound to a query that has a static filter parameter that excludes completed tasks. You are just making life harder trying to synchronize two tables.

However, did the UPDATE query suggestion work?