DAO.Recordset as Report record source

[Steve S]

Can I use an individual record of a DAO.Recordset as Report record source?

I need to loop thru a recordset (or table or query) opening a different
report for each record in the recordset.

What I am trying to do is create score sheets for an athletic event where
each sub-event has a different form of score sheet. So it is open-close for
each record.

 

[Steve]

It seems like if your tables are designed correctly, you ought to be able to
design a report where each page is a different score sheet for your event.
Why don't you post the appropriate tables and let us have a look to see if
the above report is possible.

Steve
(e-mail address removed)

 

[Steve S]

Maybe I should be more specific. I want the printed pages (scoresheets) to
be in order by last name. If Mary black competes in 3 sub events (each with
a different score sheet that has different blocks for the judge to check or
put in a score) I now need to open and print 3 different reports consisting
of one printed page each. Next jane Brown competes in 4 events so I
open/print/close 4 reports. this takes FOREVER if I have 250 competitors
each in an average of 6 (out of 14 possible)events.

Right now I am creating a temp table and looping thru it until all score
sheets are printed in name order. there must be a faster way and I thought
it might be to use a DAO dynaset instead of a temp table.

Is it possible to desigh a report with multiple pages (different template
info on each page? That way I could load the report once and select (based
on the record source data) the correct page to print.


Steve S

 

[Gina Whipp]

Steve,

If I understand you correctly... take a look at Sorings and Groups and then
use a Page Break on the Footer of Last Name. That way you get each Player
on their own sheet and in order.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Steve]

Steve,

If you post your relevant tables, we can help you.

Steve
(e-mail address removed)

 

[Keith Wilby]

Steve,

If you post your relevant tables, we can help you.

"We"? Please don't consider yourself to be a part of this community until
your start to obey its rules.

 

[Arno R]

"We"? Please don't consider yourself to be a part of this community until your start to
obey its rules.

Hi Keith, I could not agree more...

Arno R

 

[Duane Hookom]

If you have different reports for different events then you should have a
field in your unique event table that stores the report name. You can then
create a recordset with the athletes, events, and reports. Loop through the
recordset and print the appropriate report.

It isn't clear whether there is one scoresheet per athlete per event or if
multiple athletes can be displayed in the same scoresheet.

 

[Duane Hookom]

You should have a field in your unique events table that identifies the
report/scoresheet to use. You can then create a recordset of people and
events with the appropriate report. Loop through the recordset and open the
reports to the printer.

 

[Steve S]

Thanks for the quick response Duane.

Was there some problem with this site the last few days?

Anyway I have one athlete/event/score sheet. I have a table (could be a
query if necessary) feeding the process. What I have to do is open score
sheet S1 for Mary B, then score sheet S2 for Mary B, score sheet S5 for Mary
B, score sheet S2 for Jane B, score sheet S6 for Jane B and so on.

I can loop thru the records opening the required report and I have all the
data you suggest in the source table but the problem (as I see it) is how to
open the report with only one record in the record source?

Bottom line is how do I set the record sourde of a report using DAO code.

Do you possibly have sample code?



How

 

[Steve]

You are trying to print score sheets in the wrong way. If you post your
appropriate tables we should be able to show you how to print all your
scoresheets in one operation.

Steve
(e-mail address removed)

 

[Duane Hookom]

I think the NGs (at least the web interface) has been wonky for a couple days.

Assuming you have some code with a recordset, you can use a Where Condition:

DoCmd.OpenReport rs.Fields("ReportName"), , , "[AthleteID]=" &
rs.Fields("AthleteID")

This all depends on your fields and their data types.

 

[Arno R]

You are trying to print score sheets in the wrong way. If you post your appropriate
tables we should be able to show you how to print all your scoresheets in one operation.

Steve
(e-mail address removed)

we should be able to show you how

Who is 'we' ??
I don't think you belong to 'us' $teve...
Don't pretend...

Arno R

 

[Steve S]

Here is the code as based on your (Duanes) example

Dim rst As DAO.Recordset
Set rst = Me.[Show Detail Records].Form.Recordset
Debug.Print rst.Fields("ScoreSheetName")
Debug.Print rst.Fields("Twirler")
DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, ,
"[Twirler]=" & rst.Fields("Twirler")

The 2 Debug.Print lines produce the two lines below which is correct
SS 2Baton
Holly Jacobson

the correct report opens but I get a #Name? error on the report for the
textbox where 'Twirler' is the record source.

Any suggestions ????
--
Steve S

I think the NGs (at least the web interface) has been wonky for a couple days.

Assuming you have some code with a recordset, you can use a Where Condition:

DoCmd.OpenReport rs.Fields("ReportName"), , , "[AthleteID]=" &
rs.Fields("AthleteID")

This all depends on your fields and their data types.

--
Duane Hookom
Microsoft Access MVP

Thanks for the quick response Duane.

Was there some problem with this site the last few days?

Anyway I have one athlete/event/score sheet. I have a table (could be a
query if necessary) feeding the process. What I have to do is open score
sheet S1 for Mary B, then score sheet S2 for Mary B, score sheet S5 for Mary
B, score sheet S2 for Jane B, score sheet S6 for Jane B and so on.

I can loop thru the records opening the required report and I have all the
data you suggest in the source table but the problem (as I see it) is how to
open the report with only one record in the record source?

Bottom line is how do I set the record sourde of a report using DAO code.

Do you possibly have sample code?



How

 

[Douglas J. Steele]

Since Twirler is a text field, you need quotes around the value:

DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, , _
"[Twirler]=""" & rst.Fields("Twirler") & """"

That's three double quotes in a row before the name, and four double quotes
in a row after.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here is the code as based on your (Duanes) example

Dim rst As DAO.Recordset
Set rst = Me.[Show Detail Records].Form.Recordset
Debug.Print rst.Fields("ScoreSheetName")
Debug.Print rst.Fields("Twirler")
DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, ,
"[Twirler]=" & rst.Fields("Twirler")

The 2 Debug.Print lines produce the two lines below which is correct
SS 2Baton
Holly Jacobson

the correct report opens but I get a #Name? error on the report for the
textbox where 'Twirler' is the record source.

Any suggestions ????
--
Steve S

I think the NGs (at least the web interface) has been wonky for a couple
days.

Assuming you have some code with a recordset, you can use a Where
Condition:

DoCmd.OpenReport rs.Fields("ReportName"), , , "[AthleteID]=" &
rs.Fields("AthleteID")

This all depends on your fields and their data types.

--
Duane Hookom
Microsoft Access MVP

Thanks for the quick response Duane.

Was there some problem with this site the last few days?

Anyway I have one athlete/event/score sheet. I have a table (could be
a
query if necessary) feeding the process. What I have to do is open
score
sheet S1 for Mary B, then score sheet S2 for Mary B, score sheet S5 for
Mary
B, score sheet S2 for Jane B, score sheet S6 for Jane B and so on.

I can loop thru the records opening the required report and I have all
the
data you suggest in the source table but the problem (as I see it) is
how to
open the report with only one record in the record source?

Bottom line is how do I set the record sourde of a report using DAO
code.

Do you possibly have sample code?



How
--
Steve S


:

If you have different reports for different events then you should
have a
field in your unique event table that stores the report name. You can
then
create a recordset with the athletes, events, and reports. Loop
through the
recordset and print the appropriate report.

It isn't clear whether there is one scoresheet per athlete per event
or if
multiple athletes can be displayed in the same scoresheet.

--
Duane Hookom
Microsoft Access MVP


:

Can I use an individual record of a DAO.Recordset as Report record
source?

I need to loop thru a recordset (or table or query) opening a
different
report for each record in the recordset.

What I am trying to do is create score sheets for an athletic event
where
each sub-event has a different form of score sheet. So it is
open-close for
each record.

 

[Steve S]

OK, here is a simplified table / query / DAO.Recordset.

Twirler Scoresheet
Mary B Strut
Mary B Solo
Mary B 2 Baton
Mary B Modeling
Jane C Modeling
Jane C X Strut
Jane C Solo
Jane C 2 Baton

Each score sheet is a seperate report since each event is scored on
different techniques/skills.

I need all the score sheets for each person printed in the sequence shown
above. The only way I can see to do this is to open the required report for
one score sheet, get the twirlers name into the correct text box, print the
report, and go on to the next twirler.

Ideas???

 

[Steve S]

Thanks Douglas,

Below is a cut/paste of your suggestion but it produced the same results:
#Name?

DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, ,
"[Twirler]=""" & rst.Fields("Twirler") & """"

I am using AC2000. Could that be the problem? I have verified that the
Control source for the textbox is "Twirler" and there is no 'Record source'
shown on the Property sheet.
--
Steve S

Since Twirler is a text field, you need quotes around the value:

DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, , _
"[Twirler]=""" & rst.Fields("Twirler") & """"

That's three double quotes in a row before the name, and four double quotes
in a row after.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here is the code as based on your (Duanes) example

Dim rst As DAO.Recordset
Set rst = Me.[Show Detail Records].Form.Recordset
Debug.Print rst.Fields("ScoreSheetName")
Debug.Print rst.Fields("Twirler")
DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, ,
"[Twirler]=" & rst.Fields("Twirler")

The 2 Debug.Print lines produce the two lines below which is correct
SS 2Baton
Holly Jacobson

the correct report opens but I get a #Name? error on the report for the
textbox where 'Twirler' is the record source.

Any suggestions ????
--
Steve S

I think the NGs (at least the web interface) has been wonky for a couple
days.

Assuming you have some code with a recordset, you can use a Where
Condition:

DoCmd.OpenReport rs.Fields("ReportName"), , , "[AthleteID]=" &
rs.Fields("AthleteID")

This all depends on your fields and their data types.

--
Duane Hookom
Microsoft Access MVP


:

Thanks for the quick response Duane.

Was there some problem with this site the last few days?

Anyway I have one athlete/event/score sheet. I have a table (could be
a
query if necessary) feeding the process. What I have to do is open
score
sheet S1 for Mary B, then score sheet S2 for Mary B, score sheet S5 for
Mary
B, score sheet S2 for Jane B, score sheet S6 for Jane B and so on.

I can loop thru the records opening the required report and I have all
the
data you suggest in the source table but the problem (as I see it) is
how to
open the report with only one record in the record source?

Bottom line is how do I set the record sourde of a report using DAO
code.

Do you possibly have sample code?



How
--
Steve S


:

If you have different reports for different events then you should
have a
field in your unique event table that stores the report name. You can
then
create a recordset with the athletes, events, and reports. Loop
through the
recordset and print the appropriate report.

It isn't clear whether there is one scoresheet per athlete per event
or if
multiple athletes can be displayed in the same scoresheet.

--
Duane Hookom
Microsoft Access MVP


:

Can I use an individual record of a DAO.Recordset as Report record
source?

I need to loop thru a recordset (or table or query) opening a
different
report for each record in the recordset.

What I am trying to do is create score sheets for an athletic event
where
each sub-event has a different form of score sheet. So it is
open-close for
each record.

.

 

[David W. Fenton]

Can I use an individual record of a DAO.Recordset as Report record
source?

I need to loop thru a recordset (or table or query) opening a
different report for each record in the recordset.

What I am trying to do is create score sheets for an athletic
event where each sub-event has a different form of score sheet.
So it is open-close for each record.

Is there some performance reason that the traditional method of
supplying a WHERE clause as an OpenReport argument is not
sufficient?

While I understand the desire to be efficient, it may be more
trouble than it's worth as assigning a recordset to Access objects
is not nearly as reliable as just filtering a Recordsource.

Also, I have my doubts as to whether it makes sense to walk through
a recordset in the first place. If you format the report
appropriately, you can just load all the records in it and it will
print one instance for each record. Again, this is the traditional
Access approach, and it is likely going to be much easier in the
long run than the setup you're asking about.

It seems to me that a lot of people who come to Access from other
programming environments (I don't know whether that would include
you or not) make things harder than they need to be, because they
never took the time to learn to use Access interactively. Basically,
I create the forms and reports and queries interactively, and only
use code to tie them together. I never assign recordsets from one
object to another object, as I can think of no situation in which
the extra work this takes returns value sufficient to justify it.

 

[Steve S]

David please see comments below.
--
Steve S

Is there some performance reason that the traditional method of
supplying a WHERE clause as an OpenReport argument is not
sufficient?

With the help of previous posts to my question this exactly where I am now.

While I understand the desire to be efficient, it may be more
trouble than it's worth as assigning a recordset to Access objects
is not nearly as reliable as just filtering a Recordsource.

Also, I have my doubts as to whether it makes sense to walk through
a recordset in the first place. If you format the report
appropriately, you can just load all the records in it and it will
print one instance for each record.

OK, here are the results of the query I need to feed my reports.

Twirler Scoresheet
Mary B Strut
Mary B Solo
Mary B 2 Baton
Mary B Modeling
Jane C Modeling
Jane C X Strut
Jane C Solo
Jane C 2 Baton

Each score sheet is a seperate report since each event is scored on
different techniques/skills.

I need all the score sheets for each person printed in the sequence shown
above. The only way I can see to do this is to open the required report for
one score sheet, get the twirlers name into the correct text box, print the
report, and go on to the next twirler.

Ideas???


Again, this is the traditional

Access approach, and it is likely going to be much easier in the
long run than the setup you're asking about.

It seems to me that a lot of people who come to Access from other
programming environments (I don't know whether that would include
you or not)

Yes I am. I Took my first programming class (Frotran) in 1970. Worked on
a very large payroll/finance system in the late 70/early 80s for the USMC
(written in Assembler). Taught Fortran, COBOL, PL1, Assembler at the Junior
college level. Have a degree in Computer Science from the University of
California Irvine. Old dogs can learn new tricks.

make things harder than they need to be, because they

never took the time to learn to use Access interactively.

exactly what do you mean 'interactively'? using the Wizards? or the design
views of objects?

Basically,

 

[Douglas J. Steele]

If you put

Debug.Print "[Twirler]=""" & rst.Fields("Twirler") & """"

in your code, what appears in the Immediate window (Ctrl-G)?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas,

Below is a cut/paste of your suggestion but it produced the same results:
#Name?

DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, ,
"[Twirler]=""" & rst.Fields("Twirler") & """"

I am using AC2000. Could that be the problem? I have verified that the
Control source for the textbox is "Twirler" and there is no 'Record
source'
shown on the Property sheet.
--
Steve S

Since Twirler is a text field, you need quotes around the value:

DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, , _
"[Twirler]=""" & rst.Fields("Twirler") & """"

That's three double quotes in a row before the name, and four double
quotes
in a row after.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here is the code as based on your (Duanes) example

Dim rst As DAO.Recordset
Set rst = Me.[Show Detail Records].Form.Recordset
Debug.Print rst.Fields("ScoreSheetName")
Debug.Print rst.Fields("Twirler")
DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview, ,
"[Twirler]=" & rst.Fields("Twirler")

The 2 Debug.Print lines produce the two lines below which is correct
SS 2Baton
Holly Jacobson

the correct report opens but I get a #Name? error on the report for the
textbox where 'Twirler' is the record source.

Any suggestions ????
--
Steve S


:

I think the NGs (at least the web interface) has been wonky for a
couple
days.

Assuming you have some code with a recordset, you can use a Where
Condition:

DoCmd.OpenReport rs.Fields("ReportName"), , , "[AthleteID]=" &
rs.Fields("AthleteID")

This all depends on your fields and their data types.

--
Duane Hookom
Microsoft Access MVP


:

Thanks for the quick response Duane.

Was there some problem with this site the last few days?

Anyway I have one athlete/event/score sheet. I have a table (could
be
a
query if necessary) feeding the process. What I have to do is open
score
sheet S1 for Mary B, then score sheet S2 for Mary B, score sheet S5
for
Mary
B, score sheet S2 for Jane B, score sheet S6 for Jane B and so on.

I can loop thru the records opening the required report and I have
all
the
data you suggest in the source table but the problem (as I see it)
is
how to
open the report with only one record in the record source?

Bottom line is how do I set the record sourde of a report using DAO
code.

Do you possibly have sample code?



How
--
Steve S


:

If you have different reports for different events then you should
have a
field in your unique event table that stores the report name. You
can
then
create a recordset with the athletes, events, and reports. Loop
through the
recordset and print the appropriate report.

It isn't clear whether there is one scoresheet per athlete per
event
or if
multiple athletes can be displayed in the same scoresheet.

--
Duane Hookom
Microsoft Access MVP


:

Can I use an individual record of a DAO.Recordset as Report
record
source?

I need to loop thru a recordset (or table or query) opening a
different
report for each record in the recordset.

What I am trying to do is create score sheets for an athletic
event
where
each sub-event has a different form of score sheet. So it is
open-close for
each record.

.