Solved

Needed  fast: How-to create a dynamic report?

Posted on 1998-05-25
5
387 Views
Last Modified: 2008-02-01
Hello,

i'm rather new to MS Access and have a possibly easy to answer, but for me urgent question:

How do i create a report that will display its results based on a table with varying column-count?

Follow me? I have a table (result of a query) with 3 to 72 columns. I want to print the results that appear here. How do i create a dynamic report that will use this table as it's source?

Please help me out, Answers.mdb doesn't go this deep...

Regardz,

Joachyz!
0
Comment
Question by:joachyz
5 Comments
 
LVL 8

Expert Comment

by:Helicopter
ID: 1974352
How do you want to display the columns? 72 columns across a page would be impractical so I guess they would be listed down the page. Incidentally you do mean columns and not rows don't you?

If your table has 72 fields then presumably your query puts something into them in which case you just need one text box for each field, bind the report to the query and bind each text box to the required field. This will mean that any empty fields will be blank on the page but unless you want to dynamically place the text boxes there's no way round that.

If you do mean 3 to 72 rows then all you need is one text box bound to that field then in the on format event check if it is empty and if so set cancel=true so that blanks won't be printed.


0
 

Author Comment

by:joachyz
ID: 1974353
Thanks for your prompt response, however:

- I *do* mean 72 columns.
- I create a table dynamically, it can contain 3 to 72 columns, depending on what a user selects. (This requires some cool coding, it's all SQL :-)
- This table needs to be displayed in a formatted way. I consider to create x reports, and display them if needed or something.
- On one page 3 columns can be displayed, so that can take a lot of paper...

Help me!

Regardz,

Joachyz!
0
 
LVL 1

Accepted Solution

by:
Arve earned 200 total points
ID: 1974354
First, you design the report with 72 columns, like it would be when you have to use all columns. All fields is named like lbl01 to lbl72, txt01 to txt72. Find also out the space between every field. Then set visible=false and left = 0 on all fields.
On report.open event, find out how many columns you have and do this:
for i = 1 to me.recordsource.count
  me("lbl" & format$(i,"00")).left = i * 2000
  me("lbl" & format$(i,"00")).Visible = true
  me("txt" & format$(i,"00")).left = i * 2000
  me("txt" & format$(i,"00")).visible = true
next i
'2000 is the distance in twips between the fields, you need to 'use another value.

'This is not tested code

Regards, Arve
0
 

Author Comment

by:joachyz
ID: 1974355
I give you excellent grading :-) for the quick reponse. Actually i would like to test it first, but that will not be possible, only l8ter at home. Please allow me to ask you more questions when i try it out...

By the way, what are 'twips' ???

Well, anyway, thanks!

0
 
LVL 3

Expert Comment

by:strykker
ID: 3731052
1 inch = 1440 twips -- it is a microsoft measurement
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question