Trying to build a report without refering to tables 1

[paxelius]

I have 1 table in which I have 50 fields, ie. num1, num2...num50 - Each of these have number as data type.

Each of these fields can store a number from 0 to 3. (In my survey 0 = unanswered, 1 = must, 2 = may and 3=no).

I have built using VB a criteria calculation which shows all fields, diveded up in 4 states with these values ( 0..3). I need to print these out to a sheet of paper, preferably in Excel style.

My problem is that I cannot use the report-generator, because for every field, ie. num1, I need to list the totals of each of the 4 states ( must/may/no/unanswered).. And this means that I need 50 x 4 => 200 fields in my table to store all the pre-calculated numbers. This sounds very cumbersome to make and I'm sure there is a better way to do this (but I can't comprehend what yet).

I need to use my already calculated sums from VB in my report, which means I cannot begin to calculate with the fields from my table in the report. This is my problem. Hope you all understood.

I need to make a report based on variables and arrays I have already computed in VB, but I cannot store those values to fields in a table, so how can I then refer to them on my report ??

-Aeron Pax

 

[dhookom]

You are committing spreadsheet. There is an example of a survey "At Your Survey" at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

that is fairly well normalized.

If your data was normalized, you could easily create crosstab query of the results. If you can't normalize, you can try create a union query of your table and then a crosstab from the union query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[paxelius]

Thanks dhookom. But your reply sadly went way over my head.

First of all I must say that my survey database is built up entirely different than your example, and it is impossible for me at this junction to relate anything specific from your example to my database. sorry.

Let me rephrase my problem:

I have 4 arrays of numbers in VB for each of the 50 field in my table. I have built my own report-form which displays on screen perfectly all I need to know. But I need to be able to print out my report-form with its data onto an Excel sheet. It is not adequate to print the screen, because it is too big to fit on a A4 paper, and it just gets messy.

I'm not sure what the best method is, but I need to output every variable from VB to a file, preperably .xls to simulate my report-form on screen.

Can this be done and how ?

-Aeron Pax

 

[dhookom]

You can open a blank report and use the Print method to print text anywhere you want on the report page. For instance, the following code fills an array in the On Open event of the report and then in the On Page event, prints all the values to your report page:

Option Compare Database
Dim Results(50, 4) 'create the array

Private Sub Report_Open(Cancel As Integer)
    Dim intX As Integer
    Dim intY As Integer
    'fill the array with random values
    For intX = 1 To 50
        For intY = 1 To 4
            Results(intX, intY) = Int(Rnd() * 4)
        Next
    Next
End Sub

Private Sub Report_Page()
    Dim intX As Integer
    Dim intY As Integer
    For intX = 1 To 50
        For intY = 1 To 4
            'set the position on the page
            CurrentX = intX * CLng(Me.Width / 50)
            CurrentY = intY * 500
            'print the value
            Me.Print Results(intX, intY)
        Next
    Next
End Sub

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[paxelius]

Thank you! This looks like exactly what I need, I'll try it out right away ...

-Aeron Pax

 

[paxelius]

Now I'm ready to print all my variables to the report page, but there's just one more thing I need to know -.

In my Report-form screen I have 50 labels, one for every field. How can I refer to these labels from the report ?

My Report-form is called "Rapport" and my labels are named "Label1", "label2", etc.. "label50". I've tried to print them in the bottom of the sub Report_page like this:
Me.Print "test:" & [Rapport]![Label1] - but this does not work, it returns Error 2465. Am I close ?

-Aeron Pax

 

[dhookom]

To refer to the caption property of a label control on an open form, use:

     Forms!FormName!LabelName.Caption

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[paxelius]

Thanks Duane !

-Aeron Pax

 

[paxelius]

Duane: Your method of refering to labels from an open form and print them to a blank report works very well. I'm very pleased with this... but now that I have had some time to think things through, I see that the best result for my report would be to put the labels and values directly into the cells of an Excel sheet.

This must be possible, but I don't know how to assign my data to their correct cells in Excel - Could you point me to some code that illustrates how to do this ?

Thanks!

-Aeron Pax

 

[dhookom]

I probably wouldn't send this to Excel. You can search for information on Excel Automation.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[paxelius]

Ok.. I'll search some more.

But why wouldn't you send this to Excel ?
If I only wanted to output my report to a printer I agree with you, but since I'm outputting a report based on selected sorting-criteria of the survey, I see the need to output to file (preferably .xls) and designate in which cell I want to put my data, in order to make more advanced statistical data analysis possible at a later stage.

Another issue that troubles me is that I have found the TransferSpreadSheet and OutPutTo .xls methods close to useless, because I can't control in which cell a certain field is put. This results in very messy reports dumped to file, when I need an ordered structure for my fields and data to be displayed.

-Aeron Pax

 

[dhookom]

I would use "At Your Survey" which is quite normalized. I could create virtually any simple statistics required with this table structure and possibly a few modifications. Access allows graphing and very superior querying compared to Excel. A normalized database allows you to format your data however you want to send it to Excel at any time that you think you need more analysis than Access can provide.

I searched Google with "Excel Automation" as I suggested previously. I got over 9000 hits with the very first one pointing to the Access Web with a small sample of code that should get you started.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.