DAO.Recordset as Report record source

[David W. Fenton]

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


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???

It's really a matter of getting the right data in the report's
recordsource such that:

1. you can select on the right data, AND

2. you can sort and group in the appropriate order.

I don't know enough about your data to determine what will work, but
one option would be to use your existing report as a subreport of
another report that uses as its RecordSource the SQL you were
previously using in your recordset. This can cause problems if each
individual report spans multiple pages, but you don't say they
aren't 1-page reports, so it might not be an issue.

Again, this is the traditional

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.

Of course they can, but they have to know the lay of the land to
suspect what tricks are out there. I think that using Access
interactively is the best way to get started with that.

make things harder than they need to be, because they

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

Yes. This teaches you the default methods for accomplishing things
without coding (or with minimal code created by the wizards). The
ideal is to do as much interactively and only delve into code where
required.

Of course, one can very quickly start needing to get into code if
you need to do something more complicated than is possible
interactively, particular when attempting to automate a series of
manual tasks.

The point is that assigned recordsets are something you can use only
in code. Likewise, the first version of Access to even offer the
ability to assign an existing recordset to a form or report was
Access 2000, and all of us Access developers had had many years of
productivity creating complex and rich applications without ever
needing that feature. I still haven't used it except in proof of
concept experiments, and it's been available for 10 years.

 

[Steve S]

I did a copy/paste of your suggestion and it produced

[Twirler]="Holly Jacobson"

in the immediate window. this is a copy/paste of the results.

For this report the Record Source is blank or maybe null. Is this correct?
Also I am using AC2000. Could that be the problem?
--
Steve S

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.

--
Steve S


.

.

 

[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???

 

[Gina Whipp]

Steve S,

How about adding a SortOrder field to your table? Though I am confused why
Modeling is last for Mary but First for Jane or was that just a typo?

--
Gina Whipp

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

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

 

[Douglas J. Steele]

Not sure I understand what you mean by "the Record Source is blank or maybe
null".

Are you saying that the query may not be bringing back any data?

While Access 2000 was arguably one of the worst versions of Access released,
I doubt that's the cause of your problem.

If you just open the report normally, using

DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview

do you get any errors?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I did a copy/paste of your suggestion and it produced

[Twirler]="Holly Jacobson"

in the immediate window. this is a copy/paste of the results.

For this report the Record Source is blank or maybe null. Is this
correct?
Also I am using AC2000. Could that be the problem?
--
Steve S

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.

--
Steve S


.

.

 

[Steve S]

Sorry for all the work everyone has put into this but the problem is that I
misinterpreted the use of the 'OpenArgs' parameter of the DoCmd.OpenReport.
The problem is ME.

Thanks again for all the help.
--
Steve S

Not sure I understand what you mean by "the Record Source is blank or maybe
null".

Are you saying that the query may not be bringing back any data?

While Access 2000 was arguably one of the worst versions of Access released,
I doubt that's the cause of your problem.

If you just open the report normally, using

DoCmd.OpenReport rst.Fields("ScoreSheetName"), acViewPreview

do you get any errors?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I did a copy/paste of your suggestion and it produced

[Twirler]="Holly Jacobson"

in the immediate window. this is a copy/paste of the results.

For this report the Record Source is blank or maybe null. Is this
correct?
Also I am using AC2000. Could that be the problem?
--
Steve S

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.

--
Steve S


.



.

.

 

[Steve S]

Sorry for all the work everyone has put into this but the problem is that I
misinterpreted the use of the 'OpenArgs' parameter of the DoCmd.OpenReport.
The problem is ME.

Thanks again for all the help.