DAO.Recordset as Report record source

  • Thread starter Thread starter Steve S
  • Start date Start date
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)


Steve S said:
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


Douglas J. Steele said:
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)


Steve S said:
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


.
Click to expand...


.
Click to expand...
Click to expand...
 
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


Douglas J. Steele said:
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)


Steve S said:
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


Douglas J. Steele said:
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


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
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.
 
Back
Top