VBA DAO with two recordsets

[prometro]

Hi everyone!

Iam trying to find answers for maybe simple thing but with no results yet ...
I you can help me it will be great! Thanks! Jiri

I have two tables :
TAB1 :
id filedX
1 AAA
2 BBB
3 CCC

TAB2 :
id fieldY fieldZ
1 aaa1 AAA
2 aaa2 AAA
3 aaa3 AAA
4 bbb1 BBB
5 bbb1 BBB

And I need to gain this result in one text string:
AAA (aaa1;aaa2;aaa3) ; BBB (bbb1;bbb2) ; CCC

I started with this code :

Private Sub Příkaz1_Click()

Dim db As DAO.Database
Dim r1, r2 As DAO.Recordset
Dim tex1 As String

Set db = CurrentDb
Set r1 = db.OpenRecordset("SELECT * FROM TAB1")
Set r2 = db.OpenRecordset("SELECT * FROM TAB1 RIGHT JOIN TAB2 ON TAB1.id = TAB2.fieldZ WHERE (((TAB2.fieldZ)=[COLOR="Red"]XXXXXXXX[/COLOR]))")

tex1 = ""
tex2 = ""

r1.MoveFirst
Do While Not r1.EOF
   
    tex1 = tex1 + " ; " + r1!fieldX
    
    r2.MoveFirst
    Do While Not r2.EOF

        tex2 = tex2 + ";" + r2!fieldY

    r2.MoveNext
    Loop

    tex1 = tex1 + "(" + tex2 + ")"

    r1.MoveNext
Loop

MsgBox (tex1)

End Sub

In red colour is my problem - how to do relation between recordsets ...

 

[CJ_London]

Your criteria needs to be

".....WHERE (((TAB2.fieldZ)=" & r1!FieldX & "))"

but I suspect you'll also need to move your r2 openrecordset line to within the r1 loop

 

[jdraw]

In addition to CJ's comment you should be aware that

Dim r1, r2 As DAO.Recordset

does not do what you think.

In Access vba you must explicitly declare variables.

either
Dim r1 As DAO.Recordset, r2 As DAO.Recordset
or
Dim r1 As DAO.Recordset
Dim r2 As DAO.Recordset

otherwise r1 is a variant.

 

[prometro]

Thank you for your replies!
Code is working now, but result is : AAA(aaa1,aaa2,aaa3) ; BBB(aaa1,aaa2,aaa3) ; CCC(aaa1,aaa2,aaa3).
And I need : AAA(aaa1,aaa2,aaa3) ; BBB(bbb1,bbb2) ; CCC
So CJ_London you are right that is necessary to put r2 openrecordset line to within the r1 loop ...
And I admit that I do not know how ...
In appendix is my mdb file. Can you help me with it?

 

[prometro]

Hi, I think I got it! :
Now it is working, I hope that there will not be other problems
Thank you guys!

Private Sub Pøíkaz0_Click()
Dim db As DAO.Database
Dim r1 As DAO.Recordset
Dim r2 As DAO.Recordset

Dim aaaa, tex1, tex2 As String

Set db = CurrentDb


Set r1 = db.OpenRecordset("SELECT * FROM tPolozky")

tex1 = ""
tex2 = ""

r1.MoveFirst

Do While Not r1.EOF

    tex1 = tex1 + " ; " + r1!polozka
    
    aaaa = r1!idPolozky
    Set r2 = db.OpenRecordset("SELECT tObrazky.obrazek, tObrazky.spadaPodPolozku FROM tPolozky LEFT JOIN tObrazky ON tPolozky.idPolozky = tObrazky.spadaPodPolozku WHERE (((tObrazky.spadaPodPolozku) =" & aaaa & "))")

    Do While Not r2.EOF

        tex2 = tex2 + ";" + r2!obrazek

    r2.MoveNext
    Loop

    tex1 = tex1 + "(" + tex2 + ")"
    tex2 = ""
    r1.MoveNext
Loop

MsgBox (tex1)
End Sub