loop through recordset

  • Thread starter Thread starter Tony Piperato
  • Start date Start date
T

Tony Piperato

Greetings,

I am opening a recordset and would like to print the results in a report
detail section. I have the following code:

Do Until rsMyRS.EOF
txtDateStart = rsMyRS("DateStart")
txtDateStop = rsMyRS("DateStop")
Debug.Print rsMyRS("DateStart") & " " & rsMyRS("DateStop")
rsMyRS.MoveNext
Loop

txtDateStart and Stop are text boxes in the report detail section. There
are nine records in the RS, but I only get the first records' values printed
nine times instead of EACH individual record. Any thoughts?

Thanks in advance for your help.

Tony
 
I've been working on the same issue for several days
except I'm sending the output to the printer rather than
DeBug. Try:

Debug Print txtDateStart; space(); txtDateStop

Use your variables and notice the semicolons rather than
ampersands. In addition to space (), check out tab().
You can put integers between the ()'s. In the case of tab
(), the integer refers to a specific column rather than
number of spaces. " " probably works too. Let me know
how you do.
 
Also, my code is in the Page event of the main report,
itself. I get to the records by declaring & setting the
database and opening the recordset. The reason you're
getting the same record may be that the "next" record
won't work as you have it. If you need the exact code let
me know. It's not available to me now because I'm at a
friend's house. I wanted to write tonight, before they
remove your original post, and I want to paste it here so
there are no errors. Just figured it out!
 
Tony said:
Greetings,

I am opening a recordset and would like to print the results in a report
detail section. I have the following code:

Do Until rsMyRS.EOF
txtDateStart = rsMyRS("DateStart")
txtDateStop = rsMyRS("DateStop")
Debug.Print rsMyRS("DateStart") & " " & rsMyRS("DateStop")
rsMyRS.MoveNext
Loop

txtDateStart and Stop are text boxes in the report detail section. There
are nine records in the RS, but I only get the first records' values printed
nine times instead of EACH individual record.
Click to expand...


You only have a single txtDateStart text box in the detail
section so it can only display a single value. You didn't
explain enough about your report for me to tell what might
be best for your situation, but here's a couple of ideas you
might try.

Place 9 txtDateStart and txtDateStop text boxes in the
detail section. Name them txtDateStart1 through
txtDateStart9, etc, then use code along these lines:

intK = 0
With rsMyRS
Do Until .EOF
intK = intK + 1
Me("txtDateStart" & intK) = !DateStart
Me("txtDateStop" & intK)= !DateStop
Debug.Print !DateStart & " " &!DateStop
rsMyRS.MoveNext
Loop

Actually, I think this whole approach is probably the wrong
way around and you should use a subreport instead.
 
Marshall,

Actually I could have an infinite number of records, so your approach won't
work for me. I really just need to know how, after opening a recordset I
can write the contents to the detail area of a report.

Tony
 
Tony said:
Marshall,

Actually I could have an infinite number of records, so your approach won't
work for me. I really just need to know how, after opening a recordset I
can write the contents to the detail area of a report.
Click to expand...


What is the report's record source?

How does it relate to the recordset you're trying to
process?

How does the record source data appear in the report's
detail section?

From what little you've said, I'll have to guess that the
report is unbound (i.e. no record source) so my questions
above are all answered by N/A. In, which case I then have
to ask why don't you use the recordset's query as the record
source of the report (or a subreport)?

Assuming it is unbound and you have a good reason for using
a recordset. Then the recordset would normally be opened in
the report's Open event and checked for no records. The
object variable rsMyRS must be declared at the module level.
The code in the detail sections Format event would be
something like:

txtDateStart = rsMyRS("DateStart")
txtDateStop = rsMyRS("DateStop")
rsMyRS.MoveNext
If Not rsMyRS.EOF Then Me.NextRecord = False

Note the lack of an explicit looping statement. Setting the
report's NextRecord property to false will cause Access to
process the same record source record (might be none) again,
thus the looping is handled for you.

Don't forget to close the recordset and set rsMYRS to
Nothing in the report's Close event.
 
Back
Top