473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Recordset Looping

4
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



Expected output
Expand|Select|Wrap|Line Numbers
  1. Danilo De Guzman
  2.   1  Danilo De Guzman
  3.   2  Rene Acayan
  4.   3  Rene Acayan
  5.   4  Oliver Sulit
  6.   5  Michael Contridas
  7.   6  R. Barando
  8.   7  Lourdes Victoriano
  9.   8  Arlene Azanes
  10.   9  Linda Anonat
  11.   10  Lorenzo Acosta Jr
  12.   11  Oliver Sulit
  13.   12  Oliver Sulit
  14.   13  Arlene Azanes
  15.   14  Gene Escote
  16.   15  Manuelito Nerpio
  17.   16  Teody Merle
  18.   17  Alex Samuray
  19.   18  Roy Feras
  20.   19  Oliver Sulit
  21.   20  Emilio Felizarta
  22. Michael Contridas
  23.   1  Danilo De Guzman
  24.   2  Rene Acayan
  25.   3  Rene Acayan
  26.   4  Oliver Sulit
  27.   5  Michael Contridas
  28.   6  R. Barando
  29.   7  Lourdes Victoriano
  30.   8  Arlene Azanes
  31.   9  Linda Anonat
  32.   10  Lorenzo Acosta Jr
  33.   11  Oliver Sulit
  34.   12  Oliver Sulit
  35.   13  Arlene Azanes
  36.   14  Gene Escote
  37.   15  Manuelito Nerpio
  38.   16  Teody Merle
  39.   17  Alex Samuray
  40.   18  Roy Feras
  41.   19  Oliver Sulit
  42.   20  Emilio Felizarta
  43. Gene Escote  
  44.   1  Danilo De Guzman
  45.   2  Rene Acayan
  46.   3  Rene Acayan
  47.   4  Oliver Sulit
  48.   5  Michael Contridas
  49.   6  R. Barando
  50.   7  Lourdes Victoriano
  51.   8  Arlene Azanes
  52.   9  Linda Anonat
  53.   10  Lorenzo Acosta Jr
  54.   11  Oliver Sulit
  55.   12  Oliver Sulit
  56.   13  Arlene Azanes
  57.   14  Gene Escote
  58.   15  Manuelito Nerpio
  59.   16  Teody Merle
  60.   17  Alex Samuray
  61.   18  Roy Feras
  62.   19  Oliver Sulit
  63.   20  Emilio Felizarta
  64. Manuelito Nerpio
  65.   1  Danilo De Guzman
  66.   2  Rene Acayan
  67.   3  Rene Acayan
  68.   4  Oliver Sulit
  69.   5  Michael Contridas
  70.   6  R. Barando
  71.   7  Lourdes Victoriano
  72.   8  Arlene Azanes
  73.   9  Linda Anonat
  74.   10  Lorenzo Acosta Jr
  75.   11  Oliver Sulit
  76.   12  Oliver Sulit
  77.   13  Arlene Azanes
  78.   14  Gene Escote
  79.   15  Manuelito Nerpio
  80.   16  Teody Merle
  81.   17  Alex Samuray
  82.   18  Roy Feras
  83.   19  Oliver Sulit
  84.   20  Emilio Felizarta
  85. [z{(snip)Full text in attached}]
Attached Images
File Type: jpg Sample Table.jpg (74.9 KB, 513 views)
Attached Files
File Type: txt Sample Expected Report.txt (4.2 KB, 1192 views)
Oct 29 '13 #1
6 1787
ADezii
8,834 Expert 8TB
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?
Oct 30 '13 #2
Drahy
4
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:

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

Thanks,

Drahy

Attached Images
File Type: jpg Sample Result.jpg (44.7 KB, 469 views)
Nov 4 '13 #3
ADezii
8,834 Expert 8TB
For starters, change
Expand|Select|Wrap|Line Numbers
  1. Dim rstab1, rstab2 As DAO.Recordset
to
Expand|Select|Wrap|Line Numbers
  1. Dim rstab1 As DAO.Recordset
  2. 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.
Nov 4 '13 #4
9815402440
180 100+
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
Nov 4 '13 #5
ADezii
8,834 Expert 8TB
It appears that you need Nested Recordsets to accomplishing what you are asking, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rstab1 As DAO.Recordset
  3. Dim rstab2 As DAO.Recordset
  4. Set db = CurrentDb
  5.  
  6. Set rstab1 = db.OpenRecordset("Table1", dbOpenDynaset)
  7. Set rstab2 = db.OpenRecordset("Table2", dbOpenDynaset)
  8.  
  9. Do While Not rstab1.EOF
  10.   Debug.Print rstab1.Fields(1)
  11.     Do While Not rstab2.EOF
  12.       Debug.Print "  " & rstab2.Fields(0) & " " & rstab2.Fields(3)
  13.         rstab2.MoveNext
  14.     Loop
  15.      rstab1.MoveNext: rstab2.MoveFirst
  16. Loop
  17.  
  18. db.Close
  19. Set rstab1 = Nothing
  20. Set rstab2 = Nothing
Nov 6 '13 #6
Rabbit
12,516 Expert Mod 8TB
This looks like a cartesian join. How come you're printing it to debug?
Nov 7 '13 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Hamster | last post by:
I need to display data like this: CompanyName1 Company | Date| Transactions Count -- headers CompanyName1 | Date| 1 -- data CompanyName2 | Date| 5 -- data...
4
by: Tom | last post by:
I want to open a recordset object on an .asp page. When I open the recordset I would like to use a stored procedure that expects a parameter to be passed for the stored procedure. I will then use...
5
by: !TG | last post by:
I currently use Do while loop, but I'd rather use a For Loop though I have never gotten the hang of them. Would some one please be so kind as to show me how to loop through a recordset.
13
by: Jan | last post by:
Hi I have a database that I use to keep track of the sales promotions that we send to companies. I normally send a mailing based on a subset of the companies in the database (found using the...
11
by: Dacuna | last post by:
Is it possible to use a recursive function to loop through a recordset faster? I have a table that I need to edit its contents after doing some calculation. The table has one field has an RawData...
11
by: Jim Whitaker | last post by:
Expand full for viewing... I have a parent/child table. The basic setup is as follows: The parent table is called load sheet. It has fields such as Tripno, carrier, loaddate, etc. The child...
1
by: Ryan | last post by:
Hello. I was hoping that someone may be able to assist with an issue that I am experiencing. I have created an Access DB which imports an Excel File with a particular layout and field naming. ...
1
by: pds79 | last post by:
Hi everyone, I'm a newbie to the forum. I have an issue and was hoping to get some assistance/ideas: Im trying to read a XML file into two record sets. I can acheive looping through the...
3
by: mark_aok | last post by:
Hi all, All I am trying to do is open a table, edit it, and then close it. But I am having the strangest error. Here is my code Dim i as integer Dim rs as adodb.recordset Set rs = new...
3
by: Keredw | last post by:
Using Access 2003 I am looping through table values and creating a separate report for each name. The report is then emailed with a TO: and CC: address. That works fine. What I would like to do is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.