Filling Data Fields in Reports

  • Thread starter Queries within Reports
  • Start date
Q

Queries within Reports

I have created some very extensive reports in Access2000. I have many
controls that need to be filled with results from individual fields from
different queries. No matter what I put in as the Control Source when I
switch to report view in comes up as #Name?. I dont know what I am doing
wrong but I am completely stuck. Can anyone help?
 
J

John Vinson

I have created some very extensive reports in Access2000. I have many
controls that need to be filled with results from individual fields from
different queries. No matter what I put in as the Control Source when I
switch to report view in comes up as #Name?. I dont know what I am doing
wrong but I am completely stuck. Can anyone help?
Click to expand...

A Report has a "Recordsource" property. This is a Query (it can be
just a table but typically a Query). The fields in this Query are the
ones - the ONLY ones! - available for use in Controls.

Three getarounds:

- Create a bigger master Query as the recordsource, containing all the
fields you need.
- Use a Report based on one query, with one or more Subreports based
on other queries; be sure to use the proper Link Master Field and Link
Child Field properties to keep the data in synch.
- Use =DLookUp("[fieldname]", "[queryname]", "<optional criteria>") as
the Control Source of a textbox on the Report. If you do this with a
lot of textboxes it should work... but it will be VERY slow.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
B

Brent

Thank you. That is what I needed to know.

John Vinson said:
I have created some very extensive reports in Access2000. I have many
controls that need to be filled with results from individual fields from
different queries. No matter what I put in as the Control Source when I
switch to report view in comes up as #Name?. I dont know what I am doing
wrong but I am completely stuck. Can anyone help?
Click to expand...

A Report has a "Recordsource" property. This is a Query (it can be
just a table but typically a Query). The fields in this Query are the
ones - the ONLY ones! - available for use in Controls.

Three getarounds:

- Create a bigger master Query as the recordsource, containing all the
fields you need.
- Use a Report based on one query, with one or more Subreports based
on other queries; be sure to use the proper Link Master Field and Link
Child Field properties to keep the data in synch.
- Use =DLookUp("[fieldname]", "[queryname]", "<optional criteria>") as
the Control Source of a textbox on the Report. If you do this with a
lot of textboxes it should work... but it will be VERY slow.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
B

Brent

So would it be faster to use 120 DLookups or 12 different queries for each
report? Sorry but I am new to Access reports of this size and complication.

Brent said:
Thank you. That is what I needed to know.

John Vinson said:
I have created some very extensive reports in Access2000. I have many
controls that need to be filled with results from individual fields from
different queries. No matter what I put in as the Control Source when I
switch to report view in comes up as #Name?. I dont know what I am doing
wrong but I am completely stuck. Can anyone help?
Click to expand...

A Report has a "Recordsource" property. This is a Query (it can be
just a table but typically a Query). The fields in this Query are the
ones - the ONLY ones! - available for use in Controls.

Three getarounds:

- Create a bigger master Query as the recordsource, containing all the
fields you need.
- Use a Report based on one query, with one or more Subreports based
on other queries; be sure to use the proper Link Master Field and Link
Child Field properties to keep the data in synch.
- Use =DLookUp("[fieldname]", "[queryname]", "<optional criteria>") as
the Control Source of a textbox on the Report. If you do this with a
lot of textboxes it should work... but it will be VERY slow.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
Click to expand...
 
J

John Vinson

So would it be faster to use 120 DLookups or 12 different queries for each
report? Sorry but I am new to Access reports of this size and complication.
Click to expand...

Neither option sounds at ALL appealing to me! If I had to choose I'd
guess the queries, but I'm not at all sure.

Could you explain the business situation a bit? This would appear to
be a monstrously large amount of information to be printed on a single
report!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top