MS ACCESS VBA - compare 2 recordsets for matching fields then add some data from each record to a new record in another table

Hi folks

I have 2 tables, parent categories and child categories relating to products for an online shop.

Parent Categories Table fields
*Cat_id    cat_name

Child Categories Table fields
Cat_id    *parent_cat_id    cat_name

My problem is that the only information that the child categories table has about the parent categories is an ID, so parent_cat_id in the child categories table links to the cat_id field in the parent categories table.

Parent cat table = 42 records and child = 120.

I need to loop through both recordsets and where the IDs match grab cat_name from both tables and concatenate them into a full category name field in my new table (which is already created, doesn't need be created dynamically or anything).

the code I have so far:

Public Sub NameCategories()

Dim db As Database
Dim rc_parent As Recordset
Dim rc_child As Recordset
Dim rc_CategoriesComplete As Recordset
Dim parent_name As String

Set db = CurrentDb()
Set rc_parent = db.OpenRecordset("parent")
Set rc_child = db.OpenRecordset("child")
Set rc_CategoriesComplete = db.OpenRecordset("CategoriesComplete")

rc_parent.MoveFirst
rc_child.MoveFirst

Do While Not rc_child.EOF

    If rc_parent![cat_id] = rc_child![parent_cat_id] Then
   
        rc_CategoriesComplete.AddNew
        rc_CategoriesComplete![parent_cat_id] = rc_parent![cat_id]
        rc_CategoriesComplete![child_cat_id] = rc_child![cat_id]
        rc_CategoriesComplete![cat_string] = rc_parent![cat_name] + "/" + rc_child![cat_name]
        rc_CategoriesComplete.Update
        rc_child.MoveNext
       
    Else
         
        rc_parent.MoveNext
       
    End If

        rc_parent.MoveFirst
   
Loop
   
End Sub

...sorry, I know it's not moving correctly. to be honest I've been messing with it so much I'm lost now!

Can you please help?

Many thanks

AlStart Free Trial