We help IT Professionals succeed at work.

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

5,742 Views
Last Modified: 2013-12-20
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That works, thanks very much.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.