Extracting data from a report into a table 
Author Message
 Extracting data from a report into a table

I have a report called Invoice which is populated by a query.  There are
certain values on the report that are calculated as the report is run eg.
Total and VAT.  These values are in unbound text boxes and the control
source is set to an equation similar to "= (Sum([Period]*[Charge])".

I also have a macro that prints the invoice.  What i want to do is copy the
values of the Total and VAT boxes from the report and place them in a table
(eg, Invoice_Table with the fields Total and Vat and Invoice Number)

I have the following extract of VBA code set up in my macro to open the
table and add a new record but i dont know how to inset the data from the
report:

.....Set MyTable = MyDB.OpenRecordSet("Invoice", DB_OPEN_TABLE)
   MyTable.AddNew
   MyTable("Total") = ??????????
   MYTable("VAT") = ??????????
  MyTable.Update

What do i substitute ?????? with to add the values in the open report to the
table?

Any help appreciated

Andy Rowland



Mon, 26 Jan 2004 18:46:17 GMT  
 Extracting data from a report into a table
This might be a roundabout way to do it, but how about exporting it to a
file, then bringing it back in.

Something like:

...
docmd.outputTo acOutputReport,"MyReportNameHere",acFormatTXT,"my file name"
docmd.transfertext acimportDelim, "My File Name","my new table"
.....


Quote:
> I have a report called Invoice which is populated by a query.  There are
> certain values on the report that are calculated as the report is run eg.
> Total and VAT.  These values are in unbound text boxes and the control
> source is set to an equation similar to "= (Sum([Period]*[Charge])".

> I also have a macro that prints the invoice.  What i want to do is copy
the
> values of the Total and VAT boxes from the report and place them in a
table
> (eg, Invoice_Table with the fields Total and Vat and Invoice Number)

> I have the following extract of VBA code set up in my macro to open the
> table and add a new record but i dont know how to inset the data from the
> report:

> .....Set MyTable = MyDB.OpenRecordSet("Invoice", DB_OPEN_TABLE)
>    MyTable.AddNew
>    MyTable("Total") = ??????????
>    MYTable("VAT") = ??????????
>   MyTable.Update

> What do i substitute ?????? with to add the values in the open report to
the
> table?

> Any help appreciated

> Andy Rowland



Mon, 26 Jan 2004 20:21:44 GMT  
 Extracting data from a report into a table
a report does not contain any data, it simply displays the data passed to it
and/or calculated within itself.

very seldom is it advisable to store calculated data into a table anyway,
but if you really think you must then i would suggest that you create a new
query that uses the same query your report is based on for it's input. add
your calcs to this new query. when you are satisfied with results open a
recordset based on the new query and do yout table updates.

--

~~~~~~~~~~~~~
Make a Great Day
TomG


Quote:
> I have a report called Invoice which is populated by a query.  There are
> certain values on the report that are calculated as the report is run eg.
> Total and VAT.  These values are in unbound text boxes and the control
> source is set to an equation similar to "= (Sum([Period]*[Charge])".

> I also have a macro that prints the invoice.  What i want to do is copy
the
> values of the Total and VAT boxes from the report and place them in a
table
> (eg, Invoice_Table with the fields Total and Vat and Invoice Number)

> I have the following extract of VBA code set up in my macro to open the
> table and add a new record but i dont know how to inset the data from the
> report:

> .....Set MyTable = MyDB.OpenRecordSet("Invoice", DB_OPEN_TABLE)
>    MyTable.AddNew
>    MyTable("Total") = ??????????
>    MYTable("VAT") = ??????????
>   MyTable.Update

> What do i substitute ?????? with to add the values in the open report to
the
> table?

> Any help appreciated

> Andy Rowland



Mon, 26 Jan 2004 20:39:42 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Extract table data to Access table

2. Extracting data from multiple tables, for use on a VB Form

3. Extracting data from two tables - same database.

4. Extracting data from two tables - same database

5. extracting table data

6. How to create a query to sort and extract data from an Access table

7. Extracting Data from an access snapshot report

8. printing without crystal report or data report for non data base reports

9. Report shows data which was prior removed from table

10. Data Reporting using a multi-table select as datasource

11. Data Report does not match current table

12. Crystal sub-reports problem using TTX file and data tables

 

 
Powered by phpBB® Forum Software