Recordset Looping

[Drahy]

Good Day to all,

Can anyone help me for my programming difficulty regarding recordset looping. I want an expected result as shown in my attachment "Sample Expected Result.txt"
I have 2 recordsets using two tables named "Table1 and Table2". I am also using do while loop as shown in my attachment. The result of my code was shown in an immediate window.
Can anyone provide me as an exact code to show an expected result.

Thanks,
Drahy
[imgnothumb]http://bytes.com/attachments/attachment/7278d1383090465/sample-table.jpg[/imgnothumb]

Expected output

Danilo De Guzman
  1  Danilo De Guzman
  2  Rene Acayan
  3  Rene Acayan
  4  Oliver Sulit
  5  Michael Contridas
  6  R. Barando
  7  Lourdes Victoriano
  8  Arlene Azanes
  9  Linda Anonat
  10  Lorenzo Acosta Jr
  11  Oliver Sulit
  12  Oliver Sulit
  13  Arlene Azanes
  14  Gene Escote
  15  Manuelito Nerpio
  16  Teody Merle
  17  Alex Samuray
  18  Roy Feras
  19  Oliver Sulit
  20  Emilio Felizarta
Michael Contridas
  1  Danilo De Guzman
  2  Rene Acayan
  3  Rene Acayan
  4  Oliver Sulit
  5  Michael Contridas
  6  R. Barando
  7  Lourdes Victoriano
  8  Arlene Azanes
  9  Linda Anonat
  10  Lorenzo Acosta Jr
  11  Oliver Sulit
  12  Oliver Sulit
  13  Arlene Azanes
  14  Gene Escote
  15  Manuelito Nerpio
  16  Teody Merle
  17  Alex Samuray
  18  Roy Feras
  19  Oliver Sulit
  20  Emilio Felizarta
Gene Escote  
  1  Danilo De Guzman
  2  Rene Acayan
  3  Rene Acayan
  4  Oliver Sulit
  5  Michael Contridas
  6  R. Barando
  7  Lourdes Victoriano
  8  Arlene Azanes
  9  Linda Anonat
  10  Lorenzo Acosta Jr
  11  Oliver Sulit
  12  Oliver Sulit
  13  Arlene Azanes
  14  Gene Escote
  15  Manuelito Nerpio
  16  Teody Merle
  17  Alex Samuray
  18  Roy Feras
  19  Oliver Sulit
  20  Emilio Felizarta
Manuelito Nerpio
  1  Danilo De Guzman
  2  Rene Acayan
  3  Rene Acayan
  4  Oliver Sulit
  5  Michael Contridas
  6  R. Barando
  7  Lourdes Victoriano
  8  Arlene Azanes
  9  Linda Anonat
  10  Lorenzo Acosta Jr
  11  Oliver Sulit
  12  Oliver Sulit
  13  Arlene Azanes
  14  Gene Escote
  15  Manuelito Nerpio
  16  Teody Merle
  17  Alex Samuray
  18  Roy Feras
  19  Oliver Sulit
  20  Emilio Felizarta
[z{(snip)Full text in attached}]

[ADezii]

It would appear that you are trying to retrieve the [ID] and [Req'd By] Field Values from Table2, is this correct? What does Table1 have to do with this scenario?

[Drahy]

Sorry, one of the attachment is missing. I send it again the codes and the result in the immediate window.
But i want the expected result based on my previous attachment.

Here is the Codes:

1. Option Compare Database
2. Private Sub Command0_Click()
3. Dim db As Database
4. Dim rstab1, rstab2 As DAO.Recordset
5. Set db = CurrentDb
6. Set rstab1 = db.OpenRecordset("Table1", dbOpenDynaset)
7. Set rstab2 = db.OpenRecordset("Table2", dbOpenDynaset)
8. If Not (rstab1.EOF And rstab2.BOF) Then
9.     Do While Not rstab1.EOF
10.         Debug.Print rstab1.Fields(1)
11.         rstab1.MoveNext
12.             Do While Not rstab2.EOF
13.                     Debug.Print "   "; rstab2.Fields(0); rstab2.Fields(3)
14.                     rstab2.MoveNext
15.             Loop
16.     Loop
17. End If
18. db.Close
19. Set rstab1 = Nothing
20. Set rstab2 = Nothing
21. End Sub

Thanks,

Drahy
[IMGNOTHUMB]http://bytes.com/attachments/attachment/7288d1383536220/sample-result.jpg[/IMGNOTHUMB]

[ADezii]

For starters, change

Dim rstab1, rstab2 As DAO.Recordset

to

Dim rstab1 As DAO.Recordset
Dim rstab2 As DAO.Recordset

What you have done in the previous scenario is to Declare rstab1 as a Variant and not the expected DAO.Recordset.

[9815402440]

how both tables are being related. ID is the only common field and it will make one to one relationship. please explain in detail


-----------------------
Manpreet Singh Dhillon

[ADezii]

It appears that you need Nested Recordsets to accomplishing what you are asking, as in:

Dim db As Database
Dim rstab1 As DAO.Recordset
Dim rstab2 As DAO.Recordset
Set db = CurrentDb

Set rstab1 = db.OpenRecordset("Table1", dbOpenDynaset)
Set rstab2 = db.OpenRecordset("Table2", dbOpenDynaset)

Do While Not rstab1.EOF
  Debug.Print rstab1.Fields(1)
    Do While Not rstab2.EOF
      Debug.Print "  " & rstab2.Fields(0) & " " & rstab2.Fields(3)
        rstab2.MoveNext
    Loop
     rstab1.MoveNext: rstab2.MoveFirst
Loop

db.Close
Set rstab1 = Nothing
Set rstab2 = Nothing

[Rabbit]

This looks like a cartesian join. How come you're printing it to debug?