Putting Report Results IN A Form

[alexi]

HELP!

I have been stuck on this one for a couple of weeks now guys, can anyone help with some ideas?
I create an invoice report from some specs which i select on a form. This generates the invoice report once. However i want to be able to edit the data and save the data on this invoice. So what i need to do is transfer the information from the report into a form in datasheet view, based on an invoice number. This way i can edit anything i need to, it will be saved, and then i can report on that information print it out and send it off.

Any ideas?

Ta
Alexi

 

[Pat Hartman]

You need to define two new tables. One for the invoice and the second for the invoice items. Rather than generating an invoice report, calculate the data and append it to the new tables. Modify the invoice report so that it selects its data from the new tables.

 

[alexi]

ok i will try that, but is there a way of moving the info fro a report to a form?

 

[Pat Hartman]

No. You can export the report to a spreadsheet and then try to import it but I guarentee you'll have lots of trouble trying to do this.

 

[alexi]

ok, thanks for this by the way.

i have tried this before,

open report
report!control.name = forms!control!name
close report

this gives a random line from the report over to the form.
anyway of getting them all across using this method? like a loop or somethin?

 

[Pat Hartman]

You are barking up the wrong tree. If you want to process a recordset, open one by using the OpenRecordset method and loop through it. A form is used for human interaction with data and is not intended to be manipulated via code.

If you want to store the report's calculated values as they are calculated, use the report's Detail Section Format event and write the data to a recordset using .AddNew and .Update. Find code samples in help. Be very careful though since the Format event may be executed more than once for the same record. Be sure to check the FormatCount. If it is other than 1, bypass all of your code.

However, it would be best go get this code ENTIRELY out of the report since it DOES NOT BELONG THERE. Write a sub that calculates the Invoice and writes rows to the appropriate tables. Add a button to your order form to run this sub. Or run it once a day for all new orders.

Then run the report from the calculated invoice table. That way, if you have changed the calculated data manually, the invoice report will pick it up.

 

[alexi]

i dont suppose you could give me a bit of example code for this? im having trouble finding any?

 

[alexi]

ok ive done what pat suggested in the first reply by creating some tables and appending to them. this works ok, however the query shows every entry from the timesheets, so each time a task is worked on it shows it, what i want though is a sum of all the hours etc under one task, not every time it shows up in the timesheet.
any ideas?

 

[Pat Hartman]

Then change the select part of the query to a totals query. Open the query in QBE view. Press the sigma (backwards E) button. Change one of the Group Bys to Sum.

 

[alexi]

ive just tried that but which should i change to a sum to get it to do what i want?

 

[Pat Hartman]

It's your data. What column do you want to sum?

 

[alexi]

there is 3 that are calculating things off the tables. these are expressions not sums but same thing yeah? but each of these is attached to an entry (by a taskID). so i dont want each time a taskID is entered to show up, but the totals for each task and everything under that.?

 

[Pat Hartman]

The idea of a totals query is that it aggregates things. Therefore, it is necessary to omit columns that will interfere with the correct aggregation.

For example, if you want a count of each customers orders, you would include only CustomerID:
Select CustomerID, Count(*)
From YourTable
Group By CustomerID;

But if you wanted to count ALL orders, you would omit CustomerID.

Select Count(*)
From YourTable;