asked on

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

Al

Rey Obrero (Capricorn1)

try this

if cat_id is text, change

Set rc_child = db.OpenRecordset("select * from child where parent_cat_id=" & rc_parent![cat_id])

to

Set rc_child = db.OpenRecordset("select * from child where parent_cat_id='" & rc_parent![cat_id] &"'")

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_CategoriesComplete = db.OpenRecordset("CategoriesComplete")
 
rc_parent.MoveFirst
 
do until rc_parent.eof
 
Set rc_child = db.OpenRecordset("select * from child where parent_cat_id=" & rc_parent![cat_id]) 
      rc_child.movefirst   
      Do until rc_child.EOF
 
  
        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
     loop     
 
         
 rc_parent.MoveNext
   
Loop
rc_parent.close 
rc_child.close
rc_CategoriesComplete.close
End Sub
 
 
 
 
if cat_id is text, change
 
Set rc_child = db.OpenRecordset("select * from child where parent_cat_id=" & rc_parent![cat_id])
 
to
 
Set rc_child = db.OpenRecordset("select * from child where parent_cat_id='" & rc_parent![cat_id] &"'")

Open in new window

Aljeebo

ASKER

Hi capricorn1

Thanks for your response. I tried the code but I get a No Current record error message for line 22 where rc_child.movefirst. Could it be my data?

Thanks

Al

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aljeebo

ASKER

That works, thanks very much.